lojreorder.sql

derby database source code.good for you.

-- This test LOJ reordering.
-- Many testcases are derived from DB2 UDB FVT testcases.
-- Cliff Leung. 10/2002

CREATE TABLE T (A INT NOT NULL, B DECIMAL(10,3) NOT NULL, C VARCHAR(5) NOT NULL);
INSERT INTO T VALUES (1, 1.0, '1'), (2, 2.0, '2'), (3, 3.0, '3');

CREATE TABLE S (D INT NOT NULL, E DECIMAL(10,3) NOT NULL, F VARCHAR(5) NOT NULL);
INSERT INTO S VALUES (2, 2.0, '2'), (3, 3.0, '3'), (4, 4.0, '4');

CREATE TABLE R (G INT NOT NULL, H DECIMAL(10,3) NOT NULL, I VARCHAR(5) NOT NULL);
INSERT INTO R VALUES (3, 3.0, '3'), (4, 4.0, '4'), (5, 5.0, '5');

CREATE TABLE TT (J INT NOT NULL, K DECIMAL(10,3) NOT NULL, L VARCHAR(5) NOT NULL);
INSERT INTO TT VALUES (2, 2.0, '2'), (3, 3.0, '3'), (4, 4.0, '4');

select * from t left outer join s on (b = e);

select a, e, f, a+e as "A+E" from t left outer join s on (b = e);

select * from t right outer join s on (b = e);

select a, e, f, a+e as "A+E" from t right outer join s on (b = e);

select a, e, f, a+e as "A+E" from t left outer join s on (b = e) where d > 0;

select a, e, f, a+e as "A+E" from t right outer join s on (b = e) where d > 0;

-- LOJ and LOJ
select a, e, f, a+e as "A+E", g from (t left outer join s on (b = e)) left outer join r on (f = i);
select a, e, f, a+e as "A+E", g from (t left outer join s on (b = e)) left outer join r on (f = i) where a>1;
select a, d, e, a+e as "A+E", g from (t left outer join s on (b = e)) left outer join r on (f = i) where d>1;
select a, d, e, a+e as "A+E", g from (t left outer join s on (b = e)) left outer join r on (f = i) where h>1;

-- LOJ and ROJ
select a, e, f, a+e as "A+E", g from (t left outer join s on (b = e)) right outer join r on (f = i);
select a, e, f, a+e as "A+E", g from (t left outer join s on (b = e)) right outer join r on (f = i) where a>1;
select a, e, f, a+e as "A+E", g from (t left outer join s on (b = e)) right outer join r on (f = i) where d>1;
select a, e, f, a+e as "A+E", g from (t left outer join s on (b = e)) right outer join r on (f = i) where h>1;

-- ROJ and ROJ
select a, e, f, a+e as "A+E", g from (t right outer join s on (b = e)) right outer join r on (f = i);
select a, e, f, a+e as "A+E", g from (t right outer join s on (b = e)) right outer join r on (f = i) where a>1;
select a, e, f, a+e as "A+E", g from (t right outer join s on (b = e)) right outer join r on (f = i) where d>1;
select a, e, f, a+e as "A+E", g from (t right outer join s on (b = e)) right outer join r on (f = i) where h>1;

-- ROJ and LOJ
select a, e, f, a+e as "A+E", g from (t right outer join s on (b = e)) left outer join r on (f = i);
select a, e, f, a+e as "A+E", g from (t right outer join s on (b = e)) left outer join r on (f = i) where a>1;
select a, e, f, a+e as "A+E", g from (t right outer join s on (b = e)) left outer join r on (f = i) where d>1;
select a, e, f, a+e as "A+E", g from (t right outer join s on (b = e)) left outer join r on (f = i) where h>1;

