Thread

1. the operator "=" does not work for some SQL queries

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-04-20T19:26:06Z

   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, 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.
   
   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.unternehmennr and transaktionh.segmentnr = 43 and typnr = 3 and segment.segmentnr = transaktionh.segmentnr ORDER BY verkaufdt::datetime DESC;
    nameenglischkurz | selektioneintrag_osi |     isin     |     namekurz     
   ------------------+----------------------+--------------+------------------
    DAX              | DE710000             | DE0007100000 | DaimlerChrysler
    DAX              | DE604843             | DE0006048432 | Henkel
   ...
   
   
   No file was uploaded with this report
   
   
   

2. Re: the operator "=" does not work for some SQL queries

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-20T20:27:43Z

   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