Thread: BUG #2262: WHERE-clause comparisons wrong

BUG #2262: WHERE-clause comparisons wrong

From
"Richard Baverstock"
Date:
The following bug has been logged online:

Bug reference:      2262
Logged by:          Richard Baverstock
Email address:      richard@3dtechnologies.ca
PostgreSQL version: 8.1
Operating system:   Windows XP Pro
Description:        WHERE-clause comparisons wrong
Details:

History: Imported data from an 8.0 to 8.1 server. The 8.0 server was set to
use whatever the default encoding was on win32; the 8.1 server is set to use
utf8. Import using a dump from the 8.0 server went fine.

Problem: When querying the database, any requirements for "WHERE" that
involve comparing strings with spaces or dashes/hyphens return false, when
they should be true.

For example; assuming a table called inventory has a column named "Number"
(varchar), and a row has the value 'ABC 123' for the number, the following
returns the correct row:

select "Number" from "inventory" where "Number" LIKE 'ABC%';

however, the following two queries return no rows:

select "Number" from "inventory" where "NUMBER" LIKE 'ABC %';
select "Number" from "inventory" where "Number" = 'ABC 123';


IF however, the Number being searched for was not imported from the 8.0
database, everything is fine. The space as returned by the successful query
is the correct hex value (0x20).