Thread: Odd query behavior
<div class="Section1"><p class="MsoNormal">Hello,<p class="MsoNormal"> <p class="MsoNormal">I've come across an odd situation.I've had access to a database where a the following happens:<p class="MsoNormal"> <p class="MsoNormal">" SELECT* FROM table WHERE name LIKE 'abc%' " returns 2 rows...<p class="MsoNormal"> <p class="MsoNormal">but...<p class="MsoNormal"> <pclass="MsoNormal">" SELECT * FROM table WHERE name IN (SELECT name FROM table WHERE name LIKE 'abc%')" returns 0 rows...<p class="MsoNormal"> <p class="MsoNormal">I've also tried doing a join on the column to anothertable that has what appears to be the same data, and the join comes out with zero rows. Weirder yet, I took the hashof both of the values and it came out identical.<p class="MsoNormal"> <p class="MsoNormal">Now, I know the first thingyou'll ask is if you can get a copy of the data to re-produce it, and the problem is, I got a copy of the data in question,and loaded into another server and it works fine. Both queries return 2 rows.<p class="MsoNormal"> <p class="MsoNormal">Ido know that server it was running on was Finnish. The database uses a UTF8 encoding though, so I don'tknow what that would make any difference.<p class="MsoNormal"> <p class="MsoNormal">Any ideas about things I shouldbe looking at?<p class="MsoNormal"> <p class="MsoNormal">Thanks<p class="MsoNormal"> <p class="MsoNormal">Dan<p class="MsoNormal"> </div>The information transmitted is intended only for the person or entity to which it is addressed andmay contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not representthose of the company. Any review, retransmission, dissemination or other use of, or taking of any action in relianceupon, this information by persons or entities other than the intended recipient is prohibited. If you received thisin error, please contact the sender immediately and delete the material from any computer. Please see our legal detailsat http://www.cryptocard.com CRYPTOCard Inc. is registered in the province of Ontario, Canada with Business number80531 6478. CRYPTOCard Europe is limited liability company registered in England and Wales (with registered number05728808 and VAT number 869 3979 41); its registered office is Aztec Centre, Aztec West, Almondsbury, Bristol, UK,BS32 4TD
Any views involved, or separate users/roles? On 03/12/2010 08:41 AM, Dan McFadyen wrote: > Hello, > > > > I've come across an odd situation. I've had access to a database where a > the following happens: > > > > " SELECT * FROM table WHERE name LIKE 'abc%' " returns 2 rows... > > > > but... > > > > " SELECT * FROM table WHERE name IN (SELECT name FROM table WHERE name > LIKE 'abc%') " returns 0 rows... > > > > I've also tried doing a join on the column to another table that has > what appears to be the same data, and the join comes out with zero rows. > Weirder yet, I took the hash of both of the values and it came out > identical. > > > > Now, I know the first thing you'll ask is if you can get a copy of the > data to re-produce it, and the problem is, I got a copy of the data in > question, and loaded into another server and it works fine. Both queries > return 2 rows. > > > > I do know that server it was running on was Finnish. The database uses a > UTF8 encoding though, so I don't know what that would make any difference. > > > > Any ideas about things I should be looking at? > > > > Thanks > > > > Dan > > > > The information transmitted is intended only for the person or entity to > which it is addressed and may contain confidential and/or privileged > material. Statements and opinions expressed in this e-mail may not > represent those of the company. Any review, retransmission, > dissemination or other use of, or taking of any action in reliance upon, > this information by persons or entities other than the intended > recipient is prohibited. If you received this in error, please contact > the sender immediately and delete the material from any computer. Please > see our legal details at http://www.cryptocard.com CRYPTOCard Inc. is > registered in the province of Ontario, Canada with Business number 80531 > 6478. CRYPTOCard Europe is limited liability company registered in > England and Wales (with registered number 05728808 and VAT number 869 > 3979 41); its registered office is Aztec Centre, Aztec West, > Almondsbury, Bristol, UK, BS32 4TD
"Dan McFadyen" <danm@cryptocard.com> writes:
> I've come across an odd situation. I've had access to a database where a
> the following happens:
What are the plans for the two queries? If either one involves use of
an index, does disabling the index change the results?
> I do know that server it was running on was Finnish. The database uses a
> UTF8 encoding though, so I don't know what that would make any
> difference.
The other line of thought that suggests is that you've got an
encoding/locale mismatch that's messing up text comparisons. What's the
server's LC_CTYPE setting, and what PG version is this anyway?
regards, tom lane
On 03/12/10 18:41, Dan McFadyen wrote: > Hello, > > I've come across an odd situation. I've had access to a database where a > the following happens: > > " SELECT * FROM table WHERE name LIKE 'abc%' " returns 2 rows... > > but... > > " SELECT * FROM table WHERE name IN (SELECT name FROM table WHERE name > LIKE 'abc%') " returns 0 rows... I am sorry if it was already spoken. SELECT name FROM table WHERE name LIKE 'abc%' EXPLAIN SELECT name FROM table WHERE name LIKE 'abc%' EXPLAIN SELECT * FROM table WHERE name IN (SELECT name FROM table WHERE name LIKE 'abc%')
"Dan McFadyen" <danm@cryptocard.com> writes:
> You're right, the second one does use an index, one that is used to
> enforce a unique constraint on the column. I wasn't able to turn it off
> as the database is currently in use, and disabling a unique constraint
> probably isn't a good idea.
> Sorry for not mentioning the PG version, it's 8.3.
> Now, as for the LC_CTYPE, I didn't even know about such a thing till you
> mentioned it, and I found the lovely disclaimers in the postgresql docs
> about the consequences of it not being c or posix.
> It's Finnish Finland 1252. If that's the probable cause, I'm guessing
> the only option is to export, re-initdb, and import.
Oh, this is Windows huh? In principle, UTF8 encoding should work in all
locales for Windows --- at least, the code thinks so, which is why it
let you create that combination to start with. I'm not very familiar
with the possible pitfalls though. If you don't actually have any use
for Finnish-specific sorting or case-folding, a re-initdb in C locale
would probably be a good idea.
In the meantime it seems there might be a bug here. Can you give more
information about the contents of the column involved, in particular
what non-7-bit-ASCII characters it contains?
regards, tom lane
PS: please cc the list on replies.
Bah... yes Windows.
For being a developer I certainly miss all the details that make a good
bug report/question. Also, sorry about the CC, don't post to lists often
and I forget.
Now, the interesting part is for all I can tell, there are no special
characters in the field. Unless the latest version of Pgadmin (1.10.1 or
1.10.2) hides this from the UI, either that or it's some character that
renders into a similar glyph as ASCII.
Is there any way to force hex/binary in a SQL statement?
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, March 15, 2010 11:04 AM
To: Dan McFadyen
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Odd query behavior
"Dan McFadyen" <danm@cryptocard.com> writes:
> You're right, the second one does use an index, one that is used to
> enforce a unique constraint on the column. I wasn't able to turn it
off
> as the database is currently in use, and disabling a unique constraint
> probably isn't a good idea.
> Sorry for not mentioning the PG version, it's 8.3.
> Now, as for the LC_CTYPE, I didn't even know about such a thing till
you
> mentioned it, and I found the lovely disclaimers in the postgresql
docs
> about the consequences of it not being c or posix.
> It's Finnish Finland 1252. If that's the probable cause, I'm guessing
> the only option is to export, re-initdb, and import.
Oh, this is Windows huh? In principle, UTF8 encoding should work in all
locales for Windows --- at least, the code thinks so, which is why it
let you create that combination to start with. I'm not very familiar
with the possible pitfalls though. If you don't actually have any use
for Finnish-specific sorting or case-folding, a re-initdb in C locale
would probably be a good idea.
In the meantime it seems there might be a bug here. Can you give more
information about the contents of the column involved, in particular
what non-7-bit-ASCII characters it contains?
regards, tom lane
PS: please cc the list on replies.
The information transmitted is intended only for the person or entity to which it is addressed and may contain
confidentialand/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the
company.Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this
informationby persons or entities other than the intended recipient is prohibited. If you received this in error,
pleasecontact the sender immediately and delete the material from any computer. Please see our legal details at
http://www.cryptocard.com
CRYPTOCard Inc. is registered in the province of Ontario, Canada with Business number 80531 6478. CRYPTOCard Europe is
limitedliability company registered in England and Wales (with registered number 05728808 and VAT number 869 3979 41);
itsregistered office is Aztec Centre, Aztec West, Almondsbury, Bristol, UK, BS32 4TD
"Dan McFadyen" <danm@cryptocard.com> writes:
> Now, the interesting part is for all I can tell, there are no special
> characters in the field. Unless the latest version of Pgadmin (1.10.1 or
> 1.10.2) hides this from the UI, either that or it's some character that
> renders into a similar glyph as ASCII.
> Is there any way to force hex/binary in a SQL statement?
Huh. Try pg_dump'ing the table and then searching the file for any
bytes with high bit set.
It's possible that Finnish locale sorts some of the plain-ASCII
characters differently than C locale would, but I don't know of
a reason for that to break anything.
regards, tom lane