-- NO LOJ reordering
select * from (t left outer join s on (b = e)) left outer join r on (f = i) where a > 0;
select * from (t left outer join s on (b = e)) inner join r on (f = i);
select * from (t left outer join s on (b = e)) inner join r on (f = i) where a > 0;
select * from (t inner join s on (b = e)) inner join r on (f = i) where a > 0;
select * from (t inner join s on (b = e)) left outer join r on (f = i) where a > 0;
select * from t, s left outer join r on (d = g) where a = e;
select * from t left outer join s on (b = e), r where a = g;

-- bug 5658
-- In DB2 UDB, any column referenced in an expression of the 
-- join-condition must be a column of one of the operand tables of 
-- the associated join (in the scope of the same joined-table clause).
-- this query should fail
select t1.*, s2.* from t t1 left outer join s on (b = e), t t2 left outer join s s2 on (b = e);

-- a view of a regular join
create view jv (fv, ev, dv, cv, bv, av) as (select f, e, d, c, b, a from t, s where b = e);
select * from jv left outer join r on (fv = i);
select * from r left outer join jv on (fv = i);

-- a view of LOJ
create view lojv (fv, ev, dv, cv, bv, av) as (select f, e, d, c, b, a from t left outer join s on b = e);
select * from r left outer join lojv on (fv = i);
select * from r right outer join lojv on (fv = i);

-- a view on top of a LOJ view
create view vv as (select * from lojv);
select * from r left outer join vv on (fv = i);
select * from r right outer join vv on (fv = i);

-- check for ON predicate for LOJ reordering: no reordering
select * from t left outer join (s left outer join r on (f = i)) on (b = e and a > b);
select * from t left outer join (s left outer join r on (f = i)) on (b = e and a = b);
select * from t left outer join (s left outer join r on (f = i)) on (b = e and 1 = 1);
select * from t left outer join (s left outer join r on (f = i)) on (b > e);
select * from t left outer join (s left outer join r on (f = i)) on (a = a);
select * from t left outer join (s left outer join r on (f = i)) on (a = 1);
select * from t left outer join (s left outer join r on (f = i)) on (1 = 1);

-- check for ON predicate for LOJ reordering: reordering is done
select * from t left outer join (s left outer join r on (f = i)) on (a = d and b = e and c = f);
select * from t left outer join (s left outer join r on (f = i)) on ((a = d and b = e) and c = f);
select * from t left outer join (s left outer join r on (f = i)) on (a = d and (b = e and c = f));
select * from t left outer join (s left outer join r on (f = i)) on (not(a <> d or b <> e or c <> f));

-- check for LOJ reorder with subquery
select * from t left outer join (s left outer join r on (f = i)) on (a = d) where a in (select j from tt);

-- LOJ involving the same table
select * from t t1 left outer join (t t2 left outer join t t3 on (t2.a=t3.a)) on (t1.a=t2.a);
select * from t t1 left outer join (t t2 left outer join t t3 on (t2.a=t3.a)) on (t1.b=t2.b);
select * from t t1 left outer join (t t2 left outer join t t3 on (t2.a=t3.b)) on (t1.a=t2.b);

-- comparing columns of different types.
select t.a, s.d, r.g from t left outer join (s left outer join r on (e=g)) on (b=d);
select r.g from t left outer join (s left outer join r on (e=g)) on (b=d);

-- these seem to work
select * from t left outer join (s left outer join r on (e=g)) on (b=d);
select t.a from t left outer join (s left outer join r on (e=g)) on (b=d);
select s.f, s.e, s.d, t.c, t.b, t.a from t left outer join (s left outer join r on (e=g)) on (b=d);

-- need to test more on changes deep inside...
create view loj (a, b, c, d, e, f, g, h, i, ae) as (select a, b, c, d, e, f, g, h, i, a+e as "A+E" from t left outer join (s left outer join r on (f = i)) on (b = e)); 

select * from loj;

select * from t left outer join loj on (t.a=loj.a);

select * from t left outer join loj on (t.a=loj.d);


select * from t right outer join loj on (t.a=loj.a);
select * from t inner join loj on (t.a=loj.a);

