Thread: the operator "=" does not work for some SQL queries

Harald Bartel (harald.bartel@prozentor.de) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
the operator "=" does not work for some SQL queries

Long Description
Version: PostgreSQL 7.1
System: Debian Woody, both Linux 2.2.14 and 2.4.3

For some SQL queries (for an example see below) the operator "=" on text does not work, that is, no rows are returned,
althoughsome should be returned.In PostgreSQL 7.0.3 I avoided this problem by using 
the LIKE operator instead of the "=" operator.In PostgreSQL 7.1 this
trick does not work anymore. But I found another way to avoid the problem by using SUBSTR. For example I used
substr(text1,1,8)=substr(text2,1,8)instead of text1=text2 or  
text1 like text2, where 8 is the length of both texts.

Sample Code
# original query that should work:

SELECT segment.nameenglischkurz, selektioneintrag_osi(transaktionh.selektion), wp.isin, unternehmen.namekurz FROM
transaktionh,segment, wp, unternehmen, wpstamm WHERE wp.osi = selektioneintrag_osi(transaktionh.selektion) and wp.isin
=wpstamm.isin and wpstamm.unternehmennr = unternehmen.unternehmennr and transaktionh.segmentnr = 43 and typnr = 3 and
segment.segmentnr= transaktionh.segmentnr ORDER BY verkaufdt::datetime DESC; 
 nameenglischkurz | selektioneintrag_osi | isin | namekurz
------------------+----------------------+------+----------
(0 rows)

######################################
query using substr:

SELECT segment.nameenglischkurz, selektioneintrag_osi(transaktionh.selektion), wp.isin, unternehmen.namekurz FROM
transaktionh,segment, wp, unternehmen, wpstamm WHERE substr(wp.osi,1,8) =
substr(selektioneintrag_osi(transaktionh.selektion),1,8)and wp.isin = wpstamm.isin and wpstamm.unternehmennr =
unternehmen.unternehmennrand transaktionh.segmentnr = 43 and typnr = 3 and segment.segmentnr = transaktionh.segmentnr
ORDERBY verkaufdt::datetime DESC; 
 nameenglischkurz | selektioneintrag_osi |     isin     |     namekurz
------------------+----------------------+--------------+------------------
 DAX              | DE710000             | DE0007100000 | DaimlerChrysler
 DAX              | DE604843             | DE0006048432 | Henkel
...


No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> For some SQL queries (for an example see below) the operator "=" on text does not work, that is, no rows are
returned,although some should be returned.In PostgreSQL 7.0.3 I avoided this problem by using 
> the LIKE operator instead of the "=" operator.In PostgreSQL 7.1 this
> trick does not work anymore. But I found another way to avoid the problem by using SUBSTR. For example I used
substr(text1,1,8)=substr(text2,1,8)instead of text1=text2 or  
> text1 like text2, where 8 is the length of both texts.

Perhaps you are failing to consider trailing blanks in one or both input
values?

            regards, tom lane