derived.out

derby database source code.good for you.

ij> --
-- this tests derived column lists and derived tables
--
create table s (a int, b int, c int, d int, e int, f int);
0 rows inserted/updated/deleted
ij> create table t (aa int, bb int, cc int, dd int, ee int, ff int);
0 rows inserted/updated/deleted
ij> insert into s values (0,1,2,3,4,5);
1 row inserted/updated/deleted
ij> insert into s values (10,11,12,13,14,15);
1 row inserted/updated/deleted
ij> -- tests without a derived table
-- negative tests
-- # of columns does not match
select aa from s ss (aa);
ERROR 42X32: The number of columns in the derived column list must match the number of columns in table 'S'.
ij> select aa from s ss (aa, bb, cc, dd, ee, ff, gg);
ERROR 42X32: The number of columns in the derived column list must match the number of columns in table 'S'.
ij> -- duplicate names in derived column list
select aa from s ss (aa, ee, bb, cc, dd, aa);
ERROR 42X33: The derived column list contains a duplicate column name 'AA'.
ij> -- test case insensitivity
select aa from s ss (aa, bb, cc, dd, ee, AA);
ERROR 42X33: The derived column list contains a duplicate column name 'AA'.
ij> -- test uniqueness of names
select aa from s ss (aa, bb, cc, dd, ee, ff), t;
ERROR 42X03: Column name 'AA' is in more than one table in the FROM list.
ij> -- test uniqueness of names
insert into t select aa 
from s aa (aa, bb, cc, dd, ee, ff), s bb (aa, bb, cc, dd, ee, ff);
ERROR 42X03: Column name 'AA' is in more than one table in the FROM list.
ij> -- verify using "exposed" names
select a from s ss (aa, bb, cc, dd, ee, ff);
ERROR 42X04: Column 'A' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'A' is not a column in the target table.
ij> -- positive tests
-- rename the columns
select * from s ss (f, e, d, c, b, a) where f = 0;
F          |E          |D          |C          |B          |A          
-----------------------------------------------------------------------
0          |1          |2          |3          |4          |5          
ij> -- delimited identifiers in list
select * from s ss ("a a", "b b", "c c", "d d", "e e", "f f") where "a a" = 0;
a a        |b b        |c c        |d d        |e e        |f f        
-----------------------------------------------------------------------
0          |1          |2          |3          |4          |5          
ij> -- uniqueness of "exposed" names
select a, aa from s a, s b (aa, bb, cc, dd, ee, ff)	where f = ff and aa = 10;
A          |AA         
-----------------------
10         |10         
ij> select a.a, b.aa from s a, s b (aa, bb, cc, dd, ee, ff) where f = ff and b.aa = 10;
A          |AA         
-----------------------
10         |10         
ij> -- insert tests
insert into t select * from s ss (aa, bb, cc, dd, ee, ff);
2 rows inserted/updated/deleted
ij> select * from t;
AA         |BB         |CC         |DD         |EE         |FF         
-----------------------------------------------------------------------
0          |1          |2          |3          |4          |5          
10         |11         |12         |13         |14         |15         
ij> delete from t;
2 rows inserted/updated/deleted
ij> insert into t (aa,bb) select ff, aa from s ss (aa, bb, cc, dd, ee, ff);
2 rows inserted/updated/deleted
ij> select * from t;
AA         |BB         |CC         |DD         |EE         |FF         
-----------------------------------------------------------------------
5          |0          |NULL       |NULL       |NULL       |NULL       
15         |10         |NULL       |NULL       |NULL       |NULL       
ij> delete from t;
2 rows inserted/updated/deleted
ij> -- derived tables
-- negative tests
-- no correlation name
select * from (select * from s);
ERROR 42X01: Syntax error: Encountered "<EOF>" at line 5, column 31.
ij> -- # of columns does not match
select aa from (select * from s) ss (aa);
ERROR 42X32: The number of columns in the derived column list must match the number of columns in table 'SS'.
ij> select aa from (select * from s) ss (aa, bb, cc, dd, ee, ff, gg);
ERROR 42X32: The number of columns in the derived column list must match the number of columns in table 'SS'.
ij> -- duplicate names in derived column list
select aa from (select * from s) ss (aa, ee, bb, cc, dd, aa);
ERROR 42X33: The derived column list contains a duplicate column name 'AA'.
ij> -- test case insensitivity
select aa from (select * from s) ss (aa, bb, cc, dd, ee, AA);
ERROR 42X33: The derived column list contains a duplicate column name 'AA'.
ij> -- test uniqueness of names
select aa from (select * from s) ss (aa, bb, cc, dd, ee, ff), t;
ERROR 42X03: Column name 'AA' is in more than one table in the FROM list.
ij> -- test uniqueness of names
insert into t select aa 
from (select * from s) aa (aa, bb, cc, dd, ee, ff), 
	 (select * from s) bb (aa, bb, cc, dd, ee, ff);
ERROR 42X03: Column name 'AA' is in more than one table in the FROM list.
ij> -- verify using "exposed" names
select a from (select * from s) ss (aa, bb, cc, dd, ee, ff);
ERROR 42X04: Column 'A' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'A' is not a column in the target table.
ij> -- ambiguous column reference
select a from (select * from s a, s b) ss;
ERROR 42Y34: Column name 'A' matches more than one result column in table 'SS'.
ij> -- positive tests
-- simple derived table
select a from (select a from s) a;
A          
-----------
0          
10         
ij> -- select * query's
select * from (select * from s) a;
A          |B          |C          |D          |E          |F          
-----------------------------------------------------------------------
0          |1          |2          |3          |4          |5          
10         |11         |12         |13         |14         |15         
ij> select * from (select a, b, c, d, e, f from s) a;
A          |B          |C          |D          |E          |F          
-----------------------------------------------------------------------
0          |1          |2          |3          |4          |5          
10         |11         |12         |13         |14         |15         
ij> select * from (select a, b, c from s) a;
A          |B          |C          
-----------------------------------
0          |1          |2          
10         |11         |12         
ij> select a, b, c, d, e, f from (select * from s) a;
A          |B          |C          |D          |E          |F          
-----------------------------------------------------------------------
0          |1          |2          |3          |4          |5          
10         |11         |12         |13         |14         |15         
ij> -- simple derived table
insert into t (aa) select a from (select a from s) a;
2 rows inserted/updated/deleted
ij> select * from t;
AA         |BB         |CC         |DD         |EE         |FF         
-----------------------------------------------------------------------
0          |NULL       |NULL       |NULL       |NULL       |NULL       
10         |NULL       |NULL       |NULL       |NULL       |NULL       
ij> delete from t;
2 rows inserted/updated/deleted
ij> -- select * query's
insert into t select * from (select * from s) a;
2 rows inserted/updated/deleted
ij> select * from t;
AA         |BB         |CC         |DD         |EE         |FF         
-----------------------------------------------------------------------
0          |1          |2          |3          |4          |5          
10         |11         |12         |13         |14         |15         
ij> delete from t;
2 rows inserted/updated/deleted
ij> insert into t select * from (select a, b, c, d, e, f from s) a;
2 rows inserted/updated/deleted
ij> select * from t;
AA         |BB         |CC         |DD         |EE         |FF         
-----------------------------------------------------------------------
0          |1          |2          |3          |4          |5          
10         |11         |12         |13         |14         |15         
ij> delete from t;
2 rows inserted/updated/deleted
ij> insert into t (aa, bb, cc) select * from (select a, b, c from s) a;
2 rows inserted/updated/deleted
ij> select * from t;
AA         |BB         |CC         |DD         |EE         |FF         
-----------------------------------------------------------------------
0          |1          |2          |NULL       |NULL       |NULL       
10         |11         |12         |NULL       |NULL       |NULL       
ij> delete from t;
2 rows inserted/updated/deleted
ij> insert into t select a, b, c, d, e, f from (select * from s) a;
2 rows inserted/updated/deleted
ij> select * from t;
AA         |BB         |CC         |DD         |EE         |FF         
-----------------------------------------------------------------------
0          |1          |2          |3          |4          |5          
10         |11         |12         |13         |14         |15         
ij> delete from t;
2 rows inserted/updated/deleted
ij> -- simple derived table with derived column list
select a from (select a from s) a (a);
A          
-----------
0          
10         
ij> -- select * query's	with derived column lists
select * from (select * from s) a (f, e, d, c, b, a);
F          |E          |D          |C          |B          |A          
-----------------------------------------------------------------------
0          |1          |2          |3          |4          |5          
10         |11         |12         |13         |14         |15         
ij> select * from (select a, b, c, d, e, f from s) a (f, e, d, c, b, a);
F          |E          |D          |C          |B          |A          
-----------------------------------------------------------------------
0          |1          |2          |3          |4          |5          
10         |11         |12         |13         |14         |15         
ij> select * from (select a, b, c from s) a (c, f, e);
C          |F          |E          
-----------------------------------
0          |1          |2          
10         |11         |12         
ij> select a, b, c, d, e, f from (select * from s) a (a, b, c, d, e, f);
A          |B          |C          |D          |E          |F          
-----------------------------------------------------------------------
0          |1          |2          |3          |4          |5          
10         |11         |12         |13         |14         |15         
ij> -- simple derived table with derived column list
insert into t (aa) select a from (select a from s) a (a);
2 rows inserted/updated/deleted
ij> select * from t;
AA         |BB         |CC         |DD         |EE         |FF         
-----------------------------------------------------------------------
0          |NULL       |NULL       |NULL       |NULL       |NULL       
10         |NULL       |NULL       |NULL       |NULL       |NULL       
ij> delete from t;
2 rows inserted/updated/deleted
ij> -- select * query's with derived column lists
insert into t select * from (select * from s) a (c, b, a, e, f, d);
2 rows inserted/updated/deleted
ij> select * from t;
AA         |BB         |CC         |DD         |EE         |FF         
-----------------------------------------------------------------------
0          |1          |2          |3          |4          |5          
10         |11         |12         |13         |14         |15         
ij> delete from t;
2 rows inserted/updated/deleted
ij> insert into t select * from (select a, b, c, d, e, f from s) a (f, a, c, b, e, d);
2 rows inserted/updated/deleted
ij> select * from t;
AA         |BB         |CC         |DD         |EE         |FF         
-----------------------------------------------------------------------
0          |1          |2          |3          |4          |5          
10         |11         |12         |13         |14         |15         
ij> delete from t;
2 rows inserted/updated/deleted
ij> insert into t (aa, bb, cc) select * from (select a, b, c from s) a (f, e, a);
2 rows inserted/updated/deleted
ij> select * from t;