Thread: Comparison of Strings

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

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