📄 subquery.out
字号:
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 statementsprepare 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/deletedij> execute pstmt2;I -----------1 ij> remove pstmt2;ij> rollback;ij> -- correlated subquery in select list of a derived tableselect * 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 subqueriescreate table li(i int, s smallint, l bigint);0 rows inserted/updated/deletedij> insert into li values (null, null, null);1 row inserted/updated/deletedij> insert into li values (1, 1, 1);1 row inserted/updated/deletedij> insert into li values (2, 2, 2);1 row inserted/updated/deletedij> 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 nodeselect i in (1,2) from (select i from s) as tmp(i);ERROR 42X01: Syntax error: Encountered "in" at line 6, column 10.ij> -- conditional expressionselect i = 1 ? 1 : i from (select i from s) as tmp(i);ERROR 42X01: Syntax error: Encountered "=" at line 2, column 10.ij> -- more tests for correlated column resolutionselect * 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 checkselect * 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);I |S |C |VC |B -----------------------------------------------------------------------------------------------------ERROR 21000: Scalar subquery is only allowed to return a single row.ij> -- tests for distinct expression subquerycreate table dist1 (c1 int);0 rows inserted/updated/deletedij> create table dist2 (c1 int);0 rows inserted/updated/deletedij> insert into dist1 values null, 1, 2;3 rows inserted/updated/deletedij> insert into dist2 values null, null;2 rows inserted/updated/deletedij> -- no match, no violationselect * from dist1 where c1 = (select distinct c1 from dist2);C1 -----------ij> -- violationinsert into dist2 values 1;1 row inserted/updated/deletedij> 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 violationupdate dist2 set c1 = 2;3 rows inserted/updated/deletedij> select * from dist1 where c1 = (select distinct c1 from dist2);C1 -----------2 ij> drop table dist1;0 rows inserted/updated/deletedij> drop table dist2;0 rows inserted/updated/deletedij> ------------------------------------ updatecreate table u (i int, s smallint, c char(30), vc char(30), b bigint);0 rows inserted/updated/deletedij> insert into u select * from s;3 rows inserted/updated/deletedij> select * from u;I |S |C |VC |B -----------------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> update u set b = exists (select b from t)where vc <> (select vc from s where vc = '1');ERROR 42X01: Syntax error: Encountered "exists" at line 1, column 18.ij> select * from u;I |S |C |VC |B -----------------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> delete from u;3 rows inserted/updated/deletedij> insert into u select * from s;3 rows inserted/updated/deletedij> -- deletedelete from u where c < (select c from t where c = '2');2 rows inserted/updated/deletedij> select * from u;I |S |C |VC |B -----------------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL ij> -- restore udelete from u;1 row inserted/updated/deletedij> insert into u select * from s;3 rows inserted/updated/deletedij> -- check clean up when errors occur in subqueries-- insertinsert into u select * from s s_outerwhere 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> -- deletedelete 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 ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -