ij> --
--   Licensed to the Apache Software Foundation (ASF) under one or more
--   contributor license agreements.  See the NOTICE file distributed with
--   this work for additional information regarding copyright ownership.
--   The ASF licenses this file to You under the Apache License, Version 2.0
--   (the "License"); you may not use this file except in compliance with
--   the License.  You may obtain a copy of the License at
--
--      http://www.apache.org/licenses/LICENSE-2.0
--
--   Unless required by applicable law or agreed to in writing, software
--   distributed under the License is distributed on an "AS IS" BASIS,
--   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--   See the License for the specific language governing permissions and
--   limitations under the License.
--
--
-- 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 'APP.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 'APP.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.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
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;
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, c, "a", "b", b, "c" from (select * from s) a (a, c, "a", "b", b, "c");
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> -- project and reorder derived column list
select a, f from (select * from s) a (b, c, d, e, f, a);
A          |F          
-----------------------
5          |4          
15         |14         
ij> insert into t (aa, bb) select a, f from (select * from s) a (b, c, d, e, f, a);
2 rows inserted/updated/deleted
ij> select * from t;
AA         |BB         |CC         |DD         |EE         |FF         
-----------------------------------------------------------------------
5          |4          |NULL       |NULL       |NULL       |NULL       
15         |14         |NULL       |NULL       |NULL       |NULL       
ij> delete from t;
2 rows inserted/updated/deleted
ij> -- outer where clause references columns from derived table 
select * from (select * from s) a (a, b, c, d, e, f) where a = 0;
A          |B          |C          |D          |E          |F          
-----------------------------------------------------------------------
0          |1          |2          |3          |4          |5          
ij> select * from (select * from s) a (f, e, d, c, b, a) where f = 0;
F          |E          |D          |C          |B          |A          
-----------------------------------------------------------------------
0          |1          |2          |3          |4          |5          
ij> insert into t select * from (select * from s) a (a, b, c, d, e, f) where a = 0;
1 row inserted/updated/deleted
ij> select * from t;
AA         |BB         |CC         |DD         |EE         |FF         
-----------------------------------------------------------------------
0          |1          |2          |3          |4          |5          
ij> delete from t;
1 row inserted/updated/deleted
ij> insert into t select * from (select * from s) a (f, e, d, c, b, a) where f = 0;
1 row inserted/updated/deleted
ij> select * from t;
AA         |BB         |CC         |DD         |EE         |FF         
-----------------------------------------------------------------------
0          |1          |2          |3          |4          |5          
ij> delete from t;
1 row inserted/updated/deleted
ij> -- join between 2 derived tables
select * from (select a from s) a, (select a from s) b;
A          |A          
-----------------------
0          |0          
0          |10         
10         |0          
10         |10         
ij> select * from (select a from s) a, (select a from s) b where a.a = b.a;
A          |A          
-----------------------
0          |0          
10         |10         
ij> insert into t (aa, bb) select * from (select a from s) a, (select a from s) b where a.a = b.a;
2 rows inserted/updated/deleted
ij> select * from t;
AA         |BB         |CC         |DD         |EE         |FF         
-----------------------------------------------------------------------
0          |0          |NULL       |NULL       |NULL       |NULL       
10         |10         |NULL       |NULL       |NULL       |NULL       
ij> delete from t;
2 rows inserted/updated/deleted
ij> -- join within a derived table
select * from (select a.a, b.a from s a, s b) a (b, a) where b = a;
B          |A          
-----------------------
0          |0          
10         |10         
ij> select * from (select a.a, b.a from s a, s b) a (b, a),
			  (select a.a, b.a from s a, s b) b (b, a) where a.b = b.b;
B          |A          |B          |A          
-----------------------------------------------
0          |0          |0          |0          
0          |0          |0          |10         
0          |10         |0          |0          
0          |10         |0          |10         
10         |0          |10         |0          
10         |0          |10         |10         
10         |10         |10         |0          
10         |10         |10         |10         
ij> select * from (select (select 1 from s where 1 = 0), b.a from s a, s b) a (b, a),
			  (select (select 1 from s where 1 = 0), b.a from s a, s b) b (b, a) where a.b = b.b;
