subqueryflattening.sql

derby database source code.good for you.

-- test subquery flattening into outer query block

set isolation to rr;

-- tests for flattening a subquery based on a
-- uniqueness condition

-- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;

-- create some tables
create table outer1 (c1 int, c2 int, c3 int);
create table outer2 (c1 int, c2 int, c3 int);
create table noidx (c1 int);
create table idx1 (c1 int);
create unique index idx1_1 on idx1(c1);
create table idx2 (c1 int, c2 int);
create unique index idx2_1 on idx2(c1, c2);
create table nonunique_idx1 (c1 int);
create index nonunique_idx1_1 on nonunique_idx1(c1);


insert into outer1 values (1, 2, 3);
insert into outer1 values (4, 5, 6);
insert into outer2 values (1, 2, 3);
insert into outer2 values (4, 5, 6);
insert into noidx values 1, 1;
insert into idx1 values 1, 2;
insert into idx2 values (1, 1), (1, 2);
insert into nonunique_idx1 values 1, 1;

-- cases where subqueries don't get flattened
-- (we would get incorrect results with 
-- incorrect flattening)
-- one of tables in subquery doesn't have index
select * from outer1 where c1 in (select idx1.c1 from noidx, idx1 where idx1.c1 = noidx.c1);
-- group by in subquery
select * from outer1 o where c1 <= (select c1 from idx1 i group by c1);
-- otherwise flattenable subquery under an or 
-- subquery returns no rows
select * from outer1 o where c1 + 0 = 1 or c1 in (select c1 from idx1 i where i.c1 = 0);
select * from outer1 o where c1 in (select c1 from idx1 i where i.c1 = 0) or c1 + 0 = 1;
-- empty subquery in select list which is otherwise flattenable
select (select c1 from idx1 where c1 = 0) from outer1;
-- multiple tables in subquery
-- no one table's equality condition based
-- solely on constants and correlation columns
select * from outer1 o where exists (select * from idx2 i, idx1 where o.c1 = i.c1 and i.c2 = idx1.c1);

-- subqueries that should get flattened
call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
maximumdisplaywidth 40000;