-- 4 level deep
-- b=e will be pushed first, one level down
-- j=a will then be pushed next, 2 levels down (after b=e pushed)
select * from tt left outer join (t left outer join (s left outer join r on (f = i)) on (b = e)) on (j=a);

select * from tt left outer join (t left outer join (s left outer join r on (f = i)) on (b = e)) on (j=a) where j>0;
select * from tt left outer join (t left outer join (s left outer join r on (f = i)) on (b = e)) on (j=a) where j>0;

select * from tt left outer join (t left outer join (s left outer join r on (f = i)) on (b = e)) on (j=d);

-- TT LOJ (T LOJ (S LOJ R))) becomes TT LOJ ((T LOJ S) LOJ R)
select 1 from tt left outer join (t left outer join (s left outer join r on (f = i)) on (b = e)) on (j=r.g);
select 1 from tt right outer join (t left outer join (s left outer join r on (f = i)) on (b = e)) on (j=g);

-- okay
select 1 from tt, (t left outer join (s left outer join r on (f = i)) on (b = e)) where (j=g);
select 1 from tt right outer join (t left outer join (s inner join r on (f = i)) on (b = e)) on (j=g);
select 1 from tt inner join (t left outer join (s left outer join r on (f = i)) on (b = e)) on (j=g);

-- LOJ w/ left-deep LOJ: LOJ will be pushed twice
select * from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=a);
select * from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=a) where j > 0; 
select * from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=a) where b > 0; 
select * from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=a) where e > 0; 
select * from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=a) where g > 0; 

select j+1, k+2, L||'s', a+10, b+10, C||'t', d+20, e+20, f||'u', g+30, h+30, i||'v' from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=a);

select j, j+1, k, k+2, L, L||'s' from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=a);
select a, a+10, b, b+10, C, C||'t' from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=a);
select d, d+20, e, e+20, f, f||'u' from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=a);
select g, g+30, h, h+30, i, i||'v' from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=a);

select * from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=d);
select * from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=d) where j > 0;
select * from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=d) where b > 0;
select * from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=d) where e > 0;
select * from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=d) where g > 0;

select * from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=g);
select * from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=g) where j > 0;
select * from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=g) where b > 0;
select * from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=g) where e > 0;
select * from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=g) where g > 0;


--
-- Create views of union, regular joins, subquery, group by, having clause,
-- and use the view in the LOJ operands and yet the LOJ reordering is applicable.
-- We need to test the join condition: LHS=RHS.

create view v1 (fv, ev, dv, cv, bv, av) as (select f, e, d, c, b, a from t left outer join s on b = e);
create view v2 (fv, ev, dv, cv, bv, av) as (select f, e, d, c, b, a from t inner join s on b = e);
create view v3 (fv, ev, dv, cv, bv, av) as (select f, e, d, c, b, a from t right join s on b = e);
create view v4 (fv, ev, dv, cv, bv, av) as (select f, e, d, c, b, a from t, s where b = e);
create view v5 (cv, bv, cnt) as (select c, b, count(*) from t group by c, b);
create view v6 (cv, bv, cnt) as (select c, b, count(*) from t group by c, b having b > 0);
create view v7 (cv, bv, av) as (select c, b, a from t where b in (select e from s));
create view v8 (cv, bv, av) as (select c, b, a from t union select f, e, d from s);

-- All these test cases need table references to determine if the join condition is proper for LOJ
-- reordering: LHS = RHS
select * from v1 left outer join (s left outer join r on (f = i)) on (d=v1.av);
select * from v2 left outer join (s left outer join r on (f = i)) on (d=v2.av);
select * from v3 left outer join (s left outer join r on (f = i)) on (d=v3.av);
select * from v4 left outer join (s left outer join r on (f = i)) on (d=v4.av);
select * from v5 left outer join (s left outer join r on (f = i)) on (e=v5.bv);
select * from v6 left outer join (s left outer join r on (f = i)) on (e=v6.bv);

