Problem with a subquery

forum.sqlbase (2000-2010)
NewsgroupServer
Robot
Robot
Posts: 115623
Joined: 24 Feb 2017, 12:00
Location: World wide

Problem with a subquery

Post by NewsgroupServer » 09 Jul 2010, 13:40

 Posted by:  Rainer Gareis 

Hi,

I get a wrong result set from a query with subquery.
Sqlbase 11.5.2

select count(NR) from table1 where date > 2010-01-01 ID in ('05003849' );
Result: 14, PASS

select count(nr) from table2 where date > 2010-01-01 ID in ( '05003849' );
Result: 14, PASS

Correlation between the two tables:
table1 table2
0007A000551 0007A000551
0007A000587 0007A000587
0007A000830 0007A000830
0007A000856 0007A000856
0103B001026 0103B001026
0103B001088 0103B001088
0103B001138 0103B001138
0104B001221 0104B001221
0104B001227 0104B001227
01A9810A000158
0310B002638
0310B002643
0310B002652
0310B002655
0104B001329
0104B001330
0202B002152
0310B002569
9905A000311

#Query 1 with syncronized subquery
select NR, ID from table1 where @year(date) + 1900 = 2010 and ID in (
'05003849' ) and NR not in (select nr from table2 where @year(date) +
1900 = 2010 and ID = table1.ID ) ;

Result: PASS
0310B002638 05003849
0310B002643 05003849
0310B002652 05003849
0310B002655 05003849
01A9810A000158 05003849

#Query 2 with unsyncronized subquery
select NR, ID from table1 where @year(date) + 1900 = 2010 and ID in (
'05003849' ) and NR not in (select nr from table2 where @year(date) +
1900 = 2010) ;

Result: FAIL
NULL

#Query 3 with syncronized subquery
select NR, ID from table1 where @year(date) + 1900 = 2010 and ID in (
'05003849' ) and NR in (select nr from table2 where @year(date) + 1900 =
2010 and table2.ID = table1.ID ) ;

Result: PASS
0007A000551 05003849
0007A000587 05003849
0007A000830 05003849
0007A000856 05003849
0103B001026 05003849
0103B001088 05003849
0103B001138 05003849
0104B001221 05003849
0104B001227 05003849

#Query 4 with unsyncronized subquery
select NR, ID from table1 where @year(date) + 1900 = 2010 and ID in (
'05003849' ) and NR in (select nr from table2 where @year(date) + 1900 =
2010);
Result: PASS
0007A000551 05003849
0007A000587 05003849
0007A000830 05003849
0007A000856 05003849
0103B001026 05003849
0103B001088 05003849
0103B001138 05003849
0104B001221 05003849
0104B001227 05003849

For me, Query 1 and Query 2 should have the same result set. Is this a
bug or is something wrong with my query?

Any hints are appreciated.

Best regards
Rainer

NewsgroupServer
Robot
Robot
Posts: 115623
Joined: 24 Feb 2017, 12:00
Location: World wide

Problem with a subquery

Post by NewsgroupServer » 09 Jul 2010, 16:10

 Posted by:  Elio Boezio 

Hi Ranier,

