Submitted by: Saulius Vabalas (svabalas)

'select ... from ... where ... not in (select ... from ...)' does not return any results when it supposed to. Test case follows below. Issue is already resolved in 2.0.3. As a work around Pavel suggested replacing 'NOT IN' with '<field> <> ALL'. But workaround is also failing in some cases. See CORE1554 for details.

/* prepare metadata */

SET TERM ^ ;
CREATE PROCEDURE TST1
RETURNS (PACKAGES INTEGER)
AS
begin
packages=1;
suspend;
packages=2;
suspend;
end^
SET TERM ; ^

CREATE TABLE FRRATES1 (
FRRATES1 INTEGER NOT NULL,
PACKAGES INTEGER,
primary key(frRates1)
);
commit;

CREATE INDEX IDX_FRRATES1_PACKAGES ON FRRATES1 (PACKAGES);
commit;

CREATE TABLE SCHEDPKGS1 (
SCHEDPKGS1 INTEGER NOT NULL,
SCHEDULE INTEGER,
FRRATES1 INTEGER,
primary key (schedPkgs1)
);
commit;

CREATE INDEX IDX_SCHEDPKGS1_SCHEDULE ON SCHEDPKGS1 (SCHEDULE);
commit;

/* prepare data */

insert into frrates1 (frRates1, packages) values (11, 1);
insert into frrates1 (frRates1, packages) values (12, 2); /* second record is essential (must exist in tst1) */
commit;

insert into schedpkgs1 (schedPkgs1, schedule, frRates1) values(21, 16651,
11); /* existing */
insert into schedpkgs1 (schedPkgs1, schedule, frRates1) values(22, 16651,
null); /* IMPORTANT NULL VALUE */
commit;

/* test queries */

/* sub-query to be used later in sub-select,
correctly uses frRates1 primary key index FR INDEX (RDB$PRIMARY121),
correctly returns (1) */

Select fr.packages
from SchedPkgs1 sp
join frRates1 fr on fr.frRates1=sp.FrRates1
where sp.schedule = 16651;

/* 1. results from stored procedure (1, 2),
filtered out by sub-select query (1),
expected results -- (2),
ib5.6 correctly uses frRates1 primary key index FR INDEX (RDB$PRIMARY121),
ib5.6 returns correct results (2),
PROBLEM -- fb1.5.3 does not return anything,
PROBLEM -- fb1.5.3 uses wrong frRates1 index FR INDEX (IDX_FRRATES1_PACKAGES) */

select packages
from tst1
where packages not in (Select fr.packages
from SchedPkgs1 sp
join frRates1 fr on fr.frRates1=sp.FrRates1
where sp.schedule = 16651);

/* 2. adding additional filter in sub-select query 'fr.packages>0'
fb1.5.3 still uses questionable frRates1 index FR INDEX (IDX_FRRATES1_PACKAGES) but results are as expected (2) */

select packages
from tst1
where packages not in (Select fr.packages
from SchedPkgs1 sp
join frRates1 fr on fr.frRates1=sp.FrRates1
where sp.schedule = 16651 and fr.packages>0);

/* 3. using table instead of stored procedure in main query,
both ib5.6 and fb1.5.3 uses questionable frRates1 index FR INDEX (IDX_FRRATES1_PACKAGES), and results are WRONG, i.e. does not return (2) */

select f2.packages
from frRates1 f2
where f2.packages not in (Select fr.packages
from SchedPkgs1 sp
join frRates1 fr on fr.frRates1=sp.FrRates1
where sp.schedule = 16651);

/* 4. adding the same additional filter 'fr.packages>0' in sub-select query,
INCORRECT results in ib5.6 (no results) (FR INDEX(RDB$PRIMARY121,IDX_FRRATES1_PACKAGES)),
correct results in fb1.5.3 (returns 2)
fb1.5.3 still uses questionable frRates1 index FR INDEX(IDX_FRRATES1_PACKAGES) */

select f2.packages
from frRates1 f2
where f2.packages not in (Select fr.packages
from SchedPkgs1 sp
join frRates1 fr on fr.frRates1=sp.FrRates1
where sp.schedule = 16651 and fr.packages>0);

/* cleanup */

commit;
drop table frRates1;
drop table schedPkgs1;
drop procedure tst1;
commit;