Strange query behavior where clause produces odd behavior on '>' query
The following query:
SELECT * FROM Customers_connxstore where customerid > 'specd'
Returns the row containing Customers_connxstore.customerid == 'SPECD'
I would expect to get that row if the query was:
SELECT * FROM Customers_connxstore where customerid >= 'specd'
The other operators (<, <=, >, =, !=) all work as expected.
Sample file to reproduce the problem:
http://cap.connx.com/bugreport/pgbug.sql.bz2
Is this a known issue with PostgreSQL or for some reason the desired
behavior?
"Dann Corbit" <DCorbit@connx.com> writes:
The following query:
SELECT * FROM Customers_connxstore where customerid > 'specd'
Returns the row containing Customers_connxstore.customerid == 'SPECD'
What encoding/locale are you using? And while I'm asking, which PG version?
regards, tom lane
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, October 30, 2008 5:31 PM
To: Dann Corbit
Cc: [HACKERS]; Sherry Griffin
Subject: Re: [HACKERS] Strange query behavior where clause produces
odd
behavior on '>' query
"Dann Corbit" <DCorbit@connx.com> writes:
The following query:
SELECT * FROM Customers_connxstore where customerid > 'specd'
Returns the row containing Customers_connxstore.customerid ==
'SPECD'
What encoding/locale are you using?
Whatever the default encoding/locale is.
I did not define any custom encoding, locale, or collating sequence.
And while I'm asking, which PG
version?
All versions from PostgreSQL 7.1.3 to "PostgreSQL 8.3.3, compiled by
Visual C++ build 1400" seem to display this behavior.
Are you unable to reproduce it?
"Dann Corbit" <DCorbit@connx.com> writes:
What encoding/locale are you using?
Whatever the default encoding/locale is.
"Whatever" is the wrong answer here. I just finished verifying that the
sort order you're complaining about is the expected ordering in some
locales. I suggest that you take the trouble to find out.
Are you unable to reproduce it?
Well, I see this on a Fedora machine:
$ cat foo
specd
SPECD
$ sort foo
SPECD
specd
$ LANG=en_US sort foo
specd
SPECD
$
regards, tom lane
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, October 30, 2008 6:34 PM
To: Dann Corbit
Cc: [HACKERS]; Sherry Griffin
Subject: Re: [HACKERS] Strange query behavior where clause produces
odd
behavior on '>' query
"Dann Corbit" <DCorbit@connx.com> writes:
What encoding/locale are you using?
Whatever the default encoding/locale is.
"Whatever" is the wrong answer here. I just finished verifying that
the
sort order you're complaining about is the expected ordering in some
locales. I suggest that you take the trouble to find out.
English (United States) is my locale.
Are you unable to reproduce it?
Well, I see this on a Fedora machine:
$ cat foo
specd
SPECD
$ sort foo
SPECD
specd
$ LANG=en_US sort foo
specd
SPECD
$
The compare works as it should.
The only bug was in my understanding.