| 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> |