📄 derived.out
字号:
AA |BB |CC |DD |EE |FF -----------------------------------------------------------------------0 |1 |2 |NULL |NULL |NULL 10 |11 |12 |NULL |NULL |NULL ij> delete from t;2 rows inserted/updated/deletedij> insert into t select a, c, "a", "b", b, "c" from (select * from s) a (a, c, "a", "b", b, "c");2 rows inserted/updated/deletedij> select * from t;AA |BB |CC |DD |EE |FF -----------------------------------------------------------------------0 |1 |2 |3 |4 |5 10 |11 |12 |13 |14 |15 ij> delete from t;2 rows inserted/updated/deletedij> -- project and reorder derived column listselect a, f from (select * from s) a (b, c, d, e, f, a);A |F -----------------------5 |4 15 |14 ij> insert into t (aa, bb) select a, f from (select * from s) a (b, c, d, e, f, a);2 rows inserted/updated/deletedij> select * from t;AA |BB |CC |DD |EE |FF -----------------------------------------------------------------------5 |4 |NULL |NULL |NULL |NULL 15 |14 |NULL |NULL |NULL |NULL ij> delete from t;2 rows inserted/updated/deletedij> -- outer where clause references columns from derived table select * from (select * from s) a (a, b, c, d, e, f) where a = 0;A |B |C |D |E |F -----------------------------------------------------------------------0 |1 |2 |3 |4 |5 ij> select * from (select * from s) a (f, e, d, c, b, a) where f = 0;F |E |D |C |B |A -----------------------------------------------------------------------0 |1 |2 |3 |4 |5 ij> insert into t select * from (select * from s) a (a, b, c, d, e, f) where a = 0;1 row inserted/updated/deletedij> select * from t;AA |BB |CC |DD |EE |FF -----------------------------------------------------------------------0 |1 |2 |3 |4 |5 ij> delete from t;1 row inserted/updated/deletedij> insert into t select * from (select * from s) a (f, e, d, c, b, a) where f = 0;1 row inserted/updated/deletedij> select * from t;AA |BB |CC |DD |EE |FF -----------------------------------------------------------------------0 |1 |2 |3 |4 |5 ij> delete from t;1 row inserted/updated/deletedij> -- join between 2 derived tablesselect * from (select a from s) a, (select a from s) b;A |A -----------------------0 |0 0 |10 10 |0 10 |10 ij> select * from (select a from s) a, (select a from s) b where a.a = b.a;A |A -----------------------0 |0 10 |10 ij> insert into t (aa, bb) select * from (select a from s) a, (select a from s) b where a.a = b.a;2 rows inserted/updated/deletedij> select * from t;AA |BB |CC |DD |EE |FF -----------------------------------------------------------------------0 |0 |NULL |NULL |NULL |NULL 10 |10 |NULL |NULL |NULL |NULL ij> delete from t;2 rows inserted/updated/deletedij> -- join within a derived tableselect * from (select a.a, b.a from s a, s b) a (b, a) where b = a;B |A -----------------------0 |0 10 |10 ij> select * from (select a.a, b.a from s a, s b) a (b, a), (select a.a, b.a from s a, s b) b (b, a) where a.b = b.b;B |A |B |A -----------------------------------------------0 |0 |0 |0 0 |0 |0 |10 0 |10 |0 |0 0 |10 |0 |10 10 |0 |10 |0 10 |0 |10 |10 10 |10 |10 |0 10 |10 |10 |10 ij> select * from (select (select 1 from s where 1 = 0), b.a from s a, s b) a (b, a), (select (select 1 from s where 1 = 0), b.a from s a, s b) b (b, a) where a.b = b.b;B |A |B |A -----------------------------------------------ij> insert into t (aa, bb) select * from (select a.a, b.a from s a, s b) a (b, a) where b = a;2 rows inserted/updated/deletedij> select * from t;AA |BB |CC |DD |EE |FF -----------------------------------------------------------------------0 |0 |NULL |NULL |NULL |NULL 10 |10 |NULL |NULL |NULL |NULL ij> delete from t;2 rows inserted/updated/deletedij> -- join within a derived table, 2 predicates can be pushed all the way downselect * from (select a.a, b.a from s a, s b) a (b, a) where b = a and a = 0 and b = 0;B |A -----------------------0 |0 ij> insert into t (aa, bb) select * from (select a.a, b.a from s a, s b) a (b, a) where b = a and a = 0 and b = 0;1 row inserted/updated/deletedij> select * from t;AA |BB |CC |DD |EE |FF -----------------------------------------------------------------------0 |0 |NULL |NULL |NULL |NULL ij> delete from t;1 row inserted/updated/deletedij> -- nested derived tablesselect * from (select * from (select * from s) a ) a;A |B |C |D |E |F -----------------------------------------------------------------------0 |1 |2 |3 |4 |5 10 |11 |12 |13 |14 |15 ij> select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from s) a ) a ) a ) a ) a ) a ) a ) a ) a ) a ) a ) a ) a ) a ) a;A |B |C |D |E |F -----------------------------------------------------------------------0 |1 |2 |3 |4 |5 10 |11 |12 |13 |14 |15 ij> -- test predicate push throughselect * from(select a.a as a1, b.a as a2 from s a, s b) a where a.a1 = 0 and a.a2 = 10;A1 |A2 -----------------------0 |10 ij> -- push column = column throughselect * from (select a, a from s) a (x, y) where x = y;X |Y -----------------------0 |0 10 |10 ij> select * from (select a, a from s) a (x, y) where x + y = x * y;X |Y -----------------------0 |0 ij> -- return contants and expressions from derived tableselect * from (select 1 from s) a;1 -----------1 1 ij> select * from (select 1 from s) a (x) where x = 1;X -----------1 1 ij> select * from (select 1 from s a, s b where a.a = b.a) a (x);X -----------1 1 ij> select * from (select 1 from s a, s b where a.a = b.a) a (x) where x = 1;X -----------1 1 ij> select * from (select a + 1 from s) a;1 -----------1 11 ij> select * from (select a + 1 from s) a (x) where x = 1;X -----------1 ij> select * from (select a.a + 1 from s a, s b where a.a = b.a) a (x) where x = 1;X -----------1 ij> -- Bug 2767, don't flatten derived table with joincreate table tab1(tab1_c1 int, tab1_c2 int);0 rows inserted/updated/deletedij> create table tab2(tab2_c1 int, tab2_c2 int);0 rows inserted/updated/deletedij> insert into tab1 values (1, 1), (2, 2);2 rows inserted/updated/deletedij> insert into tab2 values (1, 1), (2, 2);2 rows inserted/updated/deletedij> select * from (select * from tab1, tab2) c where tab1_c1 in (1, 3);TAB1_C1 |TAB1_C2 |TAB2_C1 |TAB2_C2 -----------------------------------------------1 |1 |1 |1 1 |1 |2 |2 ij> drop table tab1;0 rows inserted/updated/deletedij> drop table tab2;0 rows inserted/updated/deletedij> drop table s;0 rows inserted/updated/deletedij> drop table t;0 rows inserted/updated/deletedij> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -