[Firebird-devel] [FB-Tracker] Created: (CORE-1555) 'select ...from...where...not in (select...from...)' no results

[Saulius V. (JIRA) <tr...@fi...>]

'select ...from...where...not in (select...from...)' no results
---------------------------------------------------------------

                 Key: CORE-1555
                 URL: http://tracker.firebirdsql.org/browse/CORE-1555
             Project: Firebird Core
          Issue Type: Bug
          Components: Engine
    Affects Versions: 1.5.4, 1.5.5
         Environment: OS Independant. CS & SS both affected
            Reporter: Saulius Vabalas


 '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 CORE-1554 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;


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira