SQLBase 9.0.1 not in (select ...) with empty results set

[Matin Hobbs]

 Posted by:  Matin Hobbs 

Hi,

We have a simple piece of SQL which is returning the wrong result:

select tab1x,tab1y from tab1, tab2 where tab1x = tab2x and tab1y = tab2y
and tab1x not in (select tab3x from tab3)

Without the 'and x not in (select x from tab3)' you get results back from
this query. If the subquery is added and returns an empty result set then
the whole query returns an empty result set even though it should return the
same result set; same as when 'and x not in (select x from tab3)' was
removed from the query.

I have noticed that in the bugs fixed list for PTF1 of 9.0.0 a similar (if
not the same) problem is fixed.

Any ideas?

For the time being I am going to have to rewrite the SQL to get rid of the
sub query.

Best Regards

Martin.

[Mike Vandine]

 Posted by:  Mike Vandine 

Is there any null data in the subselect? If so, this could be defect 85197,
reported in 9.0.1. Try using a 'not exists' instead of a 'not in'. I have
seen that work. Please report back your findings...

Mike

[Matin Hobbs]

 Posted by:  Matin Hobbs 

Hi Mike,

The result set of the sub select is empty (null?). I thought that it might
be related to 82867. How can I find the status/details of 85197?

Best Regards

[Mike Vandine]

 Posted by:  Mike Vandine 

Hi Martin,

Sorry, but I've given you some misinformation. Defect 85197 has actually
been closed as 'as designed'. What you are describing is correct as far as
the SQL standard is concerned. I will try to explain:

When a comparison is made using an IN or NOT IN clause, the whole result set
of the subselect is compared and if ANY rows returned in the subselect are
null, no rows will match the IN or NOT IN clause. For example:

CREATE TABLE Table1 (ID NUMBER NOT NULL, STATE NUMBER, STORE NUMBER);
INSERT INTO TABLE1 values (1,0,1001);
INSERT INTO TABLE1 values (2,0,1020);
INSERT INTO TABLE1 values (3,1,1231);
INSERT INTO TABLE1 values (4,0,NULL);
INSERT INTO TABLE1 values (5,1,1235);
CREATE TABLE Table2 (ID NUMBER NOT NULL, STORE NUMBER NOT NULL, NAME
VARCHAR(40) NOT NULL, SIZE INTEGER NOT NULL);
insert into table2 values (1, 1020, 'Hamburg', 500);
insert into table2 values (2, 1001, 'Berlin', 600);
insert into table2 values (3, 1235, 'Muenchen', 450);
insert into table2 values (4, 1231, 'Dresden', 490);

the statement:

SELECT STORE || ' - ' || NAME FROM Table2
WHERE TABLE2.STORE NOT IN (SELECT DISTINCT STORE FROM TABLE1 WHERE STATE =
0);

finds 1 row in the subselect that is null (4,0,null). Thus, no rows match
the NOT IN clause because the null comparison returns 'unknown'.
Unfortunately, this is the SQL standard... Please see the appropriate links
from Microsoft and Oracle below.

You can also see no rows returned if you modify the subselect to be
constants:

SELECT STORE || ' - ' || NAME FROM Table2
WHERE TABLE2.STORE NOT IN (1001,1020,null);

The way around this is to either include a 'and store is not null' in the
subselect, as in:

SELECT STORE || ' - ' || NAME FROM Table2
WHERE TABLE2.STORE NOT IN (SELECT DISTINCT STORE FROM TABLE1 WHERE STATE =
0 and store is not null );

which will exclude any nulls from the subselect.

A second way around this is to use a 'NOT EXISTS' clause, like:

SELECT STORE || ' - ' || NAME FROM Table2
WHERE NOT EXISTS (SELECT STORE FROM TABLE1 WHERE store = TABLE2.STORE and
STATE = 0);

This looks almost the same as the first select. However, the subselect is
actually a correlated select and the result would never return the null row
and thus never negate the whole NOT EXISTS comparison.

I hope this helps with your understanding. Here are the links
to the papers:

Oracle Link http://builder.com.com/5100-6388_14-5319615.html

Microsoft Link http://msdn2.microsoft.com/en-us/library/ms177682.aspx


Mike