B          |A          |B          |A          
-----------------------------------------------
ij> insert into t (aa, bb) select * from (select a.a, b.a from s a, s b) a (b, a) where b = a;
2 rows inserted/updated/deleted
ij> select * from t;
AA         |BB         |CC         |DD         |EE         |FF         
-----------------------------------------------------------------------
0          |0          |NULL       |NULL       |NULL       |NULL       
10         |10         |NULL       |NULL       |NULL       |NULL       
ij> delete from t;
2 rows inserted/updated/deleted
ij> -- join within a derived table, 2 predicates can be pushed all the way down
select * from (select a.a, b.a from s a, s b) a (b, a) where b = a and a = 0 and b = 0;
B          |A          
-----------------------
0          |0          
ij> insert into t (aa, bb) select * from (select a.a, b.a from s a, s b) a (b, a) where b = a and a = 0 and b = 0;
1 row inserted/updated/deleted
ij> select * from t;
AA         |BB         |CC         |DD         |EE         |FF         
-----------------------------------------------------------------------
0          |0          |NULL       |NULL       |NULL       |NULL       
ij> delete from t;
1 row inserted/updated/deleted
ij> -- nested derived tables
select * from (select * from (select * from s) a ) a;
A          |B          |C          |D          |E          |F          
-----------------------------------------------------------------------
0          |1          |2          |3          |4          |5          
10         |11         |12         |13         |14         |15         
ij> select * from 
	(select * from 
		(select * from 
			(select * from 
				(select * from 
					(select * from
						(select * from
							(select * from
								(select * from
									(select * from
										(select * from
											(select * from
												(select * from
													(select * from
														(select * from
															(select * from s) a
														) a
													) a
												) a
											) a
										) a
									) a
								) a
							) a
						) a
					) a
				) a
			) a
		) a
	) a;
A          |B          |C          |D          |E          |F          
-----------------------------------------------------------------------
0          |1          |2          |3          |4          |5          
10         |11         |12         |13         |14         |15         
ij> -- test predicate push through
select * from
(select a.a as a1, b.a as a2 from s a, s b) a 
where a.a1 = 0 and a.a2 = 10;
A1         |A2         
-----------------------
0          |10         
ij> -- push column = column through
select * from (select a, a from s) a (x, y) where x = y;
X          |Y          
-----------------------
0          |0          
10         |10         
ij> select * from (select a, a from s) a (x, y) where x + y = x * y;
X          |Y          
-----------------------
0          |0          
ij> -- return contants and expressions from derived table
select * from (select 1 from s) a;
1          
-----------
1          
1          
ij> select * from (select 1 from s) a (x) where x = 1;
X          
-----------
1          
1          
ij> select * from (select 1 from s a, s b where a.a = b.a) a (x);
X          
-----------
1          
1          
ij> select * from (select 1 from s a, s b where a.a = b.a) a (x) where x = 1;
X          
-----------
1          
1          
ij> select * from (select a + 1 from s) a;
1          
-----------
1          
11         
ij> select * from (select a + 1 from s) a (x) where x = 1;
X          
-----------
1          
ij> select * from (select a.a + 1 from s a, s b where a.a = b.a) a (x) where x = 1;
X          
-----------
1          
ij> -- Bug 2767, don't flatten derived table with join
create table tab1(tab1_c1 int, tab1_c2 int);
0 rows inserted/updated/deleted
ij> create table tab2(tab2_c1 int, tab2_c2 int);
0 rows inserted/updated/deleted
ij> insert into tab1 values (1, 1), (2, 2);
2 rows inserted/updated/deleted
ij> insert into tab2 values (1, 1), (2, 2);
2 rows inserted/updated/deleted
ij> select * from (select * from tab1, tab2) c where tab1_c1 in (1, 3);
TAB1_C1    |TAB1_C2    |TAB2_C1    |TAB2_C2    
-----------------------------------------------
1          |1          |1          |1          
1          |1          |2          |2          
ij> drop table tab1;
0 rows inserted/updated/deleted
ij> drop table tab2;
0 rows inserted/updated/deleted
ij> drop table s;
0 rows inserted/updated/deleted
ij> drop table t;
0 rows inserted/updated/deleted
ij>