select * from v7 left outer join (s left outer join r on (f = i)) on (e=v7.bv);
select * from v8 left outer join (s left outer join r on (f = i)) on (e=v8.bv);

select * from t left outer join (s left outer join v1 on (f = cv)) on (d=a);
select * from t left outer join (s left outer join v8 on (f = cv)) on (d=a);

select * from t left outer join (v1 left outer join s on (f = cv)) on (av=a);
select * from t left outer join (v8 left outer join s on (f = cv)) on (av=a);

-- No LOJ reordering due to join conditions
select * from v1 left outer join (s left outer join r on (f = i)) on (g=v1.av);
select * from v2 left outer join (s left outer join r on (f = i)) on (g=v2.av);

select * from t left outer join (s left outer join v1 on (f = cv)) on (av=a);
select * from t left outer join (s left outer join v8 on (f = cv)) on (av=a);

select * from t left outer join (v1 left outer join s on (f = cv)) on (d=a);
select * from t left outer join (v8 left outer join s on (f = cv)) on (d=a);

-- values clause
-- select * from table (values (1, 2, 3)) as q(a, b, c);

drop view v1;
drop view v2;
drop view v3;
drop view v4;
drop view v5;
drop view v6;
drop view v7;
drop view v8;
drop view loj;
drop view vv;
drop view lojv;
drop view jv;

DROP TABLE T;
DROP TABLE S;
DROP TABLE R;
DROP TABLE TT;


-- from the customer

CREATE TABLE TNL1 (ID INTEGER NOT NULL UNIQUE, col_char CHAR(20), col_decimal DECIMAL(12,5));
insert into TNL1 values (1, 'abc', 1.1);
insert into TNL1 values (2, 'bcd', 2.2);
insert into TNL1 values (3, 'cde', 3.3);
insert into TNL1 values (4, 'ABC', 1.1);
insert into TNL1 values (5, 'BCD', 2.2);
insert into TNL1 values (6, 'CDE', 3.3);

CREATE TABLE TNL1_1 (ID INTEGER NOT NULL UNIQUE, col_char CHAR(20), col_decimal DECIMAL(12,5), ID2 INTEGER);
CREATE INDEX I_TNL11 ON TNL1_1 (ID2 ASC);
insert into TNL1_1 values (3, 'cde', 3.3, 30);
insert into TNL1_1 values (4, 'xyz', 4.4, 40);
insert into TNL1_1 values (5, 'lmn', 5.5, 50);
insert into TNL1_1 values (6, 'CDE', 3.3, 30);
insert into TNL1_1 values (7, 'XYZ', 4.4, 40);
insert into TNL1_1 values (8, 'LMN', 5.5, 50);

CREATE TABLE TNL1_1_1 (ID INTEGER NOT NULL UNIQUE, col_char CHAR(20), col_decimal DECIMAL(12,5));
insert into TNL1_1_1 values (2, 'bcd', 2.2);
insert into TNL1_1_1 values (3, 'cde', 3.3);
insert into TNL1_1_1 values (4, 'xyz', 4.4);
insert into TNL1_1_1 values (5, 'BCD', 2.2);
insert into TNL1_1_1 values (6, 'CDE', 3.3);
insert into TNL1_1_1 values (7, 'XYZ', 4.4);

CREATE TABLE TNL1_2 (ID INTEGER NOT NULL UNIQUE, col_char CHAR(20), col_decimal DECIMAL(12,5));
insert into TNL1_2 values (4, 'xyz', 4.4);
insert into TNL1_2 values (5, 'lmn', 5.5);
insert into TNL1_2 values (6, 'stu', 6.6);
insert into TNL1_2 values (7, 'XYZ', 4.4);
insert into TNL1_2 values (8, 'LMN', 5.5);
insert into TNL1_2 values (9, 'STU', 6.6);