📄 subquery.out
字号:
ij> ---- 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/deletedij> 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> commit;ij> -- exists -- non-correlated-- negative tests-- "mis"qualified allselect * 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 listselect * 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 listselect * 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 levelselect * 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 subqueryselect * 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 listselect * 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 setselect * from s where exists (select * from t where i = -1);I |S |C |VC |B -----------------------------------------------------------------------------------------------------ij> -- test semantics of AnyResultSetselect * 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 tableselect * 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 ORselect * 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 nullselect * 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 existsselect * 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 nodeselect * from s where i = (select * from t);ERROR 42X38: 'SELECT *' only allowed in EXISTS and NOT EXISTS subqueries.ij> -- too many columns in select listselect * from s where i = (select i, s from t);ERROR 42X39: Subquery is only allowed to return a single column.ij> -- no conversionsselect * 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 subqueryselect * 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 violationselect * 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 --------------------------------------------------------------------------------------------------------------------------------⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -