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 (ANY and ALL subqueries) -- 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> 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> -- ANY subqueries -- negative tests -- select * subquery select * from s where s = ANY (select * from s); ERROR 42X38: 'SELECT *' only allowed in EXISTS and NOT EXISTS subqueries. ij> -- incompatable types select * from s where s >= ANY (select b from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> -- invalid operator select * from s where s * ANY (select c from t); ERROR 42X01: Syntax error: Encountered "ANY" at line 2, column 27. 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> -- ? in select list of subquery select * from s where s = ANY (select ? from s); ERROR 42X34: There is a ? parameter in the select list. This is not allowed. ij> -- positive tests -- constants on left side of subquery select * from s where 1 = ANY (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 = ANY (select i from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where '1' = ANY (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 = ANY (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> select * from s where 1 <> ANY (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 <> ANY (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 '1' <> ANY (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 <> ANY (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> select * from s where 1 >= ANY (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 >= ANY (select i from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where '1' >= ANY (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 >= ANY (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> select * from s where 1 > ANY (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 > ANY (select i from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where '1' > ANY (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 > ANY (select b from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where 1 <= ANY (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 <= ANY (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 '1' <= ANY (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 <= ANY (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> select * from s where 1 < ANY (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 < ANY (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 '1' < ANY (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 < ANY (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> -- Try a ? parameter on the LHS of a subquery. prepare subq1 as 'select * from s where ? = ANY (select s from t)'; ij> execute subq1 using 'values (1)'; I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> remove subq1; ij> -- constants in subquery select list select * from s where i = ANY (select 1 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 1 |1 |1 |1 |1 ij> select * from s where i = ANY (select -1 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where c = ANY (select '1' from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 1 |1 |1 |1 |1 ij> select * from s where b = ANY (select 1 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 1 |1 |1 |1 |1 ij> select * from s where i <> ANY (select 1 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 ij> select * from s where i <> ANY (select -1 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where c <> ANY (select '1' from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 ij> select * from s where b <> ANY (select 1 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 ij> select * from s where i >= ANY (select 1 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 1 |1 |1 |1 |1 ij> select * from s where i >= ANY (select -1 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where c >= ANY (select '1' from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 1 |1 |1 |1 |1 ij> select * from s where b >= ANY (select 1 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 1 |1 |1 |1 |1 ij> select * from s where i > ANY (select 1 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where i > ANY (select -1 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where c > ANY (select '1' from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where b > ANY (select 1 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where i <= ANY (select 1 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where i <= ANY (select -1 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where c <= ANY (select '1' from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where b <= ANY (select 1 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where i < ANY (select 1 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 ij> select * from s where i < ANY (select -1 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where c < ANY (select '1' from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 ij> select * from s where b < ANY (select 1 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 ij> -- constants on both sides select * from s where 1 = ANY (select 0 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where 0 = ANY (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> select * from s where 1 <> ANY (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> select * from s where 0 <> ANY (select 0 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where 1 >= ANY (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> select * from s where 0 >= ANY (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> select * from s where 1 > ANY (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> select * from s where 0 > ANY (select 0 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where 1 <= ANY (select 0 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where 0 <= ANY (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> select * from s where 1 < ANY (select 0 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where 0 < ANY (select 0 from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> -- compatable types select * from s where c = ANY (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 = ANY (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 = ANY (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 = ANY (select i from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where c <> ANY (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 <> ANY (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 <> ANY (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 <> ANY (select i from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where c >= ANY (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 >= ANY (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 >= ANY (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 >= ANY (select i from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where c > ANY (select vc from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 1 |1 |1 |1 |1 ij> select * from s where vc > ANY (select c from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 1 |1 |1 |1 |1 ij> select * from s where i > ANY (select s from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 1 |1 |1 |1 |1 ij> select * from s where s > ANY (select i from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 1 |1 |1 |1 |1 ij> select * from s where c <= ANY (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 <= ANY (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 <= ANY (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 <= ANY (select i from t); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where c < ANY (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 < ANY (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 < ANY (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 < ANY (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 = ANY (select i from t where 1 = 0); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where i <> ANY (select i from t where 1 = 0); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where i >= ANY (select i from t where 1 = 0); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where i > ANY (select i from t where 1 = 0); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where i <= ANY (select i from t where 1 = 0); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> select * from s where i < ANY (select i from t where 1 = 0); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- ij> -- subquery under an or select i from s where i = -1 or i = ANY (select i from t); I ----------- 0 1 ij> select i from s where i = 0 or i = ANY (select i from t where i = -1); I ----------- 0 ij> select i from s where i = -1 or i = ANY (select i from t where i = -1 or i = 1); I ----------- 1 ij> select i from s where i = -1 or i <> ANY (select i from t); I ----------- 0 1 ij> select i from s where i = 0 or i >= ANY (select i from t where i = -1); I ----------- 0 ij> select i from s where i = -1 or i < ANY (select i from t where i = -1 or i = 1); I ----------- 0 ij> select i from s where i = -1 or i >= ANY (select i from t); I ----------- 0 1 ij> select i from s where i = 0 or i > ANY (select i from t where i = -1); I ----------- 0 ij> select i from s where i = -1 or i <> ANY (select i from t where i = -1 or i = 1); I ----------- 0 ij> -- correlated subqueries select * from s where i > ANY (select i from t where s.s > t.s); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 1 |1 |1 |1 |1 ij> select * from s where i >= ANY (select i from t where s.s >= t.s); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where i < ANY (select i from t where s.s < t.s); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where i <= ANY (select i from t where s.s <= t.s); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where i = ANY (select i from t where s.s = t.s); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where i <> ANY (select i from t where s.s <> t.s); I |S |C |VC |B ----------------------------------------------------------------------------------------------------- 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> -- ALL/NOT IN and NOTs -- create tables create table s_3rows (i int); 0 rows inserted/updated/deleted ij> create table t_1 (i int); 0 rows inserted/updated/deleted ij> create table u_null (i int); 0 rows inserted/updated/deleted ij> create table v_empty (i int); 0 rows inserted/updated/deleted ij> create table w_2 (i int); 0 rows inserted/updated/deleted ij> -- populate tables insert into s_3rows values(NULL); 1 row inserted/updated/deleted ij> insert into s_3rows values(1); 1 row inserted/updated/deleted ij> insert into s_3rows values(2); 1 row inserted/updated/deleted ij> insert into u_null values(NULL); 1 row inserted/updated/deleted ij> insert into t_1 values(1); 1 row inserted/updated/deleted ij> insert into w_2 values(2); 1 row inserted/updated/deleted ij> -- test ALLs select * from s_3rows where s_3rows.i not in (select i from t_1); I ----------- 2 ij> select * from s_3rows where s_3rows.i <> ALL (select i from t_1); I ----------- 2 ij> select * from s_3rows where s_3rows.i >= ALL (select i from t_1); I ----------- 1 2 ij> select * from s_3rows where s_3rows.i > ALL (select i from t_1); I ----------- 2 ij> select * from s_3rows where s_3rows.i <= ALL (select i from t_1); I ----------- 1 ij> select * from s_3rows where s_3rows.i < ALL (select i from t_1); I ----------- ij> select * from s_3rows where s_3rows.i = ALL (select i from t_1); I ----------- 1 ij> select * from s_3rows where s_3rows.i not in (select i from u_null); I ----------- ij> select * from s_3rows where s_3rows.i <> ALL (select i from u_null); I ----------- ij> select * from s_3rows where s_3rows.i >= ALL (select i from u_null); I ----------- ij> select * from s_3rows where s_3rows.i > ALL (select i from u_null); I ----------- ij> select * from s_3rows where s_3rows.i <= ALL (select i from u_null); I ----------- ij> select * from s_3rows where s_3rows.i < ALL (select i from u_null); I ----------- ij> select * from s_3rows where s_3rows.i = ALL (select i from u_null); I ----------- ij> select * from s_3rows where s_3rows.i not in (select i from v_empty); I ----------- NULL 1 2 ij> select * from s_3rows where s_3rows.i <> ALL (select i from v_empty); I ----------- NULL 1 2 ij> select * from s_3rows where s_3rows.i >= ALL (select i from v_empty); I ----------- NULL 1 2 ij> select * from s_3rows where s_3rows.i > ALL (select i from v_empty); I ----------- NULL 1 2 ij> select * from s_3rows where s_3rows.i <= ALL (select i from v_empty); I ----------- NULL 1 2 ij> select * from s_3rows where s_3rows.i < ALL (select i from v_empty); I ----------- NULL 1 2 ij> select * from s_3rows where s_3rows.i = ALL (select i from v_empty); I ----------- NULL 1 2 ij> select * from s_3rows where s_3rows.i not in (select i from w_2); I ----------- 1 ij> select * from s_3rows where s_3rows.i <> ALL (select i from w_2); I ----------- 1 ij> select * from s_3rows where s_3rows.i >= ALL (select i from w_2); I ----------- 2 ij> select * from s_3rows where s_3rows.i > ALL (select i from w_2); I ----------- ij> select * from s_3rows where s_3rows.i <= ALL (select i from w_2); I ----------- 1 2 ij> select * from s_3rows where s_3rows.i < ALL (select i from w_2); I ----------- 1 ij> select * from s_3rows where s_3rows.i = ALL (select i from w_2); I ----------- 2 ij> select * from w_2 where w_2.i = ALL (select i from w_2); I ----------- 2 ij> -- NOT = ANY <=> <> ALL select * from s_3rows where NOT s_3rows.i = ANY (select i from w_2); I ----------- 1 ij> select * from s_3rows where s_3rows.i <> ALL (select i from w_2); I ----------- 1 ij> select * from s_3rows where NOT s_3rows.i = ANY (select i from v_empty); I ----------- NULL 1 2 ij> select * from s_3rows where s_3rows.i <> ALL (select i from v_empty); I ----------- NULL 1 2 ij> -- NOT <> ANY <=> = ALL select * from s_3rows where NOT s_3rows.i <> ANY (select i from w_2); I ----------- 2 ij> select * from s_3rows where s_3rows.i = ALL (select i from w_2); I ----------- 2 ij> select * from s_3rows where NOT s_3rows.i <> ANY (select i from v_empty); I ----------- NULL 1 2 ij> select * from s_3rows where s_3rows.i = ALL (select i from v_empty); I ----------- NULL 1 2 ij> -- NOT >= ANY <=> < ALL select * from s_3rows where NOT s_3rows.i >= ANY (select i from w_2); I ----------- 1 ij> select * from s_3rows where s_3rows.i < ALL (select i from w_2); I ----------- 1 ij> select * from s_3rows where NOT s_3rows.i >= ANY (select i from v_empty); I ----------- NULL 1 2 ij> select * from s_3rows where s_3rows.i < ALL (select i from v_empty); I ----------- NULL 1 2 ij> -- NOT > ANY <=> <= ALL select * from s_3rows where NOT s_3rows.i > ANY (select i from w_2); I ----------- 1 2 ij> select * from s_3rows where s_3rows.i <= ALL (select i from w_2); I ----------- 1 2 ij> select * from s_3rows where NOT s_3rows.i > ANY (select i from v_empty); I ----------- NULL 1 2 ij> select * from s_3rows where s_3rows.i <= ALL (select i from v_empty); I ----------- NULL 1 2 ij> -- NOT <= ANY <=> > ALL select * from s_3rows where NOT s_3rows.i <= ANY (select i from w_2); I ----------- ij> select * from s_3rows where s_3rows.i > ALL (select i from w_2); I ----------- ij> select * from s_3rows where NOT s_3rows.i <= ANY (select i from v_empty); I ----------- NULL 1 2 ij> select * from s_3rows where s_3rows.i > ALL (select i from v_empty); I ----------- NULL 1 2 ij> -- NOT < ANY <=> >= ALL select * from s_3rows where NOT s_3rows.i < ANY (select i from w_2); I ----------- 2 ij> select * from s_3rows where s_3rows.i >= ALL (select i from w_2); I ----------- 2 ij> select * from s_3rows where NOT s_3rows.i < ANY (select i from v_empty); I ----------- NULL 1 2 ij> select * from s_3rows where s_3rows.i >= ALL (select i from v_empty); I ----------- NULL 1 2 ij> -- NOT = ALL <=> <> ANY select * from s_3rows where NOT s_3rows.i = ALL (select i from w_2); I ----------- 1 ij> select * from s_3rows where s_3rows.i <> ANY (select i from w_2); I ----------- 1 ij> select * from s_3rows where NOT s_3rows.i = ALL (select i from v_empty); I ----------- ij> select * from s_3rows where s_3rows.i <> ANY (select i from v_empty); I ----------- ij> -- NOT <> ALL <=> = ANY select * from s_3rows where NOT s_3rows.i <> ALL (select i from w_2); I ----------- 2 ij> select * from s_3rows where s_3rows.i = ANY (select i from w_2); I ----------- 2 ij> select * from s_3rows where NOT s_3rows.i <> ALL (select i from v_empty); I ----------- ij> select * from s_3rows where s_3rows.i = ANY (select i from v_empty); I ----------- ij> -- NOT >= ALL <=> < ANY select * from s_3rows where NOT s_3rows.i >= ALL (select i from w_2); I ----------- 1 ij> select * from s_3rows where s_3rows.i < ANY (select i from w_2); I ----------- 1 ij> select * from s_3rows where NOT s_3rows.i >= ALL (select i from v_empty); I ----------- ij> select * from s_3rows where s_3rows.i < ANY (select i from v_empty); I ----------- ij> -- NOT > ALL <=> <= ANY select * from s_3rows where NOT s_3rows.i > ALL (select i from w_2); I ----------- 1 2 ij> select * from s_3rows where s_3rows.i <= ANY (select i from w_2); I ----------- 1 2 ij> select * from s_3rows where NOT s_3rows.i > ALL (select i from v_empty); I ----------- ij> select * from s_3rows where s_3rows.i <= ANY (select i from v_empty); I ----------- ij> -- NOT <= ALL <=> > ANY select * from s_3rows where NOT s_3rows.i <= ALL (select i from w_2); I ----------- ij> select * from s_3rows where s_3rows.i > ANY (select i from w_2); I ----------- ij> select * from s_3rows where NOT s_3rows.i <= ALL (select i from v_empty); I ----------- ij> select * from s_3rows where s_3rows.i > ANY (select i from v_empty); I ----------- ij> -- NOT < ALL <=> >= ANY select * from s_3rows where NOT s_3rows.i < ALL (select i from w_2); I ----------- 2 ij> select * from s_3rows where s_3rows.i >= ANY (select i from w_2); I ----------- 2 ij> select * from s_3rows where NOT s_3rows.i < ALL (select i from v_empty); I ----------- ij> select * from s_3rows where s_3rows.i >= ANY (select i from v_empty); I ----------- ij> -- test skipping of generating is null predicates for non-nullable columns create table t1 (c1 int not null, c2 int); 0 rows inserted/updated/deleted ij> create table t2 (c1 int not null, c2 int); 0 rows inserted/updated/deleted ij> insert into t1 values(1, 2); 1 row inserted/updated/deleted ij> insert into t2 values(0, 3); 1 row inserted/updated/deleted ij> select * from t1 where c1 not in (select c2 from t2); C1 |C2 ----------------------- 1 |2 ij> select * from t1 where c2 not in (select c1 from t2); C1 |C2 ----------------------- 1 |2 ij> select * from t1 where c1 not in (select c1 from t2); C1 |C2 ----------------------- 1 |2 ij> drop table t1; 0 rows inserted/updated/deleted ij> drop table t2; 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 * from t) where vc < ANY (select vc from s); 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 < ANY (select c from t); 2 rows inserted/updated/deleted ij> select * from u; I |S |C |VC |B ----------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL ij> -- do consistency check on scans, etc. values ConsistencyChecker(); 1 -------------------------------------------------------------------------------------------------------------------------------- No open scans, etc. 2 dependencies found ij> -- reset autocommit autocommit on; ij> -- drop the tables drop table 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> drop table s_3rows; 0 rows inserted/updated/deleted ij> drop table t_1; 0 rows inserted/updated/deleted ij> drop table u_null; 0 rows inserted/updated/deleted ij> drop table v_empty; 0 rows inserted/updated/deleted ij> drop table w_2; 0 rows inserted/updated/deleted ij> -- DERBY-634: Dynamic subquery materialization can cause stack overflow create table parentT ( i int, j int, k int); 0 rows inserted/updated/deleted ij> create table childT ( i int, j int, k int); 0 rows inserted/updated/deleted ij> -- Load some data insert into parentT values (1,1,1), (2,2,2), (3,3,3), (4,4,4); 4 rows inserted/updated/deleted ij> insert into parentT select i+4, j+4, k+4 from parentT; 4 rows inserted/updated/deleted ij> insert into parentT select i+8, j+8, k+8 from parentT; 8 rows inserted/updated/deleted ij> insert into parentT select i+16, j+16, k+16 from parentT; 16 rows inserted/updated/deleted ij> insert into parentT select i+32, j+32, k+32 from parentT; 32 rows inserted/updated/deleted ij> insert into parentT select i+64, j+64, k+64 from parentT; 64 rows inserted/updated/deleted ij> insert into parentT select i+128, j+128, k+128 from parentT; 128 rows inserted/updated/deleted ij> insert into parentT select i+256, j+256, k+256 from parentT; 256 rows inserted/updated/deleted ij> insert into parentT select i+512, j+512, k+512 from parentT; 512 rows inserted/updated/deleted ij> insert into parentT select i+1024, j+1024, k+1024 from parentT; 1024 rows inserted/updated/deleted ij> insert into parentT select i+2048, j+2048, k+2048 from parentT; 2048 rows inserted/updated/deleted ij> insert into parentT select i+4096, j+4096, k+4096 from parentT; 4096 rows inserted/updated/deleted ij> insert into parentT select i+8192, j+8192, k+8192 from parentT; 8192 rows inserted/updated/deleted ij> -- Try with three different sizes of subquery results. update parentT set j = j /10; 16384 rows inserted/updated/deleted ij> update parentT set k = k /100; 16384 rows inserted/updated/deleted ij> create unique index parentIdx on parentT(i); 0 rows inserted/updated/deleted ij> insert into childT select * from parentT; 16384 rows inserted/updated/deleted ij> select count(*) from parentT where i < 10 and i not in (select i from childT); 1 ----------- 0 ij> select count(*) from parentT where i< 10 and exists (select i from childT where childT.i=parentT.i); 1 ----------- 9 ij> select count(*) from parentT where i< 10 and j not in (select distinct j from childT); 1 ----------- 0 ij> select count(*) from parentT where i< 10 and exists (select distinct j from childT where childT.j=parentT.j); 1 ----------- 9 ij> select count(*) from parentT where i< 10 and k not in (select distinct k from childT); 1 ----------- 0 ij> select count(*) from parentT where i< 10 and exists (select distinct k from childT where childT.k=parentT.k); 1 ----------- 9 ij> drop table childT; 0 rows inserted/updated/deleted ij> drop table parentT; 0 rows inserted/updated/deleted ij>