Обсуждение: 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.