joins.out

derby database source code.good for you.

	select t2_c1, t2_c2, t1_c1, t1_c2 from t1, t2;
4 rows inserted/updated/deleted
ij> select * from instab;
INSTAB_C1  |INSTAB_C2 |INSTAB_C3  |INSTAB_C4 
---------------------------------------------
1          |t2-row1   |1          |t1-row1   
2          |t2-row2   |1          |t1-row1   
1          |t2-row1   |2          |t1-row2   
2          |t2-row2   |2          |t1-row2   
ij> delete from instab;
4 rows inserted/updated/deleted
ij> insert into instab (instab_c3, instab_c1, instab_c2, instab_c4)
	select t2_c1, t1_c1, t1_c2, t2_c2 from t1, t2;
4 rows inserted/updated/deleted
ij> select * from instab;
INSTAB_C1  |INSTAB_C2 |INSTAB_C3  |INSTAB_C4 
---------------------------------------------
1          |t1-row1   |1          |t2-row1   
1          |t1-row1   |2          |t2-row2   
2          |t1-row2   |1          |t2-row1   
2          |t1-row2   |2          |t2-row2   
ij> delete from instab;
4 rows inserted/updated/deleted
ij> -- projection
insert into instab (instab_c1, instab_c3)
	select t1_c1, t2_c1 from t1, t2;
4 rows inserted/updated/deleted
ij> select * from instab;
INSTAB_C1  |INSTAB_C2 |INSTAB_C3  |INSTAB_C4 
---------------------------------------------
1          |NULL      |1          |NULL      
1          |NULL      |2          |NULL      
2          |NULL      |1          |NULL      
2          |NULL      |2          |NULL      
ij> delete from instab;
4 rows inserted/updated/deleted
ij> -- project out 1 or more tables from join
insert into instab select 1, '2', 3, '4' from t1, t2;
4 rows inserted/updated/deleted
ij> select * from instab;
INSTAB_C1  |INSTAB_C2 |INSTAB_C3  |INSTAB_C4 
---------------------------------------------
1          |2         |3          |4         
1          |2         |3          |4         
1          |2         |3          |4         
1          |2         |3          |4         
ij> delete from instab;
4 rows inserted/updated/deleted
ij> insert into instab select 1, t1.t1_c2, 3, t1.t1_c2 from t1, t2;
4 rows inserted/updated/deleted
ij> select * from instab;
INSTAB_C1  |INSTAB_C2 |INSTAB_C3  |INSTAB_C4 
---------------------------------------------
1          |t1-row1   |3          |t1-row1   
1          |t1-row1   |3          |t1-row1   
1          |t1-row2   |3          |t1-row2   
1          |t1-row2   |3          |t1-row2   
ij> delete from instab;
4 rows inserted/updated/deleted
ij> insert into instab select t2.t2_c1, '2', t2.t2_c1, '4' from t1, t2;
4 rows inserted/updated/deleted
ij> select * from instab;
INSTAB_C1  |INSTAB_C2 |INSTAB_C3  |INSTAB_C4 
---------------------------------------------
1          |2         |1          |4         
2          |2         |2          |4         
1          |2         |1          |4         
2          |2         |2          |4         
ij> delete from instab;
4 rows inserted/updated/deleted
ij> ------------------------------------------
-- test optimizations where we push around
-- predicates (remapColumnReferences)
------------------------------------------
-- case
select t1_c1 from t1, t2 where (case when t1_c1 = 1 then t2_c2 end) = t2_c2;
T1_C1      
-----------
1          
1          
ij> -- CHAR built-in function
select t1_c1 from t1, t2 where CHAR(t1_c1) = t2_c2;
T1_C1      
-----------
ij> -- logical operator OR
select t1_c1 from t1, t2 where t1_c1 = 1 or t2_c1 = 2;
T1_C1      
-----------
1          
1          
2          
ij> -- logical operator AND
select t1_c1 from t1, t2 where t1_c1 = 2147483647 and 2147483647 = t2_c1;
T1_C1      
-----------
ij> -- beetle 5421
-- INT built-in function
select t1_c1 from t1, t2 where INT(t1_c1) = t2_c1;
ERROR 42X01: Syntax error: Encountered "INT" at line 3, column 32.
ij> select t1_c1 from t1, t2 where t1_c1 = INT(2147483647) and INT(2147483647) = t2_c1;
ERROR 42X01: Syntax error: Encountered "INT" at line 1, column 40.
ij> -- transitive closure - verify join condition doesn't get dropped
create table x(c1 int);
0 rows inserted/updated/deleted
ij> create table y(c1 int);
0 rows inserted/updated/deleted
ij> insert into x values 1, 2, null;
3 rows inserted/updated/deleted
ij> insert into y values 1, 2, null;
3 rows inserted/updated/deleted
ij> select * from x,y where x.c1 = y.c1 and x.c1 = 1 and y.c1 = 2;
C1         |C1         
-----------------------
ij> select * from x,y where x.c1 = y.c1 and x.c1 is null;
C1         |C1         
-----------------------
ij> select * from x,y where x.c1 = y.c1 and x.c1 is null and y.c1 = 2;
C1         |C1         
-----------------------
ij> select * from x,y where x.c1 = y.c1 and x.c1 is null and y.c1 is null;
C1         |C1         
-----------------------
ij> -- Beetle task 5000. Bug found by Websphere. Should not return any rows.
select t1_c1, t1_c2, t2_c1, t2_c2
  from t1, t2
  where t1_c1 = t2_c1
    and t1_c1 = 1
    and t2_c1 <> 1;
