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. -- -- -- subquery tests -- autocommit off; ij> CREATE FUNCTION ConsistencyChecker() RETURNS VARCHAR(128) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.T_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 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 '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) ) a from s order by a; ERROR 42X01: Syntax error: Encountered "in" at line 2, column 12. ij> select ( i in (select i from t where 1 = 0) ) a from s order by a; ERROR 42X01: Syntax error: Encountered "in" at line 1, column 12. ij> select ( (i in (select i from t where 1 = 0)) is null ) a from s order by a; ERROR 42X01: Syntax error: Encountered "in" at line 1, column 13. 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 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 '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 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 '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 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 '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 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 '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 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 '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 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 '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 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 '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); 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> -- subquery with groupby and having clause select distinct vc, i from t as myt1 where s <= (select max(myt1.s) from t as myt2 where myt1.vc = myt2.vc and myt1.s <= myt2.s group by s having count(distinct s) <= 3); VC |I ------------------------------------------ 0 |0 1 |1 2 |2 ij> -- subquery with having clause but no groupby select distinct vc, i from t as myt1 where s <= (select max(myt1.s) from t as myt2 where myt1.vc = myt2.vc and myt1.s <= myt2.s having count(distinct s) <= 3); VC |I ------------------------------------------ 0 |0 1 |1 2 |2 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> -- DERBY-1007: Optimizer for subqueries can return incorrect cost estimates -- leading to sub-optimal join orders for the outer query. Before the patch -- for that isssue, the following query plan will show T3 first and then T1-- -- but that's determined by the optimizer to be the "bad" join order. After -- the fix, the join order will show T1 first, then T3, which is correct -- (based on the optimizer's estimates). create table t1 (i int, j int); 0 rows inserted/updated/deleted ij> insert into T1 values (1,1), (2,2), (3,3), (4,4), (5,5); 5 rows inserted/updated/deleted ij> create table t3 (a int, b int); 0 rows inserted/updated/deleted ij> insert into T3 values (1,1), (2,2), (3,3), (4,4); 4 rows inserted/updated/deleted ij> insert into t3 values (6, 24), (7, 28), (8, 32), (9, 36), (10, 40); 5 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); 0 rows inserted/updated/deleted ij> maximumdisplaywidth 20000; ij> select x1.j, x2.b from (select distinct i,j from t1) x1, (select distinct a,b from t3) x2 where x1.i = x2.a order by x1.j, x2.b; J |B ----------------------- 1 |1 2 |2 3 |3 4 |4 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select x1.j, x2.b from (select distinct i,j from t1) x1, (select distinct a,b from t3) x2 where x1.i = x2.a order by x1.j, x2.b Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Sort ResultSet: Number of opens = 1 Rows input = 4 Rows returned = 4 Eliminate duplicates = false In sorted order = false Sort information: Number of rows input=4 Number of rows output=4 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (5): Number of opens = 1 Rows seen = 4 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 4 Rows filtered = 0 Rows returned = 4 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Distinct Scan ResultSet for T1 at read committed isolation level using instantaneous share row locking: Number of opens = 1 Hash table size = 5 Distinct columns are column numbers (0,1) Rows seen = 5 Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of pages visited=1 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null scan qualifiers: None next qualifiers: None Right result set: Hash Table ResultSet (4): Number of opens = 5 Hash table size = 9 Hash key is column number 0 Rows seen = 9 Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 next qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Source result set: Distinct Scan ResultSet for T3 at read committed isolation level using instantaneous share row locking: Number of opens = 1 Hash table size = 9 Distinct columns are column numbers (0,1) Rows seen = 9 Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of pages visited=1 Number of rows qualified=9 Number of rows visited=9 Scan type=heap start position: null stop position: null scan qualifiers: None next qualifiers: None ij> -- clean up. call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0); 0 rows inserted/updated/deleted ij> drop table t1; 0 rows inserted/updated/deleted ij> drop table t3; 0 rows inserted/updated/deleted ij> -- DERBY-781: Materialize subqueries where possible to avoid creating -- invariant result sets many times. This test case executes a query -- that has subqueries twice: the first time the tables have only a -- few rows in them; the second time they have hundreds of rows in -- them. create table t1 (i int, j int); 0 rows inserted/updated/deleted ij> create table t2 (i int, j int); 0 rows inserted/updated/deleted ij> insert into t1 values (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); 5 rows inserted/updated/deleted ij> insert into t2 values (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); 5 rows inserted/updated/deleted ij> create table t3 (a int, b int); 0 rows inserted/updated/deleted ij> create table t4 (a int, b int); 0 rows inserted/updated/deleted ij> insert into t3 values (2, 2), (4, 4), (5, 5); 3 rows inserted/updated/deleted ij> insert into t4 values (2, 2), (4, 4), (5, 5); 3 rows inserted/updated/deleted ij> -- Use of the term "DISTINCT" makes it so that we don't flatten -- the subqueries. create view V1 as select distinct T1.i, T2.j from T1, T2 where T1.i = T2.i; 0 rows inserted/updated/deleted ij> create view V2 as select distinct T3.a, T4.b from T3, T4 where T3.a = T4.a; 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); 0 rows inserted/updated/deleted ij> maximumdisplaywidth 20000; ij> -- Run the test query the first time, with only a small number -- of rows in each table. Before the patch for DERBY-781 -- the optimizer would have chosen a nested loop join, which -- means that we would generate the result set for the inner -- view multiple times. After DERBY-781 the optimizer will -- choose to do a hash join and thereby materialize the inner -- result set, thus improving performance. Should see a -- Hash join as the top-level join with a HashTableResult as -- the right child of the outermost join. select * from V1, V2 where V1.j = V2.b and V1.i in (1,2,3,4,5); I |J |A |B ----------------------------------------------- 2 |2 |2 |2 4 |4 |4 |4 5 |5 |5 |5 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Run the test query the first time, with only a small number -- of rows in each table. Before the patch for DERBY-781 -- the optimizer would have chosen a nested loop join, which -- means that we would generate the result set for the inner -- view multiple times. After DERBY-781 the optimizer will -- choose to do a hash join and thereby materialize the inner -- result set, thus improving performance. Should see a -- Hash join as the top-level join with a HashTableResult as -- the right child of the outermost join. select * from V1, V2 where V1.j = V2.b and V1.i in (1,2,3,4,5) Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 3 Rows filtered = 0 Rows returned = 3 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 5 Rows returned = 5 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=5 Number of rows output=5 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (7): Number of opens = 1 Rows seen = 5 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows filtered = 0 Rows returned = 5 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Project-Restrict ResultSet (5): Number of opens = 1 Rows seen = 5 Rows filtered = 0 restriction = true projection = false constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0} Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 5 Rows seen = 5 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of pages visited=1 Number of rows qualified=5 Number of rows visited=25 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Right result set: Hash Table ResultSet (13): Number of opens = 5 Hash table size = 3 Hash key is column number 1 Rows seen = 3 Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 next qualifiers: Column[0][0] Id: 1 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Source result set: Sort ResultSet: Number of opens = 1 Rows input = 3 Rows returned = 3 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=3 Number of rows output=3 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (12): Number of opens = 1 Rows seen = 3 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 3 Rows seen from the right = 3 Rows filtered = 0 Rows returned = 3 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T3 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 3 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0} Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=3 Number of rows visited=3 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Hash Scan ResultSet for T4 at read committed isolation level using instantaneous share row locking: Number of opens = 3 Hash table size = 3 Hash key is column number 0 Rows seen = 3 Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of pages visited=1 Number of rows qualified=3 Number of rows visited=3 Scan type=heap start position: null stop position: null scan qualifiers: None next qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> -- Now add more data to the tables. insert into t1 select * from t2; 5 rows inserted/updated/deleted ij> insert into t2 select * from t1; 10 rows inserted/updated/deleted ij> insert into t2 select * from t1; 10 rows inserted/updated/deleted ij> insert into t1 select * from t2; 25 rows inserted/updated/deleted ij> insert into t2 select * from t1; 35 rows inserted/updated/deleted ij> insert into t1 select * from t2; 60 rows inserted/updated/deleted ij> insert into t2 select * from t1; 95 rows inserted/updated/deleted ij> insert into t1 select * from t2; 155 rows inserted/updated/deleted ij> insert into t2 select * from t1; 250 rows inserted/updated/deleted ij> insert into t1 select * from t2; 405 rows inserted/updated/deleted ij> insert into t3 select * from t4; 3 rows inserted/updated/deleted ij> insert into t4 select * from t3; 6 rows inserted/updated/deleted ij> insert into t3 select * from t4; 9 rows inserted/updated/deleted ij> insert into t4 select * from t3; 15 rows inserted/updated/deleted ij> insert into t3 select * from t4; 24 rows inserted/updated/deleted ij> insert into t4 select * from t3; 39 rows inserted/updated/deleted ij> insert into t3 select * from t4; 63 rows inserted/updated/deleted ij> insert into t4 select * from t3; 102 rows inserted/updated/deleted ij> insert into t3 select * from t4; 165 rows inserted/updated/deleted ij> insert into t4 select * from t3; 267 rows inserted/updated/deleted ij> insert into t3 select * from t4; 432 rows inserted/updated/deleted ij> -- Drop the views and recreate them with slightly different -- names. The reason we use different names is to ensure that -- the query will be "different" from the last time and thus we'll -- we'll go through optimization again (instead of just using -- the cached plan from last time). drop view v1; 0 rows inserted/updated/deleted ij> drop view v2; 0 rows inserted/updated/deleted ij> -- Use of the term "DISTINCT" makes it so that we don't flatten -- the subqueries. create view VV1 as select distinct T1.i, T2.j from T1, T2 where T1.i = T2.i; 0 rows inserted/updated/deleted ij> create view VV2 as select distinct T3.a, T4.b from T3, T4 where T3.a = T4.a; 0 rows inserted/updated/deleted ij> -- Now execute the query again using the larger tables. select * from VV1, VV2 where VV1.j = VV2.b and VV1.i in (1,2,3,4,5); I |J |A |B ----------------------------------------------- 2 |2 |2 |2 4 |4 |4 |4 5 |5 |5 |5 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Now execute the query again using the larger tables. select * from VV1, VV2 where VV1.j = VV2.b and VV1.i in (1,2,3,4,5) Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 3 Rows filtered = 0 Rows returned = 3 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 53055 Rows returned = 5 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=53055 Number of rows output=5 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (7): Number of opens = 1 Rows seen = 53055 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 655 Rows seen from the right = 53055 Rows filtered = 0 Rows returned = 53055 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Project-Restrict ResultSet (5): Number of opens = 1 Rows seen = 655 Rows filtered = 0 restriction = true projection = false constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 655 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0} Number of columns fetched=1 Number of pages visited=6 Number of rows qualified=655 Number of rows visited=655 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Hash Scan ResultSet for T2 at read committed isolation level using instantaneous share row locking: Number of opens = 655 Hash table size = 5 Hash key is column number 0 Rows seen = 53055 Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of pages visited=4 Number of rows qualified=405 Number of rows visited=405 Scan type=heap start position: null stop position: null scan qualifiers: None next qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Right result set: Hash Table ResultSet (13): Number of opens = 5 Hash table size = 3 Hash key is column number 1 Rows seen = 3 Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 next qualifiers: Column[0][0] Id: 1 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Source result set: Sort ResultSet: Number of opens = 1 Rows input = 100656 Rows returned = 3 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=100656 Number of rows output=3 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (12): Number of opens = 1 Rows seen = 100656 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 432 Rows seen from the right = 100656 Rows filtered = 0 Rows returned = 100656 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T4 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 432 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of pages visited=4 Number of rows qualified=432 Number of rows visited=432 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Hash Scan ResultSet for T3 at read committed isolation level using instantaneous share row locking: Number of opens = 432 Hash table size = 3 Hash key is column number 0 Rows seen = 100656 Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0} Number of columns fetched=1 Number of pages visited=6 Number of rows qualified=699 Number of rows visited=699 Scan type=heap start position: null stop position: null scan qualifiers: None next qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> -- clean up. call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0); 0 rows inserted/updated/deleted ij> drop view vv1; 0 rows inserted/updated/deleted ij> drop view vv2; 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> -- DERBY-1574: Subquery in COALESCE gives NPE due -- to preprocess not implemented for that node type create table t1 (id int); 0 rows inserted/updated/deleted ij> create table t2 (i integer primary key, j int); 0 rows inserted/updated/deleted ij> insert into t1 values 1,2,3,4,5; 5 rows inserted/updated/deleted ij> insert into t2 values (1,1),(2,4),(3,9),(4,16); 4 rows inserted/updated/deleted ij> update t1 set id = coalesce((select j from t2 where t2.i=t1.id), 0); 5 rows inserted/updated/deleted ij> select * from t1; ID ----------- 1 4 9 16 0 ij> drop table t1; 0 rows inserted/updated/deleted ij> drop table t2; 0 rows inserted/updated/deleted ij> -- DERBY-2218 create table t1 (i int); 0 rows inserted/updated/deleted ij> -- ok select * from t1 where i in (1, 2, (values cast(null as integer))); I ----------- ij> -- expect error, this used to throw NPE select * from t1 where i in (1, 2, (values null)); ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement. ij> select * from t1 where i in (select i from t1 where i in (1, 2, (values null))); ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement. ij> -- expect error select * from t1 where exists (values null); ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement. ij> select * from t1 where exists (select * from t1 where exists(values null)); ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement. ij> select i from t1 where exists (select i from t1 where exists(values null)); ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement. ij> select * from (values null) as t2; ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement. ij> select * from t1 where exists (select 1 from (values null) as t2); ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement. ij> select * from t1 where exists (select * from (values null) as t2); ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement. ij> drop table t1; 0 rows inserted/updated/deleted ij>