Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 95739632FB3 for ; Fri, 12 Mar 2010 11:42:01 -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 76126-01 for ; Fri, 12 Mar 2010 15:41:41 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from tomts5-srv.bellnexxia.net (tomts5.bellnexxia.net [209.226.175.25]) by mail.postgresql.org (Postfix) with ESMTP id 1F8A3632DB8 for ; Fri, 12 Mar 2010 11:41:45 -0400 (AST) Received: from toip4.srvr.bell.ca ([209.226.175.87]) by tomts5-srv.bellnexxia.net (InterMail vM.5.01.06.13 201-253-122-130-113-20050324) with ESMTP id <20100312154131.ZLLD2554.tomts5-srv.bellnexxia.net@toip4.srvr.bell.ca> for ; Fri, 12 Mar 2010 10:41:31 -0500 X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: AjkFACPvmUvPI+0k/2dsb2JhbACBRJhKgU+7QYR7BIMX Received: from pop.cryptocard.com (HELO smtp.cryptocard.com) ([207.35.237.36]) by toip4.srvr.bell.ca with ESMTP; 12 Mar 2010 11:04:19 -0500 Received: from pop.cryptocard.com (localhost [127.0.0.1]) by smtp.cryptocard.com (Spam & Virus Firewall) with ESMTP id 0488816DA17 for ; Fri, 12 Mar 2010 10:41:26 -0500 (EST) Received: from pop.cryptocard.com (exchange03.cryptocard.internal [192.168.10.27]) by smtp.cryptocard.com with ESMTP id uSKdWpQVbNKJeBeA for ; Fri, 12 Mar 2010 10:41:26 -0500 (EST) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01CAC1FA.7536A7FC" X-MimeOLE: Produced By Microsoft Exchange V6.5 Subject: Odd query behavior Date: Fri, 12 Mar 2010 10:41:30 -0500 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Odd query behavior thread-index: AcrB+nxueuOn4cv8QTeGZW662GpzeA== 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/66 X-Sequence-Number: 33773 This is a multi-part message in MIME format. ------_=_NextPart_001_01CAC1FA.7536A7FC 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 Finnish. 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 =20 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_01CAC1FA.7536A7FC 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%' "    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. Both queries return 2 rows.

 

I do know that server it was running on was Finnish. T= he 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_01CAC1FA.7536A7FC--