Postgres Pro
Downloads
Home   >   mailing lists

Thread: Strange query behavior where clause produces odd behavior on '>' query

Strange query behavior where clause produces odd behavior on '>' query

From
"Dann Corbit"
Date:
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?

Re: Strange query behavior where clause produces odd behavior on '>' query

From
Tom Lane
Date:
"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

Re: Strange query behavior where clause produces odd behavior on '>' query

From
"Dann Corbit"
Date:
> -----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?

Re: Strange query behavior where clause produces odd behavior on '>' query

From
Tom Lane
Date:
"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

Re: Strange query behavior where clause produces odd behavior on '>' query

From
"Dann Corbit"
Date:
> -----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.