lojreorder.out

来自「derby database source code.good for you.」· OUT 代码 · 共 790 行 · 第 1/5 页

-----------------------------------------------------------------------------------------------
1          |1.000        |1    |1          |1.000        |1    |1          |1.000        |1    
2          |2.000        |2    |2          |2.000        |2    |2          |2.000        |2    
3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    
ij> select * from t t1 left outer join (t t2 left outer join t t3 on (t2.a=t3.a)) on (t1.b=t2.b);
A          |B            |C    |A          |B            |C    |A          |B            |C    
-----------------------------------------------------------------------------------------------
1          |1.000        |1    |1          |1.000        |1    |1          |1.000        |1    
2          |2.000        |2    |2          |2.000        |2    |2          |2.000        |2    
3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    
ij> select * from t t1 left outer join (t t2 left outer join t t3 on (t2.a=t3.b)) on (t1.a=t2.b);
A          |B            |C    |A          |B            |C    |A          |B            |C    
-----------------------------------------------------------------------------------------------
1          |1.000        |1    |1          |1.000        |1    |1          |1.000        |1    
2          |2.000        |2    |2          |2.000        |2    |2          |2.000        |2    
3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    
ij> -- 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);
A          |D          |G          
-----------------------------------
1          |NULL       |NULL       
2          |2          |NULL       
3          |3          |3          
ij> select r.g from t left outer join (s left outer join r on (e=g)) on (b=d);
G          
-----------
NULL       
NULL       
3          
ij> -- these seem to work
select * from t left outer join (s left outer join r on (e=g)) on (b=d);
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 t.a from t left outer join (s left outer join r on (e=g)) on (b=d);
A          
-----------
1          
2          
3          
ij> 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);
F    |E            |D          |C    |B            |A          
---------------------------------------------------------------
NULL |NULL         |NULL       |1    |1.000        |1          
2    |2.000        |2          |2    |2.000        |2          
3    |3.000        |3          |3    |3.000        |3          
ij> -- 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));
0 rows inserted/updated/deleted
ij> select * from loj;
A          |B            |C    |D          |E            |F    |G          |H            |I    |AE               
-----------------------------------------------------------------------------------------------------------------
1          |1.000        |1    |NULL       |NULL         |NULL |NULL       |NULL         |NULL |NULL             
2          |2.000        |2    |2          |2.000        |2    |NULL       |NULL         |NULL |4.000            
3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    |6.000            
ij> select * from t left outer join loj on (t.a=loj.a);
A          |B            |C    |A          |B            |C    |D          |E            |F    |G          |H            |I    |AE               
-------------------------------------------------------------------------------------------------------------------------------------------------
1          |1.000        |1    |1          |1.000        |1    |NULL       |NULL         |NULL |NULL       |NULL         |NULL |NULL             
2          |2.000        |2    |2          |2.000        |2    |2          |2.000        |2    |NULL       |NULL         |NULL |4.000            
3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    |6.000            
ij> select * from t left outer join loj on (t.a=loj.d);
A          |B            |C    |A          |B            |C    |D          |E            |F    |G          |H            |I    |AE               
-------------------------------------------------------------------------------------------------------------------------------------------------
1          |1.000        |1    |NULL       |NULL         |NULL |NULL       |NULL         |NULL |NULL       |NULL         |NULL |NULL             
2          |2.000        |2    |2          |2.000        |2    |2          |2.000        |2    |NULL       |NULL         |NULL |4.000            
3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    |6.000            
ij> select * from t right outer join loj on (t.a=loj.a);
A          |B            |C    |A          |B            |C    |D          |E            |F    |G          |H            |I    |AE               
-------------------------------------------------------------------------------------------------------------------------------------------------
1          |1.000        |1    |1          |1.000        |1    |NULL       |NULL         |NULL |NULL       |NULL         |NULL |NULL             
2          |2.000        |2    |2          |2.000        |2    |2          |2.000        |2    |NULL       |NULL         |NULL |4.000            
3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    |6.000            
ij> select * from t inner join loj on (t.a=loj.a);
A          |B            |C    |A          |B            |C    |D          |E            |F    |G          |H            |I    |AE               
-------------------------------------------------------------------------------------------------------------------------------------------------
1          |1.000        |1    |1          |1.000        |1    |NULL       |NULL         |NULL |NULL       |NULL         |NULL |NULL             
2          |2.000        |2    |2          |2.000        |2    |2          |2.000        |2    |NULL       |NULL         |NULL |4.000            
3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    |6.000            
ij> -- 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);
J          |K            |L    |A          |B            |C    |D          |E            |F    |G          |H            |I    
-------------------------------------------------------------------------------------------------------------------------------
2          |2.000        |2    |2          |2.000        |2    |2          |2.000        |2    |NULL       |NULL         |NULL 
3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    
4          |4.000        |4    |NULL       |NULL         |NULL |NULL       |NULL         |NULL |NULL       |NULL         |NULL 
ij> 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;
J          |K            |L    |A          |B            |C    |D          |E            |F    |G          |H            |I    
-------------------------------------------------------------------------------------------------------------------------------
2          |2.000        |2    |2          |2.000        |2    |2          |2.000        |2    |NULL       |NULL         |NULL 
3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    
4          |4.000        |4    |NULL       |NULL         |NULL |NULL       |NULL         |NULL |NULL       |NULL         |NULL 
ij> 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;
J          |K            |L    |A          |B            |C    |D          |E            |F    |G          |H            |I    
-------------------------------------------------------------------------------------------------------------------------------
2          |2.000        |2    |2          |2.000        |2    |2          |2.000        |2    |NULL       |NULL         |NULL 
3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    
4          |4.000        |4    |NULL       |NULL         |NULL |NULL       |NULL         |NULL |NULL       |NULL         |NULL 
ij> select * from tt left outer join (t left outer join (s left outer join r on (f = i)) on (b = e)) on (j=d);
J          |K            |L    |A          |B            |C    |D          |E            |F    |G          |H            |I    
-------------------------------------------------------------------------------------------------------------------------------
2          |2.000        |2    |2          |2.000        |2    |2          |2.000        |2    |NULL       |NULL         |NULL 
3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    
4          |4.000        |4    |NULL       |NULL         |NULL |NULL       |NULL         |NULL |NULL       |NULL         |NULL 
ij> -- 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);
1          
-----------
1          
1          
1          
ij> 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);
1          
-----------
1          
1          
1          
ij> -- okay
select 1 from tt, (t left outer join (s left outer join r on (f = i)) on (b = e)) where (j=g);
1          
-----------
1          
ij> select 1 from tt right outer join (t left outer join (s inner join r on (f = i)) on (b = e)) on (j=g);
1          
-----------
1          
1          
1          
ij> select 1 from tt inner join (t left outer join (s left outer join r on (f = i)) on (b = e)) on (j=g);
1          
-----------
1          
ij> -- 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);
J          |K            |L    |A          |B            |C    |D          |E            |F    |G          |H            |I    
-------------------------------------------------------------------------------------------------------------------------------
2          |2.000        |2    |2          |2.000        |2    |2          |2.000        |2    |NULL       |NULL         |NULL 
3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    
4          |4.000        |4    |NULL       |NULL         |NULL |NULL       |NULL         |NULL |NULL       |NULL         |NULL 
ij> 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;
J          |K            |L    |A          |B            |C    |D          |E            |F    |G          |H            |I    
-------------------------------------------------------------------------------------------------------------------------------
2          |2.000        |2    |2          |2.000        |2    |2          |2.000        |2    |NULL       |NULL         |NULL 
3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    
4          |4.000        |4    |NULL       |NULL         |NULL |NULL       |NULL         |NULL |NULL       |NULL         |NULL 
ij> 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;
J          |K            |L    |A          |B            |C    |D          |E            |F    |G          |H            |I    
-------------------------------------------------------------------------------------------------------------------------------
2          |2.000        |2    |2          |2.000        |2    |2          |2.000        |2    |NULL       |NULL         |NULL 
3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    |3          |3.000        |3    
ij> 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;
J          |K            |L    |A          |B            |C    |D          |E            |F    |G          |H            |I    
-------------------------------------------------------------------------------------------------------------------------------
2          |2.000        |2    |2          |2.000        |2    |2          |2.000        |2    |NULL       |NULL         |NULL