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 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; A ----- false true true ij> select ( i in (select i from t where 1 = 0) ) a from s order by a; A ----- false false false ij> select ( (i in (select i from t where 1 = 0)) is null ) a from s order by a; A ----- false false false 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); 1 ----- NULL false true 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 14. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> -- 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 42821: Columns of type 'BIGINT' cannot hold values of type 'BOOLEAN'. 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> -- DERBY-4549: NPE in JBitSet CREATE TABLE ABC (ID INT); 0 rows inserted/updated/deleted ij> CREATE TABLE DEF (ID INT); 0 rows inserted/updated/deleted ij> -- compilation of the statement used to fail with NPE PREPARE PS AS 'SELECT * FROM ABC t1 WHERE (SELECT DISTINCT t2.ID FROM DEF t2) IN (SELECT t3.ID FROM DEF t3)'; ij> -- empty tables, empty result EXECUTE PS; ID ----------- ij> -- now, test with data in the tables INSERT INTO ABC VALUES 1, 2; 2 rows inserted/updated/deleted ij> EXECUTE PS; ID ----------- ij> INSERT INTO DEF VALUES 2; 1 row inserted/updated/deleted ij> EXECUTE PS; ID ----------- 1 2 ij> INSERT INTO DEF VALUES 2; 1 row inserted/updated/deleted ij> EXECUTE PS; ID ----------- 1 2 ij> INSERT INTO DEF VALUES 3; 1 row inserted/updated/deleted ij> -- will fail because left operand of IN is no longer scalar EXECUTE PS; ERROR 21000: Scalar subquery is only allowed to return a single row. ij> DROP TABLE ABC; 0 rows inserted/updated/deleted ij> DROP TABLE DEF; 0 rows inserted/updated/deleted ij>