-- simple IN
select * from outer1 o where o.c1 in (select c1 from idx1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- simple EXISTS
select * from outer1 o where exists (select * from idx1 i where o.c1 = i.c1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- simple ANY
select * from outer1 o where o.c1 = ANY (select c1 from idx1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- another simple ANY
select * from outer1 o where o.c2 > ANY (select c1 from idx1 i where o.c1 = i.c1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- comparisons with parameters
prepare p1 as 'select * from outer1 o where exists (select * from idx1 i where i.c1 = ?)';
execute p1 using 'values 1';
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
prepare p2 as 'select * from outer1 o where ? = ANY (select c1 from idx1)';
execute p2 using 'values 1';
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- mix constants with correlation columns
select * from outer1 o where exists (select * from idx2 i where o.c1 = i.c1 and i.c2 = 2);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- multiple tables in subquery
select * from outer1 o where exists (select * from idx2 i, idx1 where o.c1 = i.c1 and i.c2 = idx1.c1 and i.c2 = 1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- comparisons with non-join expressions
select * from outer1 o where exists (select * from idx1 where idx1.c1 = 1 + 0);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select * from outer1 o where exists (select * from idx2 i, idx1 where o.c1 + 0 = i.c1 and i.c2 + 0 = idx1.c1 and i.c2 = 1 + 0);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- multilevel subqueries
-- flatten all
select * from outer1 o where exists
    (select * from idx2 i where exists
        (select * from idx1 ii 
         where o.c1 = i.c1 and i.c2 = ii.c1 and i.c2 = 1));
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- only flatten bottom
select * from outer1 o where exists
    (select * from idx2 i where exists
        (select * from idx1 ii 
         where o.c1 = i.c1 and i.c2 = ii.c1));
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- flatten innermost into exists join, then flatten middle
-- into outer
select * from outer1 o where exists
    (select * from idx2 i 
     where  o.c1 = i.c1 and i.c2 = 1 and exists
        (select * from idx1 ii));
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- flatten a subquery that has a subquery in its select list
-- verify that subquery gets copied up to outer block
select * from outer1 o where c1 in
    (select (select c1 from idx1 where c1 = i.c1)
     from idx2 i where o.c1 = i.c1 and i.c2 = 1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- expression subqueries
-- simple =
select * from outer1 o where o.c1 = (select c1 from idx1 i where o.c1 = i.c1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select * from outer1 o where o.c1 <= (select c1 from idx1 i where o.c1 = i.c1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- multiple tables in subquery
select * from outer1 o where c1 =  (select i.c1 from idx2 i, idx1 where o.c1 = i.c1 and i.c2 = idx1.c1 and i.c2 = 1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- flattening to an exists join
-- no index on subquery table
select * from outer1 where c1 in (select c1 from noidx);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- no unique index on subquery table
select * from outer1 where c1 in (select c1 from nonunique_idx1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- columns in subquery are not superset of unique index
select * from outer1 where c1 in (select c1 from idx2);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- single table subquery, self join on unique column
select * from outer1 where exists (select * from idx1 where c1 = c1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- flattening values subqueries
-- flatten unless contains a subquery
select * from outer1 where c1 in (values 1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select * from outer1 where c1 in (values (select max(c1) from outer1));
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- beetle 4459 - problems with flattening to exist joins and then flattening to 
-- normal join
-- non correlated exists subquery with conditional join
maximumdisplaywidth 40000;
select o.c1 from outer1 o join outer2 o2 on (o.c1 = o2.c1) 
where exists (select c1 from idx1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- in predicate (will be flattened to exists)
select o.c1 from outer1 o join outer2 o2 on (o.c1 = o2.c1) 
where o.c1 in (select c1 from idx1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- flattened exists join in nested subquery
select c1 from (select t.c1 from (select o.c1 from outer1 o join outer2 o2 on (o.c1 = o2.c1) where exists (select c1 from idx1)) t, outer2 where t.c1 = outer2.c1) t2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- original reported bug
create table business(businesskey int, name varchar(50), changedate int);
create table nameelement(parentkey int, parentelt varchar(50), seqnum int);
create table categorybag(cbparentkey int, cbparentelt varchar(50), 
	krtModelKey varchar(50), keyvalue varchar(50));
select businesskey, name, changedate 
from business as biz left outer join nameelement as nameElt 
	on (businesskey = parentkey and parentelt = 'businessEntity') 
where (nameElt.seqnum = 1) 
	and businesskey in 
		 (select cbparentkey 
			from categorybag 
			where (cbparentelt = 'businessEntity') and 
				(krtModelKey = 'UUID:CD153257-086A-4237-B336-6BDCBDCC6634' and keyvalue = '40.00.00.00.00'))  order by name asc , biz.changedate asc;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- clean up
drop table outer1;
drop table outer2;
drop table noidx;
drop table idx1;
drop table idx2;
drop table nonunique_idx1;
drop table business;
drop table nameelement;
drop table categorybag;


-- --------------------------------------------------------------------
-- TEST CASES for different kinds of subquery flattening, Beetle 5173
-- --------------------------------------------------------------------

drop table colls;
drop table docs;

CREATE TABLE "APP"."COLLS" ("ID" VARCHAR(128) NOT NULL, "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");

CREATE TABLE "APP"."DOCS" ("ID" VARCHAR(128) NOT NULL);
CREATE INDEX "APP"."NEW_INDEX1" ON "APP"."DOCS" ("ID");
ALTER TABLE "APP"."DOCS" ADD CONSTRAINT "NEW_KEY1" PRIMARY KEY ("ID");

insert into colls values ('123', 2);
insert into colls values ('124', -5);
insert into colls values ('24', 1);
insert into colls values ('26', -2);