T1_C1      |T1_C2     |T2_C1      |T2_C2     
---------------------------------------------
ij> -- Beetle task 4736
create table a (a1 int not null primary key, a2 int, a3 int, a4 int, a5 int, a6 int);
0 rows inserted/updated/deleted
ij> create table b (b1 int not null primary key, b2 int, b3 int, b4 int, b5 int, b6 int);
0 rows inserted/updated/deleted
ij> create table c (c1 int not null, c2 int, c3 int not null, c4 int, c5 int, c6 int);
0 rows inserted/updated/deleted
ij> create table d (d1 int not null, d2 int, d3 int not null, d4 int, d5 int, d6 int);
0 rows inserted/updated/deleted
ij> alter table c add primary key (c1,c3);
0 rows inserted/updated/deleted
ij> alter table d add primary key (d1,d3);
0 rows inserted/updated/deleted
ij> insert into a values (1,1,3,6,NULL,2),(2,3,2,4,2,2),(3,4,2,NULL,NULL,NULL),
                     (4,NULL,4,2,5,2),(5,2,3,5,7,4),(7,1,4,2,3,4),
                     (8,8,8,8,8,8),(6,7,3,2,3,4);
8 rows inserted/updated/deleted
ij> insert into b values (6,7,2,3,NULL,1),(4,5,9,6,3,2),(1,4,2,NULL,NULL,NULL),
                     (5,NULL,2,2,5,2),(3,2,3,3,1,4),(7,3,3,3,3,3),(9,3,3,3,3,3);
7 rows inserted/updated/deleted
ij> insert into c values (3,7,7,3,NULL,1),(8,3,9,1,3,2),(1,4,1,NULL,NULL,NULL),
                     (3,NULL,1,2,4,2),(2,2,5,3,2,4),(1,7,2,3,1,1),(3,8,4,2,4,6);
7 rows inserted/updated/deleted
ij> insert into d values (1,7,2,3,NULL,3),(2,3,9,1,1,2),(2,2,2,NULL,3,2),
                     (1,NULL,3,2,2,1),(2,2,5,3,2,3),(2,5,6,3,7,2);
6 rows inserted/updated/deleted
ij> select a1,b1,c1,c3,d1,d3 
  from D join (A left outer join (B join C on b2=c2) on a1=b1) 
    on d3=b3 and d1=a2;
A1         |B1         |C1         |C3         |D1         |D3         
-----------------------------------------------------------------------
1          |1          |1          |1          |1          |2          
7          |7          |8          |9          |1          |3          
ij> select a1,b1,c1,c3,d1,d3 
  from D join ((B join C on b2=c2) right outer join A on a1=b1) 
    on d3=b3 and d1=a2;
A1         |B1         |C1         |C3         |D1         |D3         
-----------------------------------------------------------------------
1          |1          |1          |1          |1          |2          
7          |7          |8          |9          |1          |3          
ij> -----------------------------------
-- clean up
----------------------------------
drop table a;
0 rows inserted/updated/deleted
ij> drop table b;
0 rows inserted/updated/deleted
ij> drop table c;
0 rows inserted/updated/deleted
ij> drop table d;
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> drop table t3;
0 rows inserted/updated/deleted
ij> drop table t4;
0 rows inserted/updated/deleted
ij> drop table instab;
0 rows inserted/updated/deleted
ij> drop table x;
0 rows inserted/updated/deleted
ij> drop table y;
0 rows inserted/updated/deleted
ij>