subqueryflattening.sql

derby database source code.good for you.

insert into colls values ('36', 1);
insert into colls values ('37', 8);

insert into docs values '24', '25', '36', '27', '124', '567';

call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
maximumdisplaywidth 40000;

-- NOT IN is flattened
SELECT COUNT(*) FROM
( SELECT ID FROM DOCS WHERE
        ( ID NOT IN (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) ) )
) AS TAB;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- NOT EXISTS is flattened
SELECT COUNT(*) FROM
( SELECT ID FROM DOCS WHERE
        ( NOT EXISTS  (SELECT ID FROM COLLS WHERE DOCS.ID = COLLS.ID
AND COLLID IN (-2,1) ) )
) AS TAB;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- EXISTS is flattened
SELECT COUNT(*) FROM
( SELECT ID FROM DOCS WHERE
        ( EXISTS  (SELECT ID FROM COLLS WHERE DOCS.ID = COLLS.ID
AND COLLID IN (-2,1) ) )
) AS TAB;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- IN is flattened
SELECT count(ID) FROM DOCS WHERE ID IN (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- ANY is flattened
SELECT count(ID) FROM DOCS WHERE ID > ANY (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- ANY is flattened
SELECT count(ID) FROM DOCS WHERE ID <> ANY (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- ALL is flattened, what's not?
SELECT count(ID) FROM DOCS WHERE ID = ALL (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- ALL is flattened, what's not?
SELECT count(ID) FROM DOCS WHERE ID < ALL (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- ALL is flattened, what's not?
SELECT count(ID) FROM DOCS WHERE ID <> ALL (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- Now test nullable correlated columns
drop table colls;
-- the only change is ID is now nullable
CREATE TABLE "APP"."COLLS" ("ID" VARCHAR(128), "COLLID" SMALLINT NOT NULL);
CREATE INDEX "APP"."NEW_INDEX3" ON "APP"."COLLS" ("COLLID");
CREATE INDEX "APP"."NEW_INDEX2" ON "APP"."COLLS" ("ID");
ALTER TABLE "APP"."COLLS" ADD CONSTRAINT "NEW_KEY2" UNIQUE ("ID", "COLLID");
insert into colls values ('123', 2);
insert into colls values ('124', -5);
insert into colls values ('24', 1);
insert into colls values ('26', -2);
insert into colls values ('36', 1);
insert into colls values ('37', 8);
insert into colls values (null, -2);
insert into colls values (null, 1);
insert into colls values (null, 8);

-- NOT EXISTS should be flattened
SELECT COUNT(*) FROM
( SELECT ID FROM DOCS WHERE
        ( NOT EXISTS  (SELECT ID FROM COLLS WHERE DOCS.ID = COLLS.ID
AND COLLID IN (-2,1) ) )
) AS TAB;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- EXISTS should be flattened
SELECT COUNT(*) FROM
( SELECT ID FROM DOCS WHERE
        ( EXISTS  (SELECT ID FROM COLLS WHERE DOCS.ID = COLLS.ID
AND COLLID IN (-2,1) ) )
) AS TAB;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- IN should be flattened
SELECT count(ID) FROM DOCS WHERE ID IN (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- ANY should be flattened
SELECT count(ID) FROM DOCS WHERE ID > ANY (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- ALL should NOT be flattened, but subquery should be materialized
SELECT count(ID) FROM DOCS WHERE ID <> ALL (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();


-- Now we make the other correlated column also nullable
drop table docs;
CREATE TABLE "APP"."DOCS" ("ID" VARCHAR(128));
CREATE INDEX "APP"."NEW_INDEX1" ON "APP"."DOCS" ("ID");

insert into docs values '24', '25', '36', '27', '124', '567';
insert into docs values null;

-- NOT EXISTS should be flattened
SELECT COUNT(*) FROM
( SELECT ID FROM DOCS WHERE
        ( NOT EXISTS  (SELECT ID FROM COLLS WHERE DOCS.ID = COLLS.ID
AND COLLID IN (-2,1) ) )
) AS TAB;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- EXISTS should be flattened
SELECT COUNT(*) FROM
( SELECT ID FROM DOCS WHERE
        ( EXISTS  (SELECT ID FROM COLLS WHERE DOCS.ID = COLLS.ID
AND COLLID IN (-2,1) ) )
) AS TAB;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- IN should be flattened
SELECT count(ID) FROM DOCS WHERE ID IN (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- ANY should be flattened
SELECT count(ID) FROM DOCS WHERE ID > ANY (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- ALL should NOT be flattened, but subquery should be materialized, watch out results
SELECT count(ID) FROM DOCS WHERE ID <> ALL (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

drop table t1;
drop table t2;
drop table t3;
drop table t4;
create table t1 (c1 int not null);
create table t2 (c1 int not null);
create table t3 (c1 int not null);
create table t4 (c1 int);

insert into t1 values 1,2,3,4,5,1,2;
insert into t2 values 1,4,5,1,1,5,4;
insert into t3 values 4,4,3,3;
insert into t4 values 1,1,2,2,3,4,5,5;

-- should return 2,3,2
select * from t1 where not exists (select * from t2 where t1.c1=t2.c1);
-- should be flattened
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

select * from t1 where not exists (select * from t2 where t1.c1=t2.c1 and t2.c1 not in (select t3.c1 from t3, t4));
-- watch out result, should return 2,3,4,2
-- can not be flattened, should be materialized
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

select * from t1 where exists (select * from t2 where t1.c1=t2.c1 and t2.c1 not in (select t3.c1 from t3, t4));
-- should return 1,5,1
-- can not be flattened, should be materialized
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

drop table colls;
drop table docs;
drop table t1;
drop table t2;
drop table t3;
drop table t4;

-- Test case for DERBY-558: optimizer hangs in rare cases where
-- multiple subqueries flattened to EXISTS put multiple restrictions
-- on legal join orders.

create table digits (d int);
insert into digits values 1, 2, 3, 4, 5, 6, 7, 8, 9, 0;

create table odd (o int);
insert into odd values 1, 3, 5, 7, 9;
commit;

-- In order to test this, "noTimeout" must be true so that
-- the optimizer will run through all of the possible join
-- orders before it quits.  In the case of DERBY-558 the
-- optimizer was getting stuck in a logic loop and thus never
-- quit, causing the hang.  NOTE: The "noTimeout" property
-- is set in the subqueryFlattening_derby.properties file.

select distinct temp_t0.d from 
	(select d from digits where d > 3) temp_t0,
	(select o from odd) temp_t1,
	odd temp_t4,
	(select o from odd) temp_t3
	where temp_t0.d = temp_t1.o
		and temp_t0.d = temp_t3.o
		and temp_t0.d in (select o from odd where o = temp_t1.o)
 		and exists (
			select d from digits
				where d = temp_t0.d)

-- Before fix for DERBY-558, we would HANG (loop indefinitely) here;
-- after fix, we should see three rows returned.
;

-- clean-up.

drop table digits;
drop table odd;