ij> -- -- subquery tests -- autocommit off; ij> CREATE FUNCTION ConsistencyChecker() RETURNS VARCHAR(128) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ConsistencyChecker.runConsistencyChecker' LANGUAGE JAVA PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij> autocommit off; ij> -- create the all type tables create table s (i int, s smallint, c char(30), vc char(30), b bigint); 0 rows inserted/updated/deleted ij> create table t (i int, s smallint, c char(30), vc char(30), b bigint); 0 rows inserted/updated/deleted ij> create table tt (ii int, ss smallint, cc char(30), vcvc char(30), b bigint); 0 rows inserted/updated/deleted ij> create table ttt (iii int, sss smallint, ccc char(30), vcvcvc char(30)); 0 rows inserted/updated/deleted ij> -- populate the tables insert into s values (null, null, null, null, null); 1 row inserted/updated/deleted ij> insert into s values (0, 0, '0', '0', 0); 1 row inserted/updated/deleted ij> insert into s values (1, 1, '1', '1', 1); 1 row inserted/updated/deleted ij> insert into t values (null, null, null, null, null); 1 row inserted/updated/deleted ij> insert into t values (0, 0, '0', '0', 0); 1 row inserted/updated/deleted ij> insert into t values (1, 1, '1', '1', 1); 1 row inserted/updated/deleted ij> insert into t values (1, 1, '1', '1', 1); 1 row inserted/updated/deleted ij> insert into t values (2, 2, '2', '2', 1); 1 row inserted/updated/deleted ij> insert into tt values (null, null, null, null, null); 1 row inserted/updated/deleted ij> insert into tt values (0, 0, '0', '0', 0); 1 row inserted/updated/deleted ij> insert into tt values (1, 1, '1', '1', 1); 1 row inserted/updated/deleted ij> insert into tt values (1, 1, '1', '1', 1); 1 row inserted/updated/deleted ij> insert into tt values (2, 2, '2', '2', 1); 1 row inserted/updated/deleted ij> insert into ttt values (null, null, null, null); 1 row inserted/updated/deleted ij> insert into ttt values (11, 11, '11', '11'); 1 row inserted/updated/deleted ij> insert into ttt values (11, 11, '11', '11'); 1 row inserted/updated/deleted ij> insert into ttt values (22, 22, '22', '22'); 1 row inserted/updated/deleted ij> commit; ij> -- exists -- non-correlated -- negative tests -- "mis"qualified all select * from s where exists (select tt.* from t); ERROR 42X10: 'TT' is not an exposed table name in the scope in which it appears. ij> select * from s where exists (select t.* from t tt); ERROR 42X10: 'T' is not an exposed table name in the scope in which it appears. ij> -- too many columns in select list select * from s where exists (select i, s from t); ERROR 42X39: Subquery is only allowed to return a single column. ij> -- invalid column reference in select list select * from s where exists (select nosuchcolumn from t); ERROR 42X04: Column 'NOSUCHCOLUMN' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'NOSUCHCOLUMN' is not a column in the target table. ij> -- multiple matches at subquery level select * from s where exists (select i from s, t); ERROR 42X03: Column name 'I' is in more than one table in the FROM list. ij> -- ? parameter in select list of exists subquery select * from s where exists (select ? from s); ERROR 42X34: There is a ? parameter in the select list. This is not allowed. ij> -- positive tests -- qualified * select * from s where exists (select s.* from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s t where exists (select t.* from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s u where exists (select u.* from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> -- column reference in select list select * from s where exists (select i from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where exists (select t.i from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> -- subquery returns empty result set select * from s where exists (select * from t where i = -1); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> -- test semantics of AnyResultSet select * from s where exists (select t.* from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where exists (select 0 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> -- subquery in derived table select * from (select * from s where exists (select * from t) and i = 0) a; I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 ij> -- exists under an OR select * from s where 0=1 or exists (select * from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where 1=1 or exists (select * from t where 0=1); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where exists (select * from t where 0=1) or exists (select * from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where exists (select * from t where exists (select * from t where 0=1) or exists (select * from t)); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> -- (exists empty set) is null select * from s where (exists (select * from t where 0=1)) is null; I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> -- not exists select * from s where not exists (select * from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where not exists (select * from t where i = -1); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> -- expression subqueries -- non-correlated -- negative tests -- all node select * from s where i = (select * from t); ERROR 42X38: 'SELECT *' only allowed in EXISTS and NOT EXISTS subqueries. ij> -- too many columns in select list select * from s where i = (select i, s from t); ERROR 42X39: Subquery is only allowed to return a single column. ij> -- no conversions select * from s where i = (select 1 from t); ERROR 21000: Scalar subquery is only allowed to return a single row. ij> select * from s where i = (select b from t); ERROR 21000: Scalar subquery is only allowed to return a single row. ij> -- ? parameter in select list of expression subquery select * from s where i = (select ? from t); ERROR 42X34: There is a ? parameter in the select list. This is not allowed. ij> -- do consistency check on scans, etc. values ConsistencyChecker(); 1 -------------------------------------------------------------------------------------------------------------------------------- No open scans, etc. 2 dependencies found ij> -- cardinality violation select * from s where i = (select i from t); ERROR 21000: Scalar subquery is only allowed to return a single row. ij> -- do consistency check on scans, etc. values ConsistencyChecker(); 1 -------------------------------------------------------------------------------------------------------------------------------- No open scans, etc. 2 dependencies found ij> select * from s where s = (select s from t where s = 1); ERROR 21000: Scalar subquery is only allowed to return a single row. ij> -- do consistency check on scans, etc. values ConsistencyChecker(); 1 -------------------------------------------------------------------------------------------------------------------------------- No open scans, etc. 2 dependencies found ij> update s set b = (select max(b) from t) where vc <> (select vc from t where vc = '1'); ERROR 21000: Scalar subquery is only allowed to return a single row. ij> -- do consistency check on scans, etc. values ConsistencyChecker(); 1 -------------------------------------------------------------------------------------------------------------------------------- No open scans, etc. 2 dependencies found ij> delete from s where c = (select c from t where c = '1'); ERROR 21000: Scalar subquery is only allowed to return a single row. ij> -- do consistency check on scans, etc. values ConsistencyChecker(); 1 -------------------------------------------------------------------------------------------------------------------------------- No open scans, etc. 2 dependencies found ij> -- positive tests select * from s; I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from t; I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 1 |1 |1 |1 |1 2 |2 |2 |2 |1 ij> -- simple subquery for each data type select * from s where i = (select i from t where i = 0); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 ij> select * from s where s = (select s from t where s = 0); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 ij> select * from s where c = (select c from t where c = '0'); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 ij> select * from s where vc = (select vc from t where vc = '0'); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 ij> select * from s where b = (select max(b) from t where b = 0); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 ij> select * from s where b = (select max(b) from t where i = 2); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 1 |1 |1 |1 |1 ij> -- ? parameter on left hand side of expression subquery prepare subq1 as 'select * from s where ? = (select i from t where i = 0)'; ij> execute subq1 using 'values (0)'; I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> remove subq1; ij> -- conversions select * from s where i = (select s from t where s = 0); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 ij> select * from s where s = (select i from t where i = 0); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 ij> select * from s where c = (select vc from t where vc = '0'); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 ij> select * from s where vc = (select c from t where c = '0'); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 ij> -- (select nullable_column ...) is null -- On of each data type to test clone() select * from s where (select s from s where i is null) is null; I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where (select i from s where i is null) is null; I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where (select c from s where i is null) is null; I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where (select vc from s where i is null) is null; I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where (select b from s where i is null) is null; I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where (select 1 from t where exists (select * from t where 1 = 0) and s = -1) is null; I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> -- subquery = subquery select * from s where (select i from t where i = 0) = (select s from t where s = 0); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> -- multiple subqueries at the same level select * from s where i = (select s from t where s = 0) and s = (select i from t where i = 2); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where i = (select s from t where s = 0) and s = (select i from t where i = 0); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 ij> -- nested subqueries select * from s where i = (select i from t where s = (select i from t where s = 2)); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where i = (select i - 1 from t where s = (select i from t where s = 2)); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 1 |1 |1 |1 |1 ij> -- expression subqueries in select list select (select i from t where 0=1) from s; 1 ----------- NULL NULL NULL ij> select (select i from t where i = 2) * (select s from t where i = 2) from s where i > (select i from t where i = 0) - (select i from t where i = 0); 1 ----------- 4 ij> -- in subqueries -- negative tests -- select * subquery select * from s where s in (select * from s); ERROR 42X38: 'SELECT *' only allowed in EXISTS and NOT EXISTS subqueries. ij> -- incompatable types select * from s where s in (select b from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> -- positive tests -- constants on left side of subquery select * from s where 1 in (select s from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where -1 in (select i from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where '1' in (select vc from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where 0 in (select b from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> -- constants in subquery select list select * from s where i in (select 1 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 1 |1 |1 |1 |1 ij> select * from s where i in (select -1 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where c in (select '1' from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 1 |1 |1 |1 |1 ij> select * from s where b in (select 0 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 ij> -- constants on both sides select * from s where 1=1 in (select 0 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where 0 in (select 0 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> -- compatable types select * from s where c in (select vc from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where vc in (select c from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where i in (select s from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where s in (select i from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> -- empty subquery result set select * from s where i in (select i from t where 1 = 0); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where (i in (select i from t where i = 0)) is null; I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> -- select list select i in (select i from t) from s; ERROR 42X01: Syntax error: Encountered "in" at line 2, column 10. ij> select i in (select i from t where 1 = 0) from s; ERROR 42X01: Syntax error: Encountered "in" at line 1, column 10. ij> select (i in (select i from t where 1 = 0)) is null from s; ERROR 42X01: Syntax error: Encountered "in" at line 1, column 11. ij> -- subquery under an or select i from s where i = -1 or i in (select i from t); I ----------- 0 1 ij> select i from s where i = 0 or i in (select i from t where i = -1); I ----------- 0 ij> select i from s where i = -1 or i in (select i from t where i = -1 or i = 1); I ----------- 1 ij> -- distinct elimination select i from s where i in (select i from s); I ----------- 0 1 ij> select i from s where i in (select distinct i from s); I ----------- 0 1 ij> select i from s ss where i in (select i from s where s.i = ss.i); I ----------- 0 1 ij> select i from s ss where i in (select distinct i from s where s.i = ss.i); I ----------- 0 1 ij> -- do consistency check on scans, etc. values ConsistencyChecker(); 1 -------------------------------------------------------------------------------------------------------------------------------- No open scans, etc. 2 dependencies found ij> -- correlated subqueries -- negative tests -- multiple matches at parent level select * from s, t where exists (select i from tt); ERROR 42X03: Column name 'I' is in more than one table in the FROM list. ij> -- match is against base table, but not derived column list select * from s ss (c1, c2, c3, c4, c5) where exists (select i from tt); ERROR 42X04: Column 'I' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'I' is not a column in the target table. ij> select * from s ss (c1, c2, c3, c4, c5) where exists (select ss.i from tt); ERROR 42X04: Column 'SS.I' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'SS.I' is not a column in the target table. ij> -- correlation name exists at both levels, but only column match is at -- parent level select * from s where exists (select s.i from tt s); ERROR 42X04: Column 'S.I' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'S.I' is not a column in the target table. ij> -- only match is at peer level select * from s where exists (select * from tt) and exists (select ii from t); ERROR 42X04: Column 'II' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'II' is not a column in the target table. ij> select * from s where exists (select * from tt) and exists (select tt.ii from t); ERROR 42X04: Column 'TT.II' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'TT.II' is not a column in the target table. ij> -- correlated column in a derived table select * from s, (select * from tt where i = ii) a; ERROR 42X04: Column 'I' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'I' is not a column in the target table. ij> select * from s, (select * from tt where s.i = ii) a; ERROR 42X04: Column 'S.I' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'S.I' is not a column in the target table. ij> -- positive tests -- simple correlated subqueries select (select i from tt where ii = i and ii <> 1) from s; 1 ----------- NULL 0 NULL ij> select (select s.i from tt where ii = s.i and ii <> 1) from s; 1 ----------- NULL 0 NULL ij> select (select s.i from ttt where iii = i) from s; 1 ----------- NULL NULL NULL ij> select * from s where exists (select * from tt where i = ii and ii <> 1); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 ij> select * from s where exists (select * from tt where s.i = ii and ii <> 1); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 ij> select * from s where exists (select * from ttt where i = iii); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> -- 1 case where we get a cardinality violation after a few rows select (select i from tt where ii = i) from s; 1 ----------- NULL 0 ERROR 21000: Scalar subquery is only allowed to return a single row. ij> -- skip levels to find match select * from s where exists (select * from ttt where iii = (select 11 from tt where ii = i and ii <> 1)); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 ij> -- join in subquery select * from s where i in (select i from t, tt where s.i <> i and i = ii); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where i in (select i from t, ttt where s.i < iii and s.i = t.i); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> -- join in outer query block select s.i, t.i from s, t where exists (select * from ttt where iii = 1); I |I ----------------------- ij> select s.i, t.i from s, t where exists (select * from ttt where iii = 11); I |I ----------------------- NULL |NULL NULL |0 NULL |1 NULL |1 NULL |2 0 |NULL 0 |0 0 |1 0 |1 0 |2 1 |NULL 1 |0 1 |1 1 |1 1 |2 ij> -- joins in both query blocks select s.i, t.i from s, t where t.i = (select iii from ttt, tt where iii = t.i); I |I ----------------------- ij> select s.i, t.i from s, t where t.i = (select ii from ttt, tt where s.i = t.i and t.i = tt.ii and iii = 22 and ii <> 1); I |I ----------------------- 0 |0 ij> -- Beetle 5382 proper caching of subqueries in prepared statements prepare pstmt as 'select s.i from s where s.i in (select s.i from s, t where s.i = t.i and t.s = ?)'; ij> execute pstmt using 'values(0)'; I ----------- 0 ij> execute pstmt using 'values(1)'; I ----------- 1 ij> remove pstmt; ij> commit; ij> prepare pstmt2 as 'select s.i from s where s.i in (select s.i from s, t where s.i = t.i and t.s = 3)'; ij> execute pstmt2; I ----------- ij> insert into t(i,s) values(1,3); 1 row inserted/updated/deleted ij> execute pstmt2; I ----------- 1 ij> remove pstmt2; ij> rollback; ij> -- correlated subquery in select list of a derived table select * from (select (select iii from ttt where sss > i and sss = iii and iii <> 11) from s) a; 1 ----------- NULL 22 22 ij> -- bigint and subqueries create table li(i int, s smallint, l bigint); 0 rows inserted/updated/deleted ij> insert into li values (null, null, null); 1 row inserted/updated/deleted ij> insert into li values (1, 1, 1); 1 row inserted/updated/deleted ij> insert into li values (2, 2, 2); 1 row inserted/updated/deleted ij> select l from li o where l = (select i from li i where o.l = i.i); L -------------------- 1 2 ij> select l from li o where l = (select s from li i where o.l = i.s); L -------------------- 1 2 ij> select l from li o where l = (select l from li i where o.l = i.l); L -------------------- 1 2 ij> select l from li where l in (select i from li); L -------------------- 1 2 ij> select l from li where l in (select s from li); L -------------------- 1 2 ij> select l from li where l in (select l from li); L -------------------- 1 2 ij> ---------------------------------- -- Some extra tests for subquery flattening -- on table expressions (remapColumnReferencesToExpressions() -- binary list node select i in (1,2) from (select i from s) as tmp(i); ERROR 42X01: Syntax error: Encountered "in" at line 6, column 10. ij> -- conditional expression select i = 1 ? 1 : i from (select i from s) as tmp(i); ERROR 42X01: Syntax error: Encountered "=" at line 2, column 10. ij> -- more tests for correlated column resolution select * from s where i = (values i); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select t.* from s, t where t.i = (values s.i); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 1 |1 |1 |1 |1 ij> select * from s where i in (values i); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select t.* from s, t where t.i in (values s.i); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 1 |1 |1 |1 |1 ij> -- tests for not needing to do cardinality check select * from s where i = (select min(i) from s where i is not null); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 ij> select * from s where i = (select min(i) from s group by i); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ERROR 21000: Scalar subquery is only allowed to return a single row. ij> -- tests for distinct expression subquery create table dist1 (c1 int); 0 rows inserted/updated/deleted ij> create table dist2 (c1 int); 0 rows inserted/updated/deleted ij> insert into dist1 values null, 1, 2; 3 rows inserted/updated/deleted ij> insert into dist2 values null, null; 2 rows inserted/updated/deleted ij> -- no match, no violation select * from dist1 where c1 = (select distinct c1 from dist2); C1 ----------- ij> -- violation insert into dist2 values 1; 1 row inserted/updated/deleted ij> select * from dist1 where c1 = (select distinct c1 from dist2); ERROR 21000: Scalar subquery is only allowed to return a single row. ij> -- match, no violation update dist2 set c1 = 2; 3 rows inserted/updated/deleted ij> select * from dist1 where c1 = (select distinct c1 from dist2); C1 ----------- 2 ij> drop table dist1; 0 rows inserted/updated/deleted ij> drop table dist2; 0 rows inserted/updated/deleted ij> ---------------------------------- -- update create table u (i int, s smallint, c char(30), vc char(30), b bigint); 0 rows inserted/updated/deleted ij> insert into u select * from s; 3 rows inserted/updated/deleted ij> select * from u; I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> update u set b = exists (select b from t) where vc <> (select vc from s where vc = '1'); ERROR 42X01: Syntax error: Encountered "exists" at line 1, column 18. ij> select * from u; I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> delete from u; 3 rows inserted/updated/deleted ij> insert into u select * from s; 3 rows inserted/updated/deleted ij> -- delete delete from u where c < (select c from t where c = '2'); 2 rows inserted/updated/deleted ij> select * from u; I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL ij> -- restore u delete from u; 1 row inserted/updated/deleted ij> insert into u select * from s; 3 rows inserted/updated/deleted ij> -- check clean up when errors occur in subqueries -- insert insert into u select * from s s_outer where i = (select s_inner.i/(s_inner.i-1) from s s_inner where s_outer.i = s_inner.i); ERROR 22012: Attempt to divide by zero. ij> select * from u; I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> -- delete delete from u where i = (select i/(i-1) from s where u.i = s.i); ERROR 22012: Attempt to divide by zero. ij> select * from u; I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> -- update update u set i = (select i from s where u.i = s.i) where i = (select i/(i-1) from s where u.i = s.i); ERROR 22012: Attempt to divide by zero. ij> update u set i = (select i/i-1 from s where u.i = s.i) where i = (select i from s where u.i = s.i); ERROR 22012: Attempt to divide by zero. ij> select * from u; I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> -- error in nested subquery select (select (select (select i from s) from s) from s) from s; ERROR 21000: Scalar subquery is only allowed to return a single row. ij> -- do consistency check on scans, etc. values ConsistencyChecker(); 1 -------------------------------------------------------------------------------------------------------------------------------- No open scans, etc. 2 dependencies found ij> -- reset autocommit autocommit on; ij> -- drop the tables drop table li; 0 rows inserted/updated/deleted ij> drop table s; 0 rows inserted/updated/deleted ij> drop table t; 0 rows inserted/updated/deleted ij> drop table tt; 0 rows inserted/updated/deleted ij> drop table ttt; 0 rows inserted/updated/deleted ij> drop table u; 0 rows inserted/updated/deleted ij>