Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id E492763258C for ; Tue, 9 Mar 2010 10:24:25 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 05477-04 for ; Tue, 9 Mar 2010 14:24:06 +0000 (UTC) X-Greylist: delayed 00:15:48.607783 by SQLgrey-1.7.6 Received: from toq9-srv.bellnexxia.net (toq9.bellnexxia.net [209.226.175.116]) by mail.postgresql.org (Postfix) with ESMTP id 16387632CC4 for ; Tue, 9 Mar 2010 10:24:14 -0400 (AST) Received: from toip3.srvr.bell.ca ([209.226.175.86]) by tomts22-srv.bellnexxia.net (InterMail vM.5.01.06.13 201-253-122-130-113-20050324) with ESMTP id <20100309140812.KNXL22975.tomts22-srv.bellnexxia.net@toip3.srvr.bell.ca> for ; Tue, 9 Mar 2010 09:08:12 -0500 X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: AuIEAC7klUvPI+0k/2dsb2JhbACBRJkNgUy7FoR5BIMX Received: from pop.cryptocard.com (HELO smtp.cryptocard.com) ([207.35.237.36]) by toip3.srvr.bell.ca with ESMTP; 09 Mar 2010 08:56:17 -0500 Received: from pop.cryptocard.com (localhost [127.0.0.1]) by smtp.cryptocard.com (Spam & Virus Firewall) with ESMTP id 9A55D27B21E for ; Tue, 9 Mar 2010 09:08:08 -0500 (EST) Received: from pop.cryptocard.com ([192.168.10.27]) by smtp.cryptocard.com with ESMTP id DXon5iWCWCmM0E8x for ; Tue, 09 Mar 2010 09:08:08 -0500 (EST) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01CABF91.ED4E4F2E" X-MimeOLE: Produced By Microsoft Exchange V6.5 Subject: Odd query behavior Date: Tue, 9 Mar 2010 09:08:10 -0500 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Odd query behavior thread-index: Acq/kfNls8xS2e04T3uZ0ROtaDjfHQ== From: "Dan McFadyen" To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/83 X-Sequence-Number: 33790 This is a multi-part message in MIME format. ------_=_NextPart_001_01CABF91.ED4E4F2E Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hello, =20 I've come across an odd situation. I've had access to a database where a the following happens: =20 " SELECT * FROM table WHERE name LIKE 'abc%' " returns 2 rows... =20 but... =20 " SELECT * FROM table WHERE name IN (SELECT name FROM table WHERE name LIKE 'abc%') " returns 0 rows... =20 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. =20 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. =20 I do know that server it was running on was not an english server, but don't know the exact language. The database uses a UTF8 encoding though, so I don't know what that would make any difference. =20 Any ideas about things I should be looking at? =20 Thanks =20 Dan The information transmitted is intended only for the person or entity to wh= ich 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 en= tities other than the intended recipient is prohibited. If you received thi= s in error, please contact the sender immediately and delete the material f= rom any computer. Please see our legal details at http://www.cryptocard.co= m=0D CRYPTOCard Inc. is registered in the province of Ontario, Canada with Busin= ess number 80531 6478. CRYPTOCard Europe is limited liability company regi= stered in England and Wales (with registered number 05728808 and VAT number= 869 3979 41); its registered office is Aztec Centre, Aztec West, Almondsbu= ry, Bristol, UK, BS32 4TD=0D =0D ------_=_NextPart_001_01CABF91.ED4E4F2E Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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%' &quo= t;    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 g= et 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. Bot= h queries return 2 rows.

 

I do know that server it was running on was not an eng= lish server, but don't know the exact language. 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 wh= ich 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 en= tities other than the intended recipient is prohibited. If you received thi= s in error, please contact the sender immediately and delete the material f= rom any computer. Please see our legal details at http://www.cryptocard.co= m=0D CRYPTOCard Inc. is registered in the province of Ontario, Canada with Busin= ess number 80531 6478. CRYPTOCard Europe is limited liability company regi= stered in England and Wales (with registered number 05728808 and VAT number= 869 3979 41); its registered office is Aztec Centre, Aztec West, Almondsbu= ry, Bristol, UK, BS32 4TD ------_=_NextPart_001_01CABF91.ED4E4F2E--