📄 subquery2.out
字号:
ij> ---- 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/deletedij> autocommit off;ij> autocommit off;ij> -- create the all type tablescreate table s (i int, s smallint, c char(30), vc char(30), b bigint);0 rows inserted/updated/deletedij> create table t (i int, s smallint, c char(30), vc char(30), b bigint);0 rows inserted/updated/deletedij> create table tt (ii int, ss smallint, cc char(30), vcvc char(30), b bigint);0 rows inserted/updated/deletedij> create table ttt (iii int, sss smallint, ccc char(30), vcvcvc char(30));0 rows inserted/updated/deletedij> -- populate the tablesinsert into s values (null, null, null, null, null);1 row inserted/updated/deletedij> insert into s values (0, 0, '0', '0', 0);1 row inserted/updated/deletedij> insert into s values (1, 1, '1', '1', 1);1 row inserted/updated/deletedij> insert into t values (null, null, null, null, null);1 row inserted/updated/deletedij> insert into t values (0, 0, '0', '0', 0);1 row inserted/updated/deletedij> insert into t values (1, 1, '1', '1', 1);1 row inserted/updated/deletedij> insert into t values (1, 1, '1', '1', 1);1 row inserted/updated/deletedij> insert into t values (2, 2, '2', '2', 1);1 row inserted/updated/deletedij> insert into tt values (null, null, null, null, null);1 row inserted/updated/deletedij> insert into tt values (0, 0, '0', '0', 0);1 row inserted/updated/deletedij> insert into tt values (1, 1, '1', '1', 1);1 row inserted/updated/deletedij> insert into tt values (1, 1, '1', '1', 1);1 row inserted/updated/deletedij> insert into tt values (2, 2, '2', '2', 1);1 row inserted/updated/deletedij> insert into ttt values (null, null, null, null);1 row inserted/updated/deletedij> insert into ttt values (11, 11, '11', '11');1 row inserted/updated/deletedij> insert into ttt values (11, 11, '11', '11');1 row inserted/updated/deletedij> insert into ttt values (22, 22, '22', '22');1 row inserted/updated/deletedij> -- ANY subqueries-- negative tests-- select * subqueryselect * from s where s = ANY (select * from s);ERROR 42X38: 'SELECT *' only allowed in EXISTS and NOT EXISTS subqueries.ij> -- incompatable typesselect * 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 operatorselect * from s where s * ANY (select c from t);ERROR 42X01: Syntax error: Encountered "ANY" at line 2, column 27.ij> -- ? in select list of subqueryselect * 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 subqueryselect * 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 listselect * 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);⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -