Re: String Comparision Weirdness

On Mon, 26 Sep 2005, Tobias Brox wrote:

> We had major problems after migrating the DB to a more powerful server; we
> managed to locate the problem to a type conversion bug in our software.
> Never the less, this thing puzzles us a lot:
>
> NBTEST2=# select '-1'>'0';
>  ?column?
> ----------
>  t
> (1 row)
>
> We've tried this query on several servers with different versions of
> postgresql and different versions of glibc - some returns true, others
> returns false - and it seems neither to be related to the postgresql version
> nor the glibc version.  At all servers we tested, strcmp("-1","0") returned
> negative - at some -3 and at others -1, and not related to postgresql.
>
> The correct result above should be false, since ascii('-')=45 while
> ascii('0')=48.
>
> Can the character set in use be significant?

It's more likely to be the locale in use.  For example, on my machine,
given a file with -1 and 0.

LANG="C" sort file
-1
0

LANG="en_US" sort file
0
-1

Many locales do a more complicated comparison than ascii values (like
strcmp).  For example, symbols and spaces may only be used as tiebreakers
after effectively comparing the strings without them.