We have had what looks like a similar problem with an IBM Universal DB2 data base, where the table in the subquery contained NULL values (more confusing, is that it "suddenly" started happening for us and we suspect a "rogue" app is putting unwanted NULL values into the table instead of zeroes. We're still looking...).

Also, one of my developers came across this article:

________________________________________________________________________________________________

from: Alexander Kuznetsov ( http://sqlblog.com/blogs/alexander_kuznetsov/default.aspx)

Defensive database programming: rewriting queries with NOT IN().

The behavior of NOT IN clause may be confusing and as such it needs some explanations. Consider the following query:
SELECT LastName, FirstName FROM Person.Contact WHERE LastName NOT IN('Hedlund', 'Holloway', NULL)

Although there are more than a thousand distinct last names in AdventureWorks.Person.Contact, the query returns nothing. This may look counterintuitive to a beginner database programmer, but it actually makes perfect sense. The explanation consist of several simple steps. First of all, consider the following two queries, which are clearly equivalent:

SELECT LastName, FirstName FROM Person.Contact
WHERE LastName IN('Hedlund', 'Holloway', NULL)

SELECT LastName, FirstName FROM Person.Contact
WHERE LastName='Hedlund' OR LastName='Holloway' OR LastName=NULL

Note that both queries return expected results. Now, let us recall DeMorgan's theorem, which states that:

not (P and Q) = (not P) or (not Q)
not (P or Q) = (not P) and (not Q)

I am cutting and pasting from Wikipedia (http://en.wikipedia.org/wiki/De_Morgan_duality). Applying DeMorgan's theorem to this queries, it follows that these two queries are also equivalent:
SELECT LastName, FirstName FROM Person.Contact WHERE LastName NOT IN('Hedlund', 'Holloway', NULL)

SELECT LastName, FirstName FROM Person.Contact
WHERE LastName'Hedlund' AND LastName'Holloway' AND LastNameNULL

Note that when I run both queries against AdventureWorks database, they both return empty result sets. The reason is simple - the condition LastNameNULL can never be true (unless you play with ANSI_NULLS setting, which you should not be doing anyway). Now that you understand why queries with NOT IN can return empty result sets, consider this query:

SELECT FROM SomeTable s
WHERE s.LastName NOT IN(SELECT LastName FROM Person.Contact WHERE )
At the time of this writing Person.Contact.LastName is not nullable, and in the short term it is perfectly safe to write such queries. In the long term, however, database schemas can and do change. At some time later on the column Person.Contact.LastName might become nullable. Come to think of it, Person.Contact might become a view instead of a table. To be on the safe side, you can either rewrite your query using NOT EXISTS clause instead of NOT IN, or you can explicitly ensure that Person.Contact.LastName is not nullable, as follows:
SELECT FROM SomeTable s
WHERE s.LastName NOT IN(SELECT LastName FROM Person.Contact
WHERE LastName IS NOT NULL AND )

Either way, your modified query will not break when the underlying database schema changes.

________________________________________________________________________________________________

HTH,
Elio

Hi,

I get a wrong result set from a query with subquery.
Sqlbase 11.5.2

select count(NR) from table1 where date > 2010-01-01 ID in ('05003849' );
Result: 14, PASS

select count(nr) from table2 where date > 2010-01-01 ID in ( '05003849' );
Result: 14, PASS

Correlation between the two tables:
table1table2
0007A0005510007A000551
0007A0005870007A000587
0007A0008300007A000830
0007A0008560007A000856
0103B0010260103B001026
0103B0010880103B001088
0103B0011380103B001138
0104B0012210104B001221
0104B0012270104B001227
01A9810A000158
0310B002638
0310B002643
0310B002652
0310B002655
0104B001329
0104B001330
0202B002152
0310B002569
9905A000311

#Query 1 with syncronized subquery
select NR, ID from table1 where @year(date) + 1900 = 2010 and ID in (
'05003849' ) and NR not in (select nr from table2 where @year(date) +
1900 = 2010 and ID = table1.ID ) ;

Result:PASS
0310B00263805003849
0310B00264305003849
0310B00265205003849
0310B00265505003849
01A9810A00015805003849

#Query 2 with unsyncronized subquery
select NR, ID from table1 where @year(date) + 1900 = 2010 and ID in (
'05003849' ) and NR not in (select nr from table2 where @year(date) +
1900 = 2010) ;

Result: FAIL
NULL

#Query 3 with syncronized subquery
select NR, ID from table1 where @year(date) + 1900 = 2010 and ID in (
'05003849' ) and NR in (select nr from table2 where @year(date) + 1900 =
2010 and table2.ID = table1.ID ) ;

Result: PASS
0007A00055105003849
0007A00058705003849
0007A00083005003849
0007A00085605003849
0103B00102605003849
0103B00108805003849
0103B00113805003849
0104B00122105003849
0104B00122705003849

#Query 4 with unsyncronized subquery
select NR, ID from table1 where @year(date) + 1900 = 2010 and ID in (
'05003849' ) and NR in (select nr from table2 where @year(date) + 1900 =
2010);
Result: PASS
0007A00055105003849
0007A00058705003849
0007A00083005003849
0007A00085605003849
0103B00102605003849
0103B00108805003849
0103B00113805003849
0104B00122105003849
0104B00122705003849

For me, Query 1 and Query 2 should have the same result set. Is this a
bug or is something wrong with my query?

Any hints are appreciated.

Best regards
Rainer

NewsgroupServer
Robot
Robot
Posts: 115623
Joined: 24 Feb 2017, 12:00
Location: World wide

Problem with a subquery

Post by NewsgroupServer » 12 Jul 2010, 14:33

 Posted by:  Rainer Gareis 

Hi Elio,

thanks for your response. The article is very helpful.

Rainer

Return to “forum.sqlbase”

Who is online

Users browsing this forum: [Ccbot] and 0 guests