Thread

1. Comparison of Strings

   Mike Schulte <schulte@cs.umsl.edu> — 2000-11-20T15:48:42Z

   I am running
         PostgreSQL 7.0.2 on sparc-sun-solaris2.8, compiled by gcc 2.95.2
   
   I have a database table whose contents are
   
   cs302=# select * from author;
        isbn     |         author
   --------------+-------------------------
    '1565921941' | 'Meyer, Jon'
    '1565921941' | 'Downing, Troy'
    '0201100886' | 'Aho, Alfred V.'
    '0201100886' | 'Sethi, Ravi'
    '0201100886' | 'Ullman, Jeffrey D.'
    '0878518096' | 'Maloney, Elbert S.'
    '0870211641' | 'Maloney, Elbert S.'
    '0441865003' | 'Thomson, Amy'
    '0312944446' | 'Vinge, Vernor'
    '0030860784' | 'Hungerford, Thomas W.'
    '0201038099' | 'Knuth, Donald E.'
    '1565920015' | 'Gilly, Daniel'
   (12 rows)
   
   
   If I run the following command, I get  [the string matches the first two
   entries in the table]:
   
   cs302=# select * from author where isbn = '1565921941';
    isbn | author
   ------+--------
   (0 rows)
   
   
   If I change the = to a ~, I get
   
   cs302=# select * from author where isbn ~ '1565921941';
        isbn     |     author
   --------------+-----------------
    '1565921941' | 'Meyer, Jon'
    '1565921941' | 'Downing, Troy'
   (2 rows)
   
   
   Is there something about comparison of strings with the = that I don't
   see, that makes it different from string matching with the ~?
   
   ---
   Michael Schulte              Specialist in Computer Science
   UM-St. Louis                 schulte@cs.umsl.edu
   8001 Natural Bridge Road     (314) 516 5239
   St. Louis, MO  63121  USA
   http://cs.umsl.edu/~schulte
   
   
   
   

2. Re: Comparison of Strings

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-24T01:46:52Z

   Mike Schulte <schulte@cs.umsl.edu> writes:
   > If I run the following command, I get  [the string matches the first two
   > entries in the table]:
   
   > cs302=# select * from author where isbn = '1565921941';
   >  isbn | author
   > ------+--------
   > (0 rows)
   
   Ugh :-(.  This looks like a corrupted-index problem --- is there an
   index on the isbn column?  If so, does EXPLAIN show that this query
   uses an indexscan?
   
   Dropping and recreating the index would probably make the problem
   go away, but that doesn't answer the more interesting question: how'd
   you get into this state?  Have you had any crashes or peculiar behavior
   with this database?  Do you have LOCALE support compiled in, and if so
   have you been careful to start the postmaster with the same locale
   setting every time?  (A column of ISBNs doesn't seem like promising
   material for a locale problem, but you never know...)
   
   It would be revealing to check whether the index is really in proper
   order or not.  Try 
   
   set enable_sort to off;
   select isdn from author order by isdn;
   
   (make sure EXPLAIN agrees that this query will be done by an indexscan
   with no explicit sort step).  See if the output is in proper sort
   order or not...
   
   			regards, tom lane