lojreorder.out

derby database source code.good for you.

ij> -- 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);
0 rows inserted/updated/deleted
ij> INSERT INTO T VALUES (1, 1.0, '1'), (2, 2.0, '2'), (3, 3.0, '3');
3 rows inserted/updated/deleted
ij> CREATE TABLE S (D INT NOT NULL, E DECIMAL(10,3) NOT NULL, F VARCHAR(5) NOT NULL);
0 rows inserted/updated/deleted
ij> INSERT INTO S VALUES (2, 2.0, '2'), (3, 3.0, '3'), (4, 4.0, '4');
3 rows inserted/updated/deleted
ij> CREATE TABLE R (G INT NOT NULL, H DECIMAL(10,3) NOT NULL, I VARCHAR(5) NOT NULL);
0 rows inserted/updated/deleted
ij> INSERT INTO R VALUES (3, 3.0, '3'), (4, 4.0, '4'), (5, 5.0, '5');
3 rows inserted/updated/deleted
ij> CREATE TABLE TT (J INT NOT NULL, K DECIMAL(10,3) NOT NULL, L VARCHAR(5) NOT NULL);
0 rows inserted/updated/deleted
ij> INSERT INTO TT VALUES (2, 2.0, '2'), (3, 3.0, '3'), (4, 4.0, '4');
3 rows inserted/updated/deleted
ij> select * from t left outer join s on (b = e);
A          |B            |C    |D          |E            |F    
---------------------------------------------------------------
1          |1.000        |1    |NULL       |NULL         |NULL 
2          |2.000        |2    |2          |2.000        |2    
3          |3.000        |3    |3          |3.000        |3    
ij> select a, e, f, a+e as "A+E" from t left outer join s on (b = e);
A          |E            |F    |A+E              
-------------------------------------------------
1          |NULL         |NULL |NULL             
2          |2.000        |2    |4.000            
3          |3.000        |3    |6.000            
ij> select * from t right outer join s on (b = e);
A          |B            |C    |D          |E            |F    
---------------------------------------------------------------
2          |2.000        |2    |2          |2.000        |2    
3          |3.000        |3    |3          |3.000        |3    
NULL       |NULL         |NULL |4          |4.000        |4    
ij> select a, e, f, a+e as "A+E" from t right outer join s on (b = e);
A          |E            |F    |A+E              
-------------------------------------------------
2          |2.000        |2    |4.000            
3          |3.000        |3    |6.000            
NULL       |4.000        |4    |NULL             
ij> select a, e, f, a+e as "A+E" from t left outer join s on (b = e) where d > 0;
A          |E            |F    |A+E              
-------------------------------------------------
2          |2.000        |2    |4.000            
3          |3.000        |3    |6.000            
ij> select a, e, f, a+e as "A+E" from t right outer join s on (b = e) where d > 0;
A          |E            |F    |A+E              
-------------------------------------------------
2          |2.000        |2    |4.000            
3          |3.000        |3    |6.000            
NULL       |4.000        |4    |NULL             
ij> -- 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);
A          |E            |F    |A+E              |G          
-------------------------------------------------------------
1          |NULL         |NULL |NULL             |NULL       
2          |2.000        |2    |4.000            |NULL       
3          |3.000        |3    |6.000            |3          
ij> 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;
A          |E            |F    |A+E              |G          
-------------------------------------------------------------
2          |2.000        |2    |4.000            |NULL       
3          |3.000        |3    |6.000            |3          
ij> 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;
A          |D          |E            |A+E              |G          
-------------------------------------------------------------------
2          |2          |2.000        |4.000            |NULL       
3          |3          |3.000        |6.000            |3          
ij> 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;
A          |D          |E            |A+E              |G          
-------------------------------------------------------------------
3          |3          |3.000        |6.000            |3          
ij> -- 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);
A          |E            |F    |A+E              |G          
-------------------------------------------------------------
3          |3.000        |3    |6.000            |3          
NULL       |NULL         |NULL |NULL             |4          
NULL       |NULL         |NULL |NULL             |5          
ij> 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;
A          |E            |F    |A+E              |G          
-------------------------------------------------------------
3          |3.000        |3    |6.000            |3          
ij> 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;
A          |E            |F    |A+E              |G          
-------------------------------------------------------------
3          |3.000        |3    |6.000            |3          
ij> 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;
A          |E            |F    |A+E              |G          
-------------------------------------------------------------
3          |3.000        |3    |6.000            |3          
NULL       |NULL         |NULL |NULL             |4          
NULL       |NULL         |NULL |NULL             |5          
ij> -- 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);
A          |E            |F    |A+E              |G          
-------------------------------------------------------------
3          |3.000        |3    |6.000            |3          
NULL       |4.000        |4    |NULL             |4          
NULL       |NULL         |NULL |NULL             |5          
ij> 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;
A          |E            |F    |A+E              |G          
-------------------------------------------------------------
3          |3.000        |3    |6.000            |3          
ij> 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;
A          |E            |F    |A+E              |G          
-------------------------------------------------------------
3          |3.000        |3    |6.000            |3          
NULL       |4.000        |4    |NULL             |4          
ij> 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;
A          |E            |F    |A+E              |G          
-------------------------------------------------------------
3          |3.000        |3    |6.000            |3          
NULL       |4.000        |4    |NULL             |4          
NULL       |NULL         |NULL |NULL             |5          
ij> -- 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);
A          |E            |F    |A+E              |G          
-------------------------------------------------------------
2          |2.000        |2    |4.000            |NULL       
3          |3.000        |3    |6.000            |3          
NULL       |4.000        |4    |NULL             |4          
ij> 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;
A          |E            |F    |A+E              |G          
-------------------------------------------------------------
2          |2.000        |2    |4.000            |NULL       
3          |3.000        |3    |6.000            |3          
ij> 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;
A          |E            |F    |A+E              |G          
-------------------------------------------------------------
2          |2.000        |2    |4.000            |NULL       
3          |3.000        |3    |6.000            |3          
NULL       |4.000        |4    |NULL             |4          
ij> 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;
A          |E            |F    |A+E              |G          
-------------------------------------------------------------
3          |3.000        |3    |6.000            |3          
NULL       |4.000        |4    |NULL             |4          
ij> -- NO LOJ reordering
select * from (t left outer join s on (b = e)) left outer join r on (f = i) where a > 0;
A          |B            |C    |D          |E            |F    |G          |H            |I    
-----------------------------------------------------------------------------------------------
1          |1.000        |1    |NULL       |NULL         |NULL |NULL       |NULL         |NULL 
2          |2.000        |2    |2          |2.000        |2    |NULL       |NULL         |NULL 
3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    
ij> select * from (t left outer join s on (b = e)) inner join r on (f = i);
A          |B            |C    |D          |E            |F    |G          |H            |I    
-----------------------------------------------------------------------------------------------
3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    
ij> select * from (t left outer join s on (b = e)) inner join r on (f = i) where a > 0;
A          |B            |C    |D          |E            |F    |G          |H            |I    
-----------------------------------------------------------------------------------------------
3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    
ij> select * from (t inner join s on (b = e)) inner join r on (f = i) where a > 0;
A          |B            |C    |D          |E            |F    |G          |H            |I