diff options
Diffstat (limited to 'mysql-test/main/brackets.result')
| -rw-r--r-- | mysql-test/main/brackets.result | 4517 |
1 files changed, 4517 insertions, 0 deletions
diff --git a/mysql-test/main/brackets.result b/mysql-test/main/brackets.result new file mode 100644 index 00000000000..dedd9a2a2bf --- /dev/null +++ b/mysql-test/main/brackets.result @@ -0,0 +1,4517 @@ +select 1 union ( select 2 union select 3); +1 +1 +2 +3 +explain extended +select 1 union ( select 2 union select 3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +4 UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +NULL UNION RESULT <union1,4> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 1 AS `1` union /* select#4 */ select `__4`.`2` AS `2` from (/* select#2 */ select 2 AS `2` union /* select#3 */ select 3 AS `3`) `__4` +select 1 union ( select 1 union select 1); +1 +1 +explain extended +select 1 union ( select 1 union select 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +4 UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +NULL UNION RESULT <union1,4> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 1 AS `1` union /* select#4 */ select `__4`.`1` AS `1` from (/* select#2 */ select 1 AS `1` union /* select#3 */ select 1 AS `1`) `__4` +select 1 union all ( select 1 union select 1); +1 +1 +1 +explain extended +select 1 union all ( select 1 union select 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +4 UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 1 AS `1` union all /* select#4 */ select `__4`.`1` AS `1` from (/* select#2 */ select 1 AS `1` union /* select#3 */ select 1 AS `1`) `__4` +select 1 union ( select 1 union all select 1); +1 +1 +explain extended +select 1 union ( select 1 union all select 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +4 UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,4> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 1 AS `1` union /* select#4 */ select `__4`.`1` AS `1` from (/* select#2 */ select 1 AS `1` union all /* select#3 */ select 1 AS `1`) `__4` +select 1 union select 1 union all select 1; +1 +1 +1 +explain extended +select 1 union select 1 union all select 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 1 AS `1` union /* select#2 */ select 1 AS `1` union all /* select#3 */ select 1 AS `1` +(select 1 as a) union (select 2) order by a; +a +1 +2 +explain extended +(select 1 as a) union (select 2) order by a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 (/* select#1 */ select 1 AS `a`) union (/* select#2 */ select 2 AS `2`) order by `a` +/* select#1 */ select 1 AS `a` union /* select#2 */ select 2 AS `2` order by `a`; +a +1 +2 +explain extended +/* select#1 */ select 1 AS `a` union /* select#2 */ select 2 AS `2` order by `a`; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 /* select#1 */ select 1 AS `a` union /* select#2 */ select 2 AS `2` order by `a` +select 1 union ( select 1 union (select 1 union (select 1 union select 1))); +1 +1 +explain extended all +select 1 union ( select 1 union (select 1 union (select 1 union select 1))); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +8 UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +7 UNION <derived3> ALL NULL NULL NULL NULL 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +6 UNION <derived4> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +5 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL +NULL UNION RESULT <union3,6> ALL NULL NULL NULL NULL NULL NULL +NULL UNION RESULT <union2,7> ALL NULL NULL NULL NULL NULL NULL +NULL UNION RESULT <union1,8> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1/0 Filter Select: select `1` AS `1` */ select 1 AS `1` union /* select#8/0 */ select `__8`.`1` AS `1` from (/* select#2/1 Filter Select: select `1` AS `1` */ select 1 AS `1` union /* select#7/1 */ select `__7`.`1` AS `1` from (/* select#3/2 Filter Select: select `1` AS `1` */ select 1 AS `1` union /* select#6/2 */ select `__6`.`1` AS `1` from (/* select#4/3 Filter Select: select `1` AS `1` */ select 1 AS `1` union /* select#5/3 */ select 1 AS `1`) `__6`) `__7`) `__8` +# +# MDEV-6341: INSERT ... SELECT UNION with parenthesis +# +create table t1 (a int, b int); +insert into t1 (select 1,1 union select 2,2); +select * from t1 order by 1; +a b +1 1 +2 2 +delete from t1; +insert into t1 select 1,1 union select 2,2; +select * from t1 order by 1; +a b +1 1 +2 2 +drop table t1; +CREATE OR REPLACE TABLE t1 AS SELECT 1 AS a UNION SELECT 2; +select * from t1 order by 1; +a +1 +2 +drop table t1; +CREATE OR REPLACE TABLE t1 AS (SELECT 1 AS a UNION SELECT 2); +select * from t1 order by 1; +a +1 +2 +drop table t1; +CREATE OR REPLACE VIEW v1 AS (SELECT 1 AS a); +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select 1 AS `a`) latin1 latin1_swedish_ci +drop view v1; +CREATE OR REPLACE VIEW v1 AS SELECT 1 AS a UNION SELECT 2; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `a` union select 2 AS `2` latin1 latin1_swedish_ci +drop view v1; +CREATE OR REPLACE VIEW v1 AS (SELECT 1 AS a UNION SELECT 2); +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `a` union select 2 AS `2` latin1 latin1_swedish_ci +drop view v1; +# +# MDEV-10028: Syntax error on ((SELECT ...) UNION (SELECT ...)) +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10); +INSERT INTO t1 VALUES (20); +INSERT INTO t1 VALUES (30); +((SELECT a FROM t1) UNION (SELECT a FROM t1)); +a +10 +20 +30 +(SELECT * FROM t1 UNION SELECT * FROM t1); +a +10 +20 +30 +((SELECT a FROM t1) LIMIT 1); +a +10 +SELECT * FROM (SELECT 1 UNION (SELECT 2 UNION SELECT 3)) t1; +1 +1 +2 +3 +DROP TABLE t1; +# +# test of several levels of ORDER BY / LIMIT +# +create table t1 (a int, b int); +insert into t1 (a,b) values (1, 100), (2, 200), (3,30), (4,4); +select a,b from t1 order by 1 limit 3; +a b +1 100 +2 200 +3 30 +(select a,b from t1 order by 1 limit 3) order by 2 limit 2; +a b +3 30 +1 100 +(select 10,1000 union select a,b from t1 order by 1 limit 3) order by 2 limit 2; +10 1000 +3 30 +1 100 +((select a,b from t1 order by 1 limit 3) order by 2 limit 2) order by 1 limit 1; +a b +1 100 +((select a,b from t1 order by 1 limit 3) order by 2 limit 2) order by 1; +a b +1 100 +3 30 +drop table t1; +# +# MDEV-16359: union with 3 selects in brackets +# +select 1 union select 1 union select 1; +1 +1 +(select 1 union select 1 union select 1); +1 +1 +((select 1) union (select 1) union (select 1)); +1 +1 +# +# MDEV-16357: union in brackets with tail +# union with tail in brackets +# +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES(1),(2),(3),(4); +CREATE TABLE t2 (a int); +INSERT INTO t2 VALUES (4),(5),(6),(7); +(SELECT a FROM t1 UNION SELECT a FROM t2) LIMIT 1; +a +1 +(SELECT a FROM t1 UNION SELECT a FROM t2) ORDER BY a DESC; +a +7 +6 +5 +4 +3 +2 +1 +(SELECT a FROM t1 UNION SELECT a FROM t2 LIMIT 1); +a +1 +DROP TABLE t1,t2; +# +# MDEV-19324: ((SELECT ...) ORDER BY col ) LIMIT n +# +create table t1 (a int); +insert into t1 values (10),(20),(30); +select a from t1 order by a desc limit 1; +a +30 +explain extended select a from t1 order by a desc limit 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using filesort +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `test`.`t1`.`a` desc limit 1 +explain format=json select a from t1 order by a desc limit 1; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "read_sorted_file": { + "filesort": { + "sort_key": "t1.a desc", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + } +} +(select a from t1 order by a desc) limit 1; +a +30 +explain extended (select a from t1 order by a desc) limit 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using filesort +Warnings: +Note 1003 (select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `test`.`t1`.`a` desc limit 1) +explain format=json (select a from t1 order by a desc) limit 1; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "read_sorted_file": { + "filesort": { + "sort_key": "t1.a desc", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + } +} +(select a from t1 where a=20 union select a from t1) order by a desc limit 1; +a +30 +explain extended (select a from t1 where a=20 union select a from t1) order by a desc limit 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 UNION t1 ALL NULL NULL NULL NULL 3 100.00 +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 20 union /* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `a` desc limit 1 +explain format=json (select a from t1 where a=20 union select a from t1) order by a desc limit 1; +EXPLAIN +{ + "query_block": { + "union_result": { + "table_name": "<union1,2>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "t1.a = 20" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + ] + } + } +} +((select a from t1 where a=20 union select a from t1) order by a desc) limit 1; +a +30 +explain extended ((select a from t1 where a=20 union select a from t1) order by a desc) limit 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 UNION t1 ALL NULL NULL NULL NULL 3 100.00 +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 20 union /* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `a` desc limit 1 +explain format=json ((select a from t1 where a=20 union select a from t1) order by a desc) limit 1; +EXPLAIN +{ + "query_block": { + "union_result": { + "table_name": "<union1,2>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "t1.a = 20" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + ] + } + } +} +drop table t1; +# +# MDEV-19363: ((SELECT ...) ORDER BY col ) LIMIT n UNION ... +# +create table t1 (pk int); +insert into t1 values (5),(4),(1),(2),(3); +((select * from t1 order by pk) limit 2) union (select * from t1 where pk > 4); +pk +1 +2 +5 +explain extended ((select * from t1 order by pk) limit 2) union (select * from t1 where pk > 4); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using filesort +2 UNION t1 ALL NULL NULL NULL NULL 5 100.00 Using where +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` order by `test`.`t1`.`pk` limit 2) union (/* select#2 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` where `test`.`t1`.`pk` > 4) +explain format=json ((select * from t1 order by pk) limit 2) union (select * from t1 where pk > 4); +EXPLAIN +{ + "query_block": { + "union_result": { + "table_name": "<union1,2>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "read_sorted_file": { + "filesort": { + "sort_key": "t1.pk", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.pk > 4" + } + } + } + ] + } + } +} +drop table t1; +# +# MDEV-18689: parenthesis around table names and derived tables +# +select * from ( mysql.db ); +Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv Delete_history_priv +% test Y Y Y Y Y Y N Y Y Y Y Y Y Y Y N N Y Y Y +% test\_% Y Y Y Y Y Y N Y Y Y Y Y Y Y Y N N Y Y Y +create table t1 (a int); +insert into t1 values (7), (2), (7); +select * from (t1); +a +7 +2 +7 +select * from ((t1)); +a +7 +2 +7 +select * from (t1 t) where t.a > 5; +a +7 +7 +select * from ((t1 t)) where t.a > 5; +a +7 +7 +select * from ((select a, sum(a) from t1 group by a) t); +a sum(a) +2 2 +7 14 +select * from (((select a, sum(a) from t1 group by a) t)); +a sum(a) +2 2 +7 14 +update (t1 t) set t.a=t.a+1; +select * from t1; +a +8 +3 +8 +drop table t1; +# +# MDEV-19956: query expressions in different contexts +# +create table t1 (a int); +insert into t1 values (3), (7), (1), (2), (4); +create table t2 (a int, b int); +insert into t2 values (3,30), (7,70), (1,10), (2,20), (4,40); +# 1. select +# 1.1. simple select +select * from t1; +a +3 +7 +1 +2 +4 +(select * from t1); +a +3 +7 +1 +2 +4 +((select * from t1)); +a +3 +7 +1 +2 +4 +# 1.2. select with tail +select * from t1 order by a; +a +1 +2 +3 +4 +7 +select a from t1 order by a; +a +1 +2 +3 +4 +7 +select a from t1 order by 1; +a +1 +2 +3 +4 +7 +select * from t1 order by t1.a; +a +1 +2 +3 +4 +7 +(select * from t1 order by t1.a); +a +1 +2 +3 +4 +7 +((select * from t1 order by t1.a)); +a +1 +2 +3 +4 +7 +(select * from t1 order by t1.a limit 2); +a +1 +2 +(select a from t1 where a=1) order by 1 desc; +a +1 +# 1.2. select with several tails +(select * from t2 order by a limit 2) order by b desc; +a b +2 20 +1 10 +(select * from t2 order by t2.a limit 2) order by b desc; +a b +2 20 +1 10 +((select * from t2 order by t2.a limit 2) order by b desc); +a b +2 20 +1 10 +(((select * from t2 order by t2.a) limit 2) order by b desc); +a b +2 20 +1 10 +# 2. union +# 2.1 simple union +select a from t1 union select a from t1; +a +3 +7 +1 +2 +4 +select a from t1 union all select a from t1; +a +3 +7 +1 +2 +4 +3 +7 +1 +2 +4 +select a from t1 union select b from t2; +a +3 +7 +1 +2 +4 +30 +70 +10 +20 +40 +(select a from t1) union (select a from t1); +a +3 +7 +1 +2 +4 +(select a from t1) union (select b from t2); +a +3 +7 +1 +2 +4 +30 +70 +10 +20 +40 +select a from t1 where a=1 union select a from t1 where a=3; +a +1 +3 +(select a from t1 where a=1) union select a from t1 where a=3; +a +1 +3 +((select a from t1 where a=1) union select a from t1 where a=3); +a +1 +3 +((select a from t1 where a<=3) union (select a from t1 where a=3)); +a +3 +1 +2 +select a from t1 where a=1 union (select a from t1 where a=3); +a +1 +3 +(select a from t1 where a=1 union (select a from t1 where a=3)); +a +1 +3 +((select a from t1 where a=1 union (select a from t1 where a=3))); +a +1 +3 +select a from t1 where a=1 +union +select a from t1 where a=3 +union +select a from t1 where a=7; +a +1 +3 +7 +( select a from t1 where a=1 +union +select a from t1 where a=3 +union +select a from t1 where a=7 ); +a +1 +3 +7 +(select a from t1 where a=1 order by a) union select a from t1 where a=3; +a +1 +3 +(select a from t1 where a!=3 order by a desc) union select a from t1 where a=3; +a +7 +1 +2 +4 +3 +((select a from t1 where a=1 order by a) union select a from t1 where a=3); +a +1 +3 +(select a from t1 where a!=3 order by a desc) union select a from t1 where a=3; +a +7 +1 +2 +4 +3 +( ( select a from t1 where a!=3 order by a desc limit 3) +union +select a from t1 where a=3 ); +a +7 +4 +2 +3 +( select a from t1 where a <=3 except select a from t1 where a >=3 ) +union +select a from t1 where a=7; +a +1 +2 +7 +( ( select a from t1 where a <=3 +except +select a from t1 where a >=3 ) +union +select a from t1 where a=7 ); +a +1 +2 +7 +( select a from t1 where a <=3 +except +( select a from t1 where a >=3 +union +select a from t1 where a=7 ) ); +a +1 +2 +( ( select a from t1 where a <=3 ) +except +( select a from t1 where a >=3 +union +select a from t1 where a=7 ) ); +a +1 +2 +# 2.2. union with tail +select a from t1 where a=1 union select a from t1 where a=3 order by a desc; +a +3 +1 +(select a from t1 limit 2) union select a from t1 where a=3 order by a desc; +a +7 +3 +select a from t1 where a=4 union (select a from t1 where a <=4 limit 2) +order by a desc; +a +4 +3 +1 +select a from t1 where a=4 +union +(select a from t1 where a <=4 order by a limit 2) +order by a desc; +a +4 +2 +1 +( select a from t1 where a=4 +union +( select a from t1 where a <=4 order by a limit 2 ) ) +order by a desc; +a +4 +2 +1 +( select a from t1 where a <=3 except select a from t1 where a >=3 ) +union +select a from t1 where a=7 order by a desc; +a +7 +2 +1 +( select a from t1 where a!=3 order by a desc ) +union +select a from t1 where a=3 +order by a desc; +a +7 +4 +3 +2 +1 +(select a from t1 where a=1) +union +(select a from t1 where a=3) +order by a desc; +a +3 +1 +( select a from t1 where a=1 +union +select a from t1 where a=3 ) +order by a desc; +a +3 +1 +( ( select a from t1 where a=1 ) +union +( select a from t1 where a=3 ) ) +order by a desc; +a +3 +1 +( select a from t1 where a=1 +union +select a from t1 where a=3 ) +order by 1 desc; +a +3 +1 +((select a from t1 where a=1 union select a from t1 where a=3)) order by 1 desc; +a +3 +1 +(((select a from t1 where a=1) union (select a from t1 where a=3))) +order by 1 desc; +a +3 +1 +( (select a from t1 where a=1 ) +union +(select a from t1 where a=3) ) +order by 1 desc; +a +3 +1 +# 2.3. complex union +select a from t1 where a=1 +union +select a from t1 where a=3 +union +select a from t1 where a=2 +union +select a from t1 where a=4; +a +1 +3 +2 +4 +( select a from t1 where a=1 +union +select a from t1 where a=3 +union +select a from t1 where a=2 ) +union +select a from t1 where a=4; +a +1 +3 +2 +4 +(select a from t1 where a=1 union select a from t1 where a=3) +union +(select a from t1 where a=2 union select a from t1 where a=4); +a +1 +3 +2 +4 +(select a from t1 where a=1 union (select a from t1 where a=3)) +union +((select a from t1 where a=2) union select a from t1 where a=4); +a +1 +3 +2 +4 +( ( select a from t1 where a=1) +union +select a from t1 where a=3 ) +union +select a from t1 where a=2 +union +select a from t1 where a=4; +a +1 +3 +2 +4 +( ( ( select a from t1 where a=1) +union +select a from t1 where a=3 ) +union +select a from t1 where a=2 ) +union +select a from t1 where a=4; +a +1 +3 +2 +4 +select a from t1 where a=1 +union +select a from t1 where a=3 +union +select a from t1 where a=2 +union +(select a from t1 where a=4); +a +1 +3 +2 +4 +select a from t1 where a=1 +union +select a from t1 where a=3 +union +( select a from t1 where a=2 +union +( select a from t1 where a=4 ) ); +a +1 +3 +2 +4 +select a from t1 where a=1 +union +( select a from t1 where a=3 +union +( select a from t1 where a=2 +union +( select a from t1 where a=4 ) ) ); +a +1 +3 +2 +4 +# 2.4. complex union with tail +( ( select a from t1 where a=1 union select a from t1 where a=3 ) +order by a desc ) +union +( ( select a from t1 where a=2 union select a from t1 where a=4 ) +order by a desc ); +a +3 +1 +4 +2 +( ( select a from t1 where a=1 union select a from t1 where a=3 ) +order by a desc ) +union +( ( select a from t1 where a=2 union select a from t1 where a=4 ) +order by a desc ) +order by a; +a +1 +2 +3 +4 +( select a from t1 where a=1 +union +select a from t1 where a=3 +union +select a from t1 where a=2 order by a desc limit 2 ) +union +select a from t1 where a=4 +order by a; +a +2 +3 +4 +( select a from t1 where a=1 +union +select a from t1 where a=3 order by a desc ) +union +select a from t1 where a=2 order by a desc limit 2; +a +3 +2 +( ( select a from t1 where a >= 2 +union +select a from t1 where a=1 order by a desc limit 2 ) +union +select a from t1 where a=3 order by a limit 2 ) +union +select a from t1 where a=1; +a +3 +4 +1 +# 3. TVC +# 3.1. simple TVC +values (3), (7), (1); +3 +3 +7 +1 +(values (3), (7), (1)); +3 +3 +7 +1 +((values (3), (7), (1))); +3 +3 +7 +1 +# 3.2. simple TVC with tail(s) +values (3), (7), (1) order by 1; +3 +1 +3 +7 +(values (3), (7), (1)) order by 1; +3 +1 +3 +7 +((values (3), (7), (1))) order by 1; +3 +1 +3 +7 +(((values (3), (7), (1))) order by 1); +3 +1 +3 +7 +(values (3), (7), (1) limit 2) order by 1 desc; +3 +7 +3 +((values (3), (7), (1)) order by 1 desc) limit 2; +3 +7 +3 +(((values (3), (7), (1)) order by 1 desc) limit 2); +3 +7 +3 +# 3.3. union of TVCs +values (3), (7), (1) union values (3), (4), (2); +3 +3 +7 +1 +4 +2 +values (3), (7), (1) union all values (3), (4), (2); +3 +3 +7 +1 +3 +4 +2 +values (3), (7), (1) union values (3), (4), (2); +3 +3 +7 +1 +4 +2 +values (3), (7), (1) except values (3), (4), (2); +3 +7 +1 +(values (3), (7), (1)) union (values (3), (4), (2)); +3 +3 +7 +1 +4 +2 +(values (3), (7), (1)) union (values (3), (4), (2)) union values (5), (7); +3 +3 +7 +1 +4 +2 +5 +(values (3), (7), (1)) union (values (3), (4), (2)) union (values (5), (7)); +3 +3 +7 +1 +4 +2 +5 +(values (3), (7), (1) union values (3), (4), (2)) union values (5), (7); +3 +3 +7 +1 +4 +2 +5 +values (3), (7), (1) union (values (3), (4), (2) union values (5), (7)); +3 +3 +7 +1 +4 +2 +5 +(values (3), (7), (1) union ((values (3), (4), (2) union values (5), (7)))); +3 +3 +7 +1 +4 +2 +5 +# 3.4. tailed union of TVCs +values (3), (7), (1) union values (3), (4), (2) order by 1; +3 +1 +2 +3 +4 +7 +(values (3), (7), (1) union values (3), (4), (2)) order by 1; +3 +1 +2 +3 +4 +7 +(values (3), (7), (1) union values (3), (4), (2)) order by 1; +3 +1 +2 +3 +4 +7 +values (3), (7), (1) union (values (3), (4), (2)) order by 1; +3 +1 +2 +3 +4 +7 +(values (3), (7), (1) union values (3), (4), (2)) order by 1; +3 +1 +2 +3 +4 +7 +((values (3), (7), (1)) union values (3), (4), (2)) order by 1; +3 +1 +2 +3 +4 +7 +# 3.5. union of tailed TVCs +(values (3), (7), (1) order by 1 limit 2) +union +(values (3), (4), (2) order by 1 desc limit 2); +3 +1 +3 +4 +((values (3), (7), (1) order by 1) limit 2) +union +((values (3), (4), (2) order by 1 desc) limit 2); +3 +1 +3 +4 +(((values (3), (7), (1)) order by 1) limit 2) +union +(((values (3), (4), (2)) order by 1 desc) limit 2); +3 +1 +3 +4 +# 3.6. tailed union of tailed TVCs +(values (3), (7), (1) order by 1 limit 2) +union +values (3), (4), (2) +order by 1; +3 +1 +2 +3 +4 +((values (3), (7), (1)) order by 1 limit 2) +union +((values (3), (4), (2) order by 1 desc) limit 2) +order by 1; +3 +1 +3 +4 +# 3.7 [tailed] union of [tailed] select and [tailed] TVC +(select a from t1 where a <=3 order by 1 limit 2) +union +(values (3), (4), (2) order by 1 desc limit 2); +a +1 +2 +4 +3 +((select a from t1 where a <=3) order by 1 limit 2) +union +(values (3), (4), (2) order by 1 desc limit 2); +a +1 +2 +4 +3 +(((select a from t1 where a <=3) order by a) limit 2) +union +(((values (3), (4), (2)) order by 1 desc) limit 2); +a +1 +2 +4 +3 +( (((select a from t1 where a <=3) order by a) limit 2) +union +(((values (3), (4), (2)) order by 1 desc) limit 2) ); +a +1 +2 +4 +3 +(select a from t1 where a <=3 order by 1 limit 2) +union +(values (3), (4), (2) order by 1 desc limit 2) +order by a; +a +1 +2 +3 +4 +((select a from t1 where a <=3) order by 1 limit 2) +union +(values (3), (4), (2) order by 1 desc limit 2) +order by a; +a +1 +2 +3 +4 +(((select a from t1 where a <=3) order by a) limit 2) +union +(((values (3), (4), (2)) order by 1 desc) limit 2) +order by a; +a +1 +2 +3 +4 +(((values (3), (4), (2)) order by 1 desc) limit 2); +3 +4 +3 +( (((select a from t1 where a <=3) order by a) limit 2) +union +(((values (3), (4), (2)) order by 1 desc) limit 2) ) +order by a; +a +1 +2 +3 +4 +(values (3), (4), (2) order by 1 desc limit 2) +union +(select a from t1 where a <=3 order by 1 limit 2); +3 +4 +3 +1 +2 +(values (3), (4), (2) order by 1 desc limit 2) +union +((select a from t1 where a <=3) order by 1 limit 2); +3 +4 +3 +1 +2 +(((values (3), (4), (2)) order by 1 desc) limit 2) +union +(((select a from t1 where a <=3) order by 1) limit 2); +3 +4 +3 +1 +2 +(((values (3), (4), (2)) order by 1 desc) limit 2) +union +(((select a from t1 where a <=3) order by a) limit 2) +order by 1; +3 +1 +2 +3 +4 +( select a from t1 where a=1 +union +values (3), (4), (2) order by 1 desc ) +union +select a from t1 where a=2 order by a desc limit 3; +a +4 +3 +2 +4. CTE +4.1. simple select with simple CTE +with t as (select * from t1 where a <=3) +select * from t; +a +3 +1 +2 +with t as (select * from t1 where a <=3) +(select * from t); +a +3 +1 +2 +with t as (select * from t1 where a <=3) +((select * from t)); +a +3 +1 +2 +with t as ((select * from t1 where a <=3)) +select * from t; +a +3 +1 +2 +with t as (((select * from t1 where a <=3))) +select * from t; +a +3 +1 +2 +4.2. tailed select with simple CTE +with t as (select * from t1 where a <=3) +select * from t order by a; +a +1 +2 +3 +with t as (select * from t1 where a <=3) +(select * from t) order by a; +a +1 +2 +3 +with t as (select * from t1 where a <=3) +(select * from t) order by a desc limit 2; +a +3 +2 +4.3. [tailed] select with tailed CTE +with t as (select * from t1 where a >=2 order by a limit 2) +select * from t; +a +2 +3 +with t as (((select * from t1 where a >=2) order by a desc) limit 2) +select * from t; +a +7 +4 +with t as (select * from t1 where a >=2 order by a desc limit 2) +select * from t order by a; +a +4 +7 +4.4. [tailed] union with CTE +with t as (select * from t1 where a <=3) +select a from t1 where a=1 union select a from t where a=3; +a +1 +3 +with t as (select * from t1 where a <=3) +(select a from t) union (select b from t2); +a +3 +1 +2 +30 +70 +10 +20 +40 +with t as (select * from t1 where a <=3) +(select a from t) union (select b as a from t2) order by a desc; +a +70 +40 +30 +20 +10 +3 +2 +1 +4.5. [tailed] union with [tailed] union in CTE +with t as (select * from t1 where a < 3 union select * from t1 where a > 3) +select a from t1 where a=1 union select a from t where a=7; +a +1 +7 +with t as +( select * from t1 where a < 3 +union +select * from t1 where a > 3 +order by a desc limit 3 ) +select a from t1 where a=4 union select a from t where a=7; +a +4 +7 +with t as +( select * from t1 where a < 3 +union +select * from t1 where a > 3 +order by a desc limit 3 ) +select a from t1 where a=4 union select a from t where a=7 order by a desc; +a +7 +4 +with t as +( (select * from t1 where a < 3) +union +(select * from t1 where a > 3) +order by a desc limit 3 ) +select a from t1 where a=4 union select a from t where a=7 order by a desc; +a +7 +4 +with t as +( (select * from t1 where a < 3) +union +(select * from t1 where a > 3) +order by a desc limit 3 ) +(select a from t1 where a=4 union select a from t where a=7 order by a desc); +a +7 +4 +with t as +( (select * from t1 where a < 3) +union +(select * from t1 where a > 3) +order by a desc limit 3 ) +((select a from t1 where a=4 union select a from t where a=7) order by a desc); +a +7 +4 +with t as +( select * from t1 where a < 3 +union +values (4), (7) +order by a desc limit 3 ) +select a from t1 where a=4 union select a from t where a=7 order by a desc; +a +7 +4 +4.6. [tailed] union with [tailed] union of TVC in CTE +with t(a) as +( values (2), (1) +union +(values (4), (7)) +order by 1 desc limit 3 ) +select a from t1 where a=4 union select a from t where a=7 order by a desc; +a +7 +4 +with t(a) as +( (values (2), (1)) +union +(values (4), (7) order by 1 desc) +order by 1 desc limit 3 ) +select a from t1 where a=1 union select a from t where a=7 order by a desc; +a +7 +1 +with t(a) as +( (values (2), (1)) +union +(values (4), (7) order by 1 desc) +order by 1 limit 3 ) +select a from t where a=1 union values (7) order by a desc; +a +7 +1 +with t(a) as +( (values (2), (1)) +union +(values (4), (7) order by 1 desc ) ) +select a from t where a=1 union select 7 order by a desc; +a +7 +1 +4.5. [tailed] union with two CTEs +with t as (select * from t1 where a < 3), +s as (select * from t1 where a > 3) +select a from t where a=1 union select a from s where a=7 order by a desc; +a +7 +1 +with t as (select * from t1 where a < 3), +s as (select * from t1 where a > 3) +(select a from t where a=1 union select a from s where a=7 order by a desc); +a +7 +1 +with t as (select * from t1 where a < 3), +s as (select * from t1 where a > 3) +(select a from t where a=1 union select a from s where a=7) order by a desc; +a +7 +1 +with t as (select * from t1 where a < 3), +s as (select * from t where a > 3) +select a from t where a=1 union select a from s where a=7 order by a desc; +a +1 +# 5. single-row subquery in expression +# 5.1. [tailed] simple select in expression +select (a+1) + b as r from t2; +r +34 +78 +12 +23 +45 +select ((a+1) + b) as r from t2; +r +34 +78 +12 +23 +45 +select (b + (select 1)) as r from t2; +r +31 +71 +11 +21 +41 +select (select a from t1 where a <=3 order by a desc limit 1) as r from t2; +r +3 +3 +3 +3 +3 +select +(select a from t1 where a <=3 order by a desc limit 1) as r from t2; +r +3 +3 +3 +3 +3 +select (select 100) as r from t2; +r +100 +100 +100 +100 +100 +select ((select 100)) as r from t2; +r +100 +100 +100 +100 +100 +select (select 100) + t2.b as r from t2; +r +130 +170 +110 +120 +140 +select ((select 100) + t2.b) as r from t2; +r +130 +170 +110 +120 +140 +# 5.2. [tailed] TVC in expression +select (values (200)) as r from t2; +r +200 +200 +200 +200 +200 +select ((values (200))) as r from t2; +r +200 +200 +200 +200 +200 +select (values (200)) + t2.b as r from t2; +r +230 +270 +210 +220 +240 +select ((values (200)) + t2.b) as r from t2; +r +230 +270 +210 +220 +240 +select (values (200), (300) order by 1 desc limit 1) as r from t2; +r +300 +300 +300 +300 +300 +select ((values (200), (300)) order by 1 desc limit 1) as r from t2; +r +300 +300 +300 +300 +300 +select (select * from t1 limit 1) as r from t2; +r +3 +3 +3 +3 +3 +select (select * from t1 order by a limit 1) as r from t2; +r +1 +1 +1 +1 +1 +select ((select * from t1 order by a limit 1)) as r from t2; +r +1 +1 +1 +1 +1 +((select ((select * from t1 order by a limit 1)) as r from t2)); +r +1 +1 +1 +1 +1 +select (select * from t1 order by a limit 1) + t2.b as r from t2; +r +31 +71 +11 +21 +41 +# 5.3. [tailed] union in expression +select +( select a from t1 where a<3 union select a from t1 where a>4 +order by a desc limit 1 ) as r +from t1; +r +7 +7 +7 +7 +7 +select +( (select a from t1 where a<3) union (select a from t1 where a>4) +order by a desc limit 1 ) as r +from t1; +r +7 +7 +7 +7 +7 +select +( select a from t1 where a<3 union select a from t1 where a>4 +order by a desc limit 1 ) + t1.a as r +from t1; +r +10 +14 +8 +9 +11 +select +t1.a + +( select a from t1 where a<3 union select a from t1 where a>4 +order by a desc limit 1 ) as r +from t1; +r +10 +14 +8 +9 +11 +select +( (select a from t1 where a<3 union select a from t1 where a>4 +order by a desc limit 1 ) + t1.a) as r +from t1; +r +10 +14 +8 +9 +11 +select +( ( (select a from t1 where a<3) union (select a from t1 where a>4) +order by a desc limit 1 ) + t1.a ) as r +from t1; +r +10 +14 +8 +9 +11 +# 5.4. [tailed] select with simple CTE in expression +select +( with t as (select * from t1 where a <=3) +select a from t limit 1) as r +from t2; +r +3 +3 +3 +3 +3 +select +( with t as (select * from t1 where a <=3) +select a from t limit 1) + t2.b as r +from t2; +r +33 +73 +13 +23 +43 +select +t2.b +( with t as (select * from t1 where a <=3) +select a from t limit 1) as r +from t2; +r +33 +73 +13 +23 +43 +select +((( with t as (select * from t1 where a <=3) +select a from t limit 1) + t2.b)) as r +from t2; +r +33 +73 +13 +23 +43 +select +( with t as (select * from t1 where a <=3) +select a from t limit 1) + 100 as r +from t2; +r +103 +103 +103 +103 +103 +select +( with t as (select * from t1 where a <=3) +select a from t limit 1) + (select 100) as r +from t2; +r +103 +103 +103 +103 +103 +select +( with t as (select * from t1 where a <=3) +select a from t limit 1) + t2.b + (select 100) as r +from t2; +r +133 +173 +113 +123 +143 +select +( with t as (select * from t1 where a <=3) +select a from t limit 1 ) + (t2.b + (select 100)) as r +from t2; +r +133 +173 +113 +123 +143 +select +( with t as (select * from t1 where a <=3) +select a from t limit 1 ) + t2.b + (values (100)) as r +from t2; +r +133 +173 +113 +123 +143 +# 5.5. [tailed] union with simple CTE in expression +select +( with t as (select * from t1 where a <=3) +select a from t union select b from t2 order by a desc limit 1) as r +from t2; +r +70 +70 +70 +70 +70 +select +( with t as (select * from t1 where a <=3) +(select a from t) union (select b from t2) order by a desc limit 1) as r +from t2; +r +70 +70 +70 +70 +70 +select +( with t as (select * from t1 where a <=3) +(select a from t) union (select b from t2) order by a desc limit 1) as r +from t2; +r +70 +70 +70 +70 +70 +select +( ( with t as (select * from t1 where a <=3) +(select a from t) union (select b from t2) order by a desc limit 1) + +t2.a ) as r +from t2; +r +73 +77 +71 +72 +74 +# 5.6. [tailed] union with CTE with union in expression +select +( with t as +( select * from t1 where a < 3 +union +select * from t1 where a > 3 +order by a desc limit 3 ) +select a from t1 where a=4 union select a from t where a=7 limit 1) as r +from t2; +r +4 +4 +4 +4 +4 +select +( with t as +( select * from t1 where a < 3 +union +select * from t1 where a > 3 +order by a desc limit 3 ) +select a from t1 where a=4 union select a from t where a=7 limit 1) + +t2. b as r +from t2; +r +34 +74 +14 +24 +44 +# 5.7. [tailed] union of TVCs with CTE with union in expression +select +( with t(a) as +( (values (2), (1)) +union +(values (4), (7) order by 1 limit 1) +order by 1 desc limit 3 ) select * from t limit 1 ) + t2.b as r +from t2; +r +34 +74 +14 +24 +44 +select +( with t(a) as +( select 2 union select 1 +union +(values (4), (7) order by 1 limit 1) +order by 1 limit 3 ) select * from t limit 1 ) + t2.b as r +from t2; +r +31 +71 +11 +21 +41 +# 6. subquery +# 6.1. TVC in IN subquery +select a from t1 where a in (1,8,7); +a +7 +1 +select a from t1 where a in (values (1), (8), (7)); +a +7 +1 +# 6.2. simple select in IN subquery +select a from t1 where a in (select a from t2 where a <= 3); +a +3 +1 +2 +select a from t1 where a in ((select a from t2 where a <= 3)); +a +3 +1 +2 +# 6.3. union in IN subquery +select a from t1 +where a in (select a from t1 where a<=2 union select a from t2 where b>40); +a +7 +1 +2 +select a from t1 +where a in (select a from t1 where a<=2 union (select a from t2 where b>40)); +a +7 +1 +2 +select a from t1 +where a in ((select a from t1 where a<=2) union select a from t2 where b>40); +a +7 +1 +2 +select a from t1 +where a in ((select a from t1 where a<=2) union (select a from t2 where b>40)); +a +7 +1 +2 +# 6.4. select with CTE and union in IN subquery +with t as (select a from t1 where a<=2) +select a from t1 +where a in ((select a from t) union (select a from t2 where b>40)); +a +7 +1 +2 +with t as ((select a from t1 where a<=2)) +select a from t1 +where a in ((select a from t) union (select a from t2 where b>40)); +a +7 +1 +2 +with t as ((select a from t1 where a<=2) order by a desc limit 1) +select a from t1 +where a in ((select a from t) union (select a from t2 where b>40)); +a +7 +2 +# 6.5. NOT IN subquery +select a from t1 where a not in (1,8,7); +a +3 +2 +4 +select a from t1 where a not in (values (1), (8), (7)); +a +3 +2 +4 +select a from t1 where a not in (select a from t2 where a <= 3); +a +7 +4 +select a from t1 where a not in ((select a from t2 where a <= 3)); +a +7 +4 +select a from t1 +where a not in (select a from t1 where a<=2 +union +select a from t2 where b>40); +a +3 +4 +select a from t1 +where a not in (select a from t1 where a<=2 +union +(select a from t2 where b>40)); +a +3 +4 +select a from t1 +where a not in ((select a from t1 where a<=2) +union +select a from t2 where b>40); +a +3 +4 +select a from t1 +where a not in ((select a from t1 where a<=2) +union +(select a from t2 where b>40)); +a +3 +4 +with t as ((select a from t1 where a<=2) order by a desc limit 1) +select a from t1 +where a not in ((select a from t) union (select a from t2 where b>40)); +a +3 +1 +4 +# 6.6. IN subquery in expression +select 1 in (select a from t1) as r, b from t2 where b > 30; +r b +1 70 +1 40 +select (1 in (select a from t1)) as r, b from t2 where b > 30; +r b +1 70 +1 40 +select 1 in ((select a from t1)) as r, b from t2 where b > 30; +r b +1 70 +1 40 +select ((1 in ((select a from t1)))) as r, b from t2 where b > 30; +r b +1 70 +1 40 +select ((1 in ((select a from t1)))) as r, b from t2 where b > 30; +r b +1 70 +1 40 +select b, if (a in (select a from t1 where a > 3),10,20) as r from t2; +b r +30 20 +70 10 +10 20 +20 20 +40 10 +select b, if (a in ((select a from t1 where a > 3)),10,20) as r from t2; +b r +30 20 +70 10 +10 20 +20 20 +40 10 +# 6.7. IN subquery in SF and SP +create function f1(x int) returns int +return (x in ((select a from t1 where a <= 4))); +select b, f1(a) from t2 where b > 20; +b f1(a) +30 1 +70 0 +40 1 +drop function f1; +create function f2(x int) returns int +if x in ((select a from t1 where a <= 4)) +then return 100; +else return 200; +end if | +select b, f2(a) from t2 where b > 20; +b f2(a) +30 100 +70 200 +40 100 +drop function f2; +# 6.8. EXISTS subquery +select exists (select a from t1 where t1.a=t2.a) as r, b from t2 where b > 30; +r b +1 70 +1 40 +select exists ((select a from t1 where t1.a=t2.a)) as r, b from t2 where b > 30; +r b +1 70 +1 40 +with s as +( (select * from t1 where a <=4 order by 1 desc limit 2) +union +values (3), (8), (7) ) +select * from t2 where exists ((select * from s where s.a=t2.a)); +a b +3 30 +7 70 +4 40 +with t as ((select a from t1 where a<=2) order by a desc limit 1) +select a from t2 +where not exists ((select a from t where t.a=t2.a) +except +(select a from t where a>40)); +a +3 +7 +1 +4 +# 6.9. EXISTS subquery with SF and SP +create function f1(x int) returns int +return exists (((select * from t1 where x=a and a <= 4))); +select b, f1(a) from t2 where b > 20; +b f1(a) +30 1 +70 0 +40 1 +drop function f1; +create function f2(x int) returns int +if not exists (((select * from t1 where x=a and a <= 4))) +then return 100; +else return 200; +end if | +select b, f2(a) from t2 where b > 20; +b f2(a) +30 200 +70 100 +40 200 +drop function f2; +# 6.10. subquery with ANY +select a from t1 where a = any(select a from t2 where a <= 3); +a +3 +1 +2 +select a from t1 where a = any((select a from t2 where a <= 3)); +a +3 +1 +2 +select a from t1 +where a = any (select a from t1 where a<=2 +union +select a from t2 where b>40); +a +7 +1 +2 +select a from t1 +where a = any(select a from t1 where a<=2 +union +(select a from t2 where b>40)); +a +7 +1 +2 +select a from t1 +where a = any((select a from t1 where a<=2) +union +select a from t2 where b>40); +a +7 +1 +2 +select a from t1 +where a = any((select a from t1 where a<=2) +union +(select a from t2 where b>40)); +a +7 +1 +2 +# 7. create table as +# 7.1. create table as simple select +create table t as select * from t1 where a <=3; +select * from t; +a +3 +1 +2 +drop table t; +create table t select * from t1 where a <=3; +select * from t; +a +3 +1 +2 +drop table t; +create table t as (select * from t1 where a <=3); +select * from t; +a +3 +1 +2 +drop table t; +create table t (select * from t1 where a <=3); +select * from t; +a +3 +1 +2 +drop table t; +create table t as ((select * from t1 where a <=3)); +select * from t; +a +3 +1 +2 +drop table t; +create table t ((select * from t1 where a <=3)); +select * from t; +a +3 +1 +2 +drop table t; +create table t(a decimal(10,2)) as select * from t1 where a <=3; +select * from t; +a +3.00 +1.00 +2.00 +drop table t; +create table t(a decimal(10,2)) select * from t1 where a <=3; +select * from t; +a +3.00 +1.00 +2.00 +drop table t; +create table t(a decimal(10,2)) as (select * from t1 where a <=3); +select * from t; +a +3.00 +1.00 +2.00 +drop table t; +create table t(a decimal(10,2)) (select * from t1 where a <=3); +select * from t; +a +3.00 +1.00 +2.00 +drop table t; +create table t(a decimal(10,2)) as ((select * from t1 where a <=3)); +select * from t; +a +3.00 +1.00 +2.00 +drop table t; +create table t(a decimal(10,2)) ((select * from t1 where a <=3)); +select * from t; +a +3.00 +1.00 +2.00 +drop table t; +create table t(a decimal(10,2), b int) as +((select a, a as b from t1 where a <=3)); +select * from t; +a b +3.00 3 +1.00 1 +2.00 2 +drop table t; +create table t(a decimal(10,2), b int) +((select a, a as b from t1 where a <=3)); +select * from t; +a b +3.00 3 +1.00 1 +2.00 2 +drop table t; +# 7.2. create table as tailed select +create table t as select * from t1 where a <=3 order by 1; +select * from t; +a +1 +2 +3 +drop table t; +create table t select * from t1 where a <=3 order by 1; +select * from t; +a +1 +2 +3 +drop table t; +create table t as select * from t1 where a <=3 order by 1 desc limit 2; +select * from t; +a +3 +2 +drop table t; +create table t select * from t1 where a <=3 order by 1 desc limit 2; +select * from t; +a +3 +2 +drop table t; +create table t as ((select * from t1 where a <=3) order by 1 desc) limit 2; +select * from t; +a +3 +2 +drop table t; +create table t ((select * from t1 where a <=3) order by 1 desc) limit 2; +select * from t; +a +3 +2 +drop table t; +# 7.3. create table as select wihout from clause +create table t as select 10; +select * from t; +10 +10 +drop table t; +create table t select 10; +select * from t; +10 +10 +drop table t; +# 7.4. create table as union of selects wihout from clause +create table t as select 10 union select 70; +select * from t; +10 +10 +70 +drop table t; +create table t select 10 union select 70; +select * from t; +10 +10 +70 +drop table t; +# 7.5. create table as TVC +create table t as values (7), (3), (8); +select * from t; +7 +7 +3 +8 +drop table t; +create table t values (7), (3), (8); +select * from t; +7 +7 +3 +8 +drop table t; +create table t as (values (7), (3), (8)); +select * from t; +7 +7 +3 +8 +drop table t; +create table t (values (7), (3), (8)); +select * from t; +7 +7 +3 +8 +drop table t; +create table t as ((values (7), (3), (8))); +select * from t; +7 +7 +3 +8 +drop table t; +create table t ((values (7), (3), (8))); +select * from t; +7 +7 +3 +8 +drop table t; +# 7.6. create table as select with CTE +create table t as +with s(a) as (select * from t1 where a <=3 order by 1 desc limit 2) +select * from s; +select * from t; +a +3 +2 +drop table t; +create table t +with s(a) as (select * from t1 where a <=3 order by 1 desc limit 2) +select * from s; +select * from t; +a +3 +2 +drop table t; +create table t as +with s as +( (select * from t1 where a <=4 order by 1 desc limit 2) +union +values (3), (8), (7) ) +select * from s; +select * from t; +a +4 +3 +8 +7 +drop table t; +create table t +with s as +( (select * from t1 where a <=4 order by 1 desc limit 2) +union +values (3), (8), (7) ) +select * from s; +select * from t; +a +4 +3 +8 +7 +drop table t; +create table t as +with s(a) as (select * from t1 where a <=3 order by 1 desc limit 2) +select * from s; +select * from t; +a +3 +2 +drop table t; +# 7.7. create table as union with CTE +create table t as +with s as +( (select * from t1 where a <=4 order by 1 desc limit 2) +union +values (3), (8), (7) ) +select * from s where a>=7 union select a from t2 where b<40; +select * from t; +a +8 +7 +3 +1 +2 +drop table t; +create table t +with s as +( (select * from t1 where a <=4 order by 1 desc limit 2) +union +values (3), (8), (7) ) +select * from s where a>=7 union select a from t2 where b<40; +select * from t; +a +8 +7 +3 +1 +2 +drop table t; +create table t +with s as +( (select * from t1 where a <=4 order by 1 desc limit 2) +union +values (3), (8), (7) ) +select * from s where a>=7 union select a from t2 where b<40; +select * from t; +a +8 +7 +3 +1 +2 +drop table t; +create table t as +with s as +( ( (select * from t1 where a <=4 order by 1 desc limit 2) +union +values (3), (8), (7) ) ) +select * from s where a>=7 union select a from t2 where b<40; +select * from t; +a +8 +7 +3 +1 +2 +drop table t; +create table t +with s as +( ( (select * from t1 where a <=4 order by 1 desc limit 2) +union +values (3), (8), (7) ) ) +select * from s where a>=7 union select a from t2 where b<40; +select * from t; +a +8 +7 +3 +1 +2 +drop table t; +create table t as +with s as +( (select * from t1 where a <=4 order by 1 desc limit 2) +union +values (3), (8), (7) ) +select * from s where a>=7 union select a from s where a<4; +select * from t; +a +8 +7 +3 +drop table t; +create table t +with s as +( (select * from t1 where a <=4 order by 1 desc limit 2) +union +values (3), (8), (7) ) +select * from s where a>=7 union select a from s where a<4; +select * from t; +a +8 +7 +3 +drop table t; +create table t as +with s as +( select * from t1 where a <=4 or a=7 ) +select * from s where a>=7 union select a from s where a<3; +select * from t; +a +7 +1 +2 +drop table t; +create table t +with s as +(select * from t1 where a <=4 or a=7) +select * from s where a>=7 union select a from s where a<3; +select * from t; +a +7 +1 +2 +drop table t; +create table t (a int) +with s as +( select * from t1 where a <=4 or a=7 ) +select * from s where a>=7 union select a from s where a<3; +select * from t; +a +7 +1 +2 +drop table t; +create table t (a int) +with s as +(select * from t1 where a <=4 or a=7) +select * from s where a>=7 union select a from s where a<3; +select * from t; +a +7 +1 +2 +drop table t; +create table t +with s as +( select * from t1 where a <=4 or a=7 ) +select * from s where a>=7 union select a from s where a<3 +order by a desc limit 2; +select * from t; +a +7 +2 +drop table t; +create table t +( with s as +( select * from t1 where a <=4 or a=7 ) +select * from s where a>=7 union select a from s where a<3 +order by a desc limit 2 ); +select * from t; +a +7 +2 +drop table t; +# 8. insert +create table t (c int, d int); +# 8.1. insert simple select +insert into t select * from t2 where a <=3; +select * from t; +c d +3 30 +1 10 +2 20 +delete from t; +insert into t(c) select t2.a from t2 where a <=3; +select * from t; +c d +3 NULL +1 NULL +2 NULL +delete from t; +insert into t (select * from t2 where a <=3); +select * from t; +c d +3 30 +1 10 +2 20 +delete from t; +insert into t(c) (select t2.a from t2 where a <=3); +select * from t; +c d +3 NULL +1 NULL +2 NULL +delete from t; +insert into t ((select * from t2 where a <=3)); +select * from t; +c d +3 30 +1 10 +2 20 +delete from t; +insert into t(c) ((select t2.a from t2 where a <=3)); +select * from t; +c d +3 NULL +1 NULL +2 NULL +delete from t; +drop table t; +create table t(c decimal(10,2)); +insert into t select * from t1 where a <=3; +select * from t; +c +3.00 +1.00 +2.00 +delete from t; +insert into t(c) select * from t1 where a <=3; +select * from t; +c +3.00 +1.00 +2.00 +delete from t; +insert into t (select * from t1 where a <=3); +select * from t; +c +3.00 +1.00 +2.00 +delete from t; +insert into t(c) (select * from t1 where a <=3); +select * from t; +c +3.00 +1.00 +2.00 +delete from t; +insert into t ((select * from t1 where a <=3)); +select * from t; +c +3.00 +1.00 +2.00 +delete from t; +insert into t(c) ((select * from t1 where a <=3)); +select * from t; +c +3.00 +1.00 +2.00 +delete from t; +drop table t; +create table t(a decimal(10,2), b int); +insert into t ((select * from t2 where a <=3)); +select * from t; +a b +3.00 30 +1.00 10 +2.00 20 +delete from t; +insert into t(a) ((select a from t2 where a <=3)); +select * from t; +a b +3.00 NULL +1.00 NULL +2.00 NULL +delete from t; +drop table t; +create table t(c int, d int); +# 8.2. insert tailed select +insert into t select * from t2 where a <=3 order by 1; +select * from t; +c d +1 10 +2 20 +3 30 +delete from t; +insert into t(c) select a from t2 where a <=3 order by 1; +select * from t; +c d +1 NULL +2 NULL +3 NULL +delete from t; +insert into t select * from t2 where a <=3 order by 1 desc limit 2; +select * from t; +c d +3 30 +2 20 +delete from t; +insert into t(c) select a from t2 where a <=3 order by 1 desc limit 2; +select * from t; +c d +3 NULL +2 NULL +delete from t; +insert into t ((select * from t2 where a <=3) order by 1 desc) limit 2; +select * from t; +c d +3 30 +2 20 +delete from t; +insert into t(c) ((select a from t2 where a <=3) order by 1 desc) limit 2; +select * from t; +c d +3 NULL +2 NULL +delete from t; +# 8.3. insert select without from clause +insert into t select 10, 20; +select * from t; +c d +10 20 +delete from t; +insert into t(c) select 10; +select * from t; +c d +10 NULL +delete from t; +# 8.4. insert union of selects without from clause +insert into t select 10,20 union select 70,80; +select * from t; +c d +10 20 +70 80 +delete from t; +insert into t(c) select 10 union select 70; +select * from t; +c d +10 NULL +70 NULL +delete from t; +# 8.5. insert TVC +insert into t values (7,70), (3,30), (8,80); +select * from t; +c d +7 70 +3 30 +8 80 +delete from t; +insert into t(c) values (7), (3), (8); +select * from t; +c d +7 NULL +3 NULL +8 NULL +delete from t; +insert into t (values (7,70), (3,30), (8,80)); +select * from t; +c d +7 70 +3 30 +8 80 +delete from t; +insert into t(c) (values (7), (3), (8)); +select * from t; +c d +7 NULL +3 NULL +8 NULL +delete from t; +insert into t ((values (7,70), (3,30), (8,80))); +select * from t; +c d +7 70 +3 30 +8 80 +delete from t; +insert into t(c) ((values (7), (3), (8))); +select * from t; +c d +7 NULL +3 NULL +8 NULL +delete from t; +# 8.7. insert simple select with CTE +insert into t +with s(a,b) as (select * from t2 where a <=3 order by 1 desc limit 2) +select * from s; +select * from t; +c d +3 30 +2 20 +delete from t; +insert into t(c) +with s(a) as (select a from t2 where a <=3 order by 1 desc limit 2) +select * from s; +select * from t; +c d +3 NULL +2 NULL +delete from t; +insert into t +with s as +( (select * from t2 where a <=4 order by 1 desc limit 2) +union +values (3,30), (8,80), (7,70) ) +select * from s; +select * from t; +c d +4 40 +3 30 +8 80 +7 70 +delete from t; +insert into t(c) +with s as +( (select a from t2 where a <=4 order by 1 desc limit 2) +union +values (3), (8), (7) ) +select * from s; +select * from t; +c d +4 NULL +3 NULL +8 NULL +7 NULL +delete from t; +# 8.8. insert into union with CTE +insert into t(c) +with s as +( (select a from t2 where a <=4 order by 1 desc limit 2) +union +values (3), (8), (7) ) +select * from s where a>=7 union select a from t2 where b<40; +select * from t; +c d +8 NULL +7 NULL +3 NULL +1 NULL +2 NULL +delete from t; +insert into t +with s as +( (select * from t2 where a <=4 order by 1 desc limit 2) +union +values (3,30), (8,80), (7,70) ) +select * from s where a>=7 union select * from s where a<4; +select * from t; +c d +8 80 +7 70 +3 30 +delete from t; +insert into t(c) +with s as +( (select a from t2 where a <=4 order by 1 desc limit 2) +union +values (3), (8), (7) ) +select * from s where a>=7 union select * from s where a<4; +select * from t; +c d +8 NULL +7 NULL +3 NULL +delete from t; +insert into t +with s as +( select * from t2 where a <=4 or a=7 ) +select * from s where a>=7 union select * from s where a<3; +select * from t; +c d +7 70 +1 10 +2 20 +delete from t; +insert into t(c) +with s as +( select a from t2 where a <=4 or a=7 ) +select * from s where a>=7 union select * from s where a<3; +select * from t; +c d +7 NULL +1 NULL +2 NULL +delete from t; +drop table t; +# 9. derived table +# 9.1. derived table as [tailed] simple select +select * from (select * from t1) as dt; +a +3 +7 +1 +2 +4 +select * from ((select * from t1)) as dt; +a +3 +7 +1 +2 +4 +select * from (((select * from t1))) as dt; +a +3 +7 +1 +2 +4 +select * from (select * from t1 order by a) as dt; +a +3 +7 +1 +2 +4 +select * from (select a from t1 order by a) as dt; +a +3 +7 +1 +2 +4 +select * from (select a from t1 order by 1) as dt; +a +3 +7 +1 +2 +4 +select * from (select a from t1 order by t1.a) as dt; +a +3 +7 +1 +2 +4 +select * from ((select * from t1 order by t1.a limit 2)) as dt; +a +1 +2 +select * from ((select * from t2 order by a limit 2) order by b desc) dt; +a b +1 10 +2 20 +select * from ((select a from t1 where a=1) order by 1 desc) dt; +a +1 +# 9.2. derived table as select with two tails +select * from +((select * from t2 order by t2.a limit 2) order by b desc) dt; +a b +1 10 +2 20 +select * from +((select * from t2 order by t2.a limit 2) order by b desc) as dt; +a b +1 10 +2 20 +select * from +(((select * from t2 order by t2.a limit 2) order by b desc )) as dt; +a b +1 10 +2 20 +select * from +(((select * from t2 order by t2.a) limit 2) order by b desc) dt; +a b +1 10 +2 20 +select * from +((select * from t2 order by a limit 2) order by b desc) dt; +a b +1 10 +2 20 +select * from +((select a from t1 where a=1) order by 1 desc) as dt; +a +1 +select * from +((select * from t2 order by t2.a limit 2) order by b desc) as dt; +a b +1 10 +2 20 +# 9.3. derived table as union +select * from (select a from t1 union select a from t1) as dt; +a +3 +7 +1 +2 +4 +select * from (select a from t1 union all select a from t1) as dt; +a +3 +7 +1 +2 +4 +3 +7 +1 +2 +4 +select * from (select a from t1 union select b from t2) as dt; +a +3 +7 +1 +2 +4 +30 +70 +10 +20 +40 +select * from +((select a from t1) union (select a from t1)) as dt; +a +3 +7 +1 +2 +4 +select * from +((select a from t1) union (select b from t2)) as dt; +a +3 +7 +1 +2 +4 +30 +70 +10 +20 +40 +select * from +(select a from t1 where a=1 union select a from t1 where a=3) dt; +a +1 +3 +select * from +((select a from t1 where a=1) union select a from t1 where a=3) dt; +a +1 +3 +select * from +(((select a from t1 where a=1) union select a from t1 where a=3)) dt; +a +1 +3 +select * from +(((select a from t1 where a<=3) union (select a from t1 where a=3))) as dt; +a +3 +1 +2 +select * from +(select a from t1 where a=1 union (select a from t1 where a=3)) as dt; +a +1 +3 +select * from +((select a from t1 where a=1 union (select a from t1 where a=3))) as dt; +a +1 +3 +select * from +(((select a from t1 where a=1 union (select a from t1 where a=3)))) as dt; +a +1 +3 +select * from +( select a from t1 where a=1 +union +select a from t1 where a=3 +union +select a from t1 where a=7 ) as dt; +a +1 +3 +7 +select * from +( (select a from t1 where a=1 order by a) +union +select a from t1 where a=3 ) as dt; +a +1 +3 +select * from +( (select a from t1 where a!=3 order by a desc) +union +select a from t1 where a=3 ) as dt; +a +7 +1 +2 +4 +3 +select * from +( ( select a from t1 where a <=3 except select a from t1 where a >=3 ) +union +select a from t1 where a=7 ) as dt; +a +1 +2 +7 +select * from +( ( ( select a from t1 where a <=3 +except +select a from t1 where a >=3 ) +union +select a from t1 where a=7 ) ) as dt; +a +1 +2 +7 +select * from +( select a from t1 where a=1 +union +select a from t1 where a=3 +order by a desc) as dt; +a +3 +1 +select *from +( (select a from t1 limit 2) +union +select a from t1 where a=3 +order by a desc) as dt; +a +7 +3 +select * from +( select a from t1 where a=4 +union +(select a from t1 where a <=4 limit 2) +order by a desc ) as dt; +a +4 +3 +1 +select * from +( ( select a from t1 where a=4 +union +( select a from t1 where a <=4 order by a ) ) +order by a desc limit 2 ) as dt; +a +4 +3 +select * from +( ( select a from t1 where a <=3 except select a from t1 where a >=3 ) +union +select a from t1 where a=7 order by a desc ) as dt; +a +7 +2 +1 +select * from +( ( select a from t1 where a!=3 order by a desc ) +union +select a from t1 where a=3 +order by a desc ) as dt; +a +7 +4 +3 +2 +1 +select * from +( (select a from t1 where a=1) +union +(select a from t1 where a=3) +order by a desc ) as dt; +a +3 +1 +select * from +( ( select a from t1 where a=1 +union +select a from t1 where a=3 ) +order by a desc ) as dt; +a +3 +1 +select * from +( ( ( select a from t1 where a=1 ) +union +( select a from t1 where a=3 ) ) +order by a desc ) as dt; +a +3 +1 +select * from +( ( select a from t1 where a=1 +union +select a from t1 where a=3 ) +order by 1 desc ) as dt; +a +3 +1 +select * from +( ( (select a from t1 where a=1 +union +select a from t1 where a=3) ) order by 1 desc ) as dt; +a +3 +1 +select * from +((((select a from t1 where a=1) union (select a from t1 where a=3))) +order by 1 desc ) as dt; +a +3 +1 +select * from +( ( (select a from t1 where a=1 ) +union +(select a from t1 where a=3) ) +order by 1 desc ) as dt; +a +3 +1 +select * from +( select a from t1 where a=1 +union +select a from t1 where a=3 +union +select a from t1 where a=2 +union +select a from t1 where a=4 ) as dt; +a +1 +3 +2 +4 +select * from +( ( select a from t1 where a=1 +union +select a from t1 where a=3 +union +select a from t1 where a=2 ) +union +select a from t1 where a=4 ) as dt; +a +1 +3 +2 +4 +select * from +( (select a from t1 where a=1 union select a from t1 where a=3) +union +(select a from t1 where a=2 union select a from t1 where a=4) ) as dt; +a +1 +3 +2 +4 +select * from +( (select a from t1 where a=1 union (select a from t1 where a=3)) +union +((select a from t1 where a=2) union select a from t1 where a=4) ) as dt; +a +1 +3 +2 +4 +select * from +( ( ( select a from t1 where a=1) +union +select a from t1 where a=3 ) +union +select a from t1 where a=2 +union +select a from t1 where a=4 ) as dt; +a +1 +3 +2 +4 +select * from +( ( ( ( select a from t1 where a=1) +union +select a from t1 where a=3 ) +union +select a from t1 where a=2 ) +union +select a from t1 where a=4 ) as dt; +a +1 +3 +2 +4 +select * from +( select a from t1 where a=1 +union +select a from t1 where a=3 +union +select a from t1 where a=2 +union +(select a from t1 where a=4) ) as dt; +a +1 +3 +2 +4 +select * from +( select a from t1 where a=1 +union +select a from t1 where a=3 +union +( select a from t1 where a=2 +union +( select a from t1 where a=4 ) ) ) as dt; +a +1 +3 +2 +4 +select * from +( select a from t1 where a=1 +union +( select a from t1 where a=3 +union +( select a from t1 where a=2 +union +( select a from t1 where a=4 ) ) ) ) as dt; +a +1 +3 +2 +4 +select * from +( ( ( select a from t1 where a=1 union select a from t1 where a=3 ) +order by a desc limit 2 ) +union +( ( select a from t1 where a=2 union select a from t1 where a=4 ) +order by a desc limit 1 ) ) as dt; +a +3 +1 +4 +select * from +( ( ( select a from t1 where a=1 union select a from t1 where a=3 ) +order by a desc limit 2 ) +union +( ( select a from t1 where a=2 union select a from t1 where a=4 ) +order by a desc limit 2 ) +order by a) as dt; +a +1 +2 +3 +4 +select * from +( ( select a from t1 where a=1 +union +select a from t1 where a=3 +union +select a from t1 where a=2 order by a desc limit 2 ) +union +select a from t1 where a=4 +order by a limit 3 ) as dt; +a +2 +3 +4 +select * from +( ( select a from t1 where a=1 +union +select a from t1 where a=3 order by a desc limit 2) +union +select a from t1 where a=2 order by a desc limit 2 ) as dt; +a +3 +2 +select * from +( ( ( select a from t1 where a >= 2 +union +select a from t1 where a=1 order by a desc limit 2 ) +union +select a from t1 where a=3 order by a limit 2 ) +union +select a from t1 where a=1 ) as dt; +a +3 +4 +1 +# 9.3. derived table as [tailed] TVC +select * from +( values (3), (7), (1) ) as dt; +3 +3 +7 +1 +select * from +( (values (3), (7), (1)) ) as dt; +3 +3 +7 +1 +select * from +(((values (3), (7), (1)))) as dt; +3 +3 +7 +1 +select * from +( values (3), (7), (1) order by 1 limit 2 ) as dt; +3 +1 +3 +select * from +( (values (3), (7), (1)) order by 1 limit 2 ) as dt; +3 +1 +3 +select * from +( ((values (3), (7), (1))) order by 1 limit 2 ) as dt; +3 +1 +3 +select * from +( (((values (3), (7), (1))) order by 1 limit 2) ) as dt; +3 +1 +3 +select * from +( ( (values (3), (7), (1) limit 2) order by 1 desc) ) as dt; +3 +3 +7 +select * from +( ((values (3), (7), (1)) order by 1 desc) limit 2 ) as dt; +3 +7 +3 +select * from +( (((values (3), (7), (1)) order by 1 desc) limit 2) ) as dt; +3 +7 +3 +# 9.3. derived table as union of TVCs +select * from +( values (3), (7), (1) union values (3), (4), (2) ) dt; +3 +3 +7 +1 +4 +2 +select * from +( values (3), (7), (1) union all values (3), (4), (2) ) as dt; +3 +3 +7 +1 +3 +4 +2 +select * from +( values (3), (7), (1) union values (3), (4), (2) ) as dt; +3 +3 +7 +1 +4 +2 +select * from +( values (3), (7), (1) except values (3), (4), (2) ) as dt; +3 +7 +1 +select * from +( (values (3), (7), (1)) union (values (3), (4), (2)) ) as dt; +3 +3 +7 +1 +4 +2 +select * from +( (values (3), (7), (1)) +union +(values (3), (4), (2)) +union values (5), (7) ) dt; +3 +3 +7 +1 +4 +2 +5 +select * from +( (values (3), (7), (1)) +union +(values (3), (4), (2)) +union +(values (5), (7)) ) as dt; +3 +3 +7 +1 +4 +2 +5 +select * from +( (values (3), (7), (1) +union +values (3), (4), (2)) +union +values (5), (7) ) as dt; +3 +3 +7 +1 +4 +2 +5 +select * from +( values (3), (7), (1) +union (values (3), (4), (2) +union +values (5), (7)) ) as dt; +3 +3 +7 +1 +4 +2 +5 +select * from +( (values (3), (7), (1) +union +((values (3), (4), (2) +union values (5), (7)))) ) dt; +3 +3 +7 +1 +4 +2 +5 +select * from +( values (3), (7), (1) +union +values (3), (4), (2) +order by 1 ) as dt; +3 +1 +2 +3 +4 +7 +select * from +( (values (3), (7), (1) union values (3), (4), (2)) order by 1 ) as dt; +3 +1 +2 +3 +4 +7 +select * from +( (values (3), (7), (1) union values (3), (4), (2)) order by 1 ) as dt; +3 +1 +2 +3 +4 +7 +select * from +( values (3), (7), (1) union (values (3), (4), (2)) order by 1 ) as dt; +3 +1 +2 +3 +4 +7 +select * from +( (values (3), (7), (1) union values (3), (4), (2)) order by 1 ) as dt; +3 +1 +2 +3 +4 +7 +select * from +( ((values (3), (7), (1)) union values (3), (4), (2)) order by 1 ) as dt; +3 +1 +2 +3 +4 +7 +select * from +( (values (3), (7), (1) order by 1 limit 2) +union +(values (3), (4), (2) order by 1 desc limit 2) ) as dt; +3 +1 +3 +4 +select * from +( ((values (3), (7), (1) order by 1) limit 2) +union +((values (3), (4), (2) order by 1 desc) limit 2) ) as dt; +3 +1 +3 +4 +select * from +( (((values (3), (7), (1)) order by 1) limit 2) +union +(((values (3), (4), (2)) order by 1 desc) limit 2) ) as dt; +3 +1 +3 +4 +select * from +( (values (3), (7), (1) order by 1 limit 2) +union +values (3), (4), (2) +order by 1 limit 3 ) as dt; +3 +1 +2 +3 +select * from +( ((values (3), (7), (1)) order by 1 limit 2) +union +((values (3), (4), (2) order by 1 desc) limit 2) +order by 1 limit 3 ) as dt; +3 +1 +3 +4 +select * from +( (select a from t1 where a <=3 order by 1 limit 2) +union +(values (3), (4), (2) order by 1 desc limit 2) ) dt; +a +1 +2 +4 +3 +select * from +( ((select a from t1 where a <=3) order by 1 limit 2) +union +(values (3), (4), (2) order by 1 desc limit 2) ) as dt; +a +1 +2 +4 +3 +select * from +( (((select a from t1 where a <=3) order by a) limit 2) +union +(((values (3), (4), (2)) order by 1 desc) limit 2) ) as dt; +a +1 +2 +4 +3 +select * from +( ( (((select a from t1 where a <=3) order by a) limit 2) +union +(((values (3), (4), (2)) order by 1 desc) limit 2) ) ) dt; +a +1 +2 +4 +3 +select * from +( (select a from t1 where a <=3 order by 1 limit 2) +union +(values (3), (4), (2) order by 1 desc limit 2) +order by a ) as dt; +a +1 +2 +3 +4 +select * from +( ((select a from t1 where a <=3) order by 1 limit 2) +union +(values (3), (4), (2) order by 1 desc limit 2) +order by a ) as dt; +a +1 +2 +3 +4 +select * from +( (((select a from t1 where a <=3) order by a) limit 2) +union +(((values (3), (4), (2)) order by 1 desc) limit 2) +order by a ) as dt; +a +1 +2 +3 +4 +select * from +( (((values (3), (4), (2)) order by 1 desc) limit 2) ) as dt; +3 +4 +3 +select * from +( ( (((select a from t1 where a <=3) order by a) limit 2) +union +(((values (3), (4), (2)) order by 1 desc) limit 2) ) +order by a ) as dt; +a +1 +2 +3 +4 +select * from +( (values (3), (4), (2) order by 1 desc limit 2) +union +(select a from t1 where a <=3 order by 1 limit 2) ) as dt; +3 +4 +3 +1 +2 +select * from +( (values (3), (4), (2) order by 1 desc limit 2) +union +((select a from t1 where a <=3) order by 1 limit 2) ) as dt; +3 +4 +3 +1 +2 +select * from +( (((values (3), (4), (2)) order by 1 desc) limit 2) +union +(((select a from t1 where a <=3) order by 1) limit 2) ) as dt; +3 +4 +3 +1 +2 +select * from +( (((values (3), (4), (2)) order by 1 desc) limit 2) +union +(((select a from t1 where a <=3) order by a) limit 2) +order by 1 ) as dt; +3 +1 +2 +3 +4 +select * from +( ( select a from t1 where a=1 +union +values (3), (4), (2) order by 1 desc ) +union +select a from t1 where a=2 order by a desc limit 3 ) as dt; +a +4 +3 +2 +# 9.4. derived table as [tailed] simple select with CTE +select * from +( with t as (select * from t1 where a <=3) +select * from t ) as dt; +a +3 +1 +2 +select * from +( with t as (select * from t1 where a <=3) +(select * from t) ) as dt; +a +3 +1 +2 +select * from +( with t as (select * from t1 where a <=3) +((select * from t)) ) as dt; +a +3 +1 +2 +select * from +( with t as ((select * from t1 where a <=3)) +select * from t ) as dt; +a +3 +1 +2 +select * from +( with t as (((select * from t1 where a <=3))) +select * from t ) as dt; +a +3 +1 +2 +select * from +( with t as (select * from t1 where a <=3) +select * from t order by a ) as dt; +a +3 +1 +2 +select * from +( with t as (select * from t1 where a <=3) +(select * from t) order by a ) as dt; +a +3 +1 +2 +select * from +( with t as (select * from t1 where a <=3) +(select * from t) order by a desc limit 2 ) as dt; +a +3 +2 +select * from +( with t as (select * from t1 where a >=2 order by a limit 2) +select * from t ) as dt; +a +2 +3 +select * from +( with t as (((select * from t1 where a >=2) order by a desc) limit 2) +select * from t ) as dt; +a +7 +4 +select * from +( with t as (select * from t1 where a >=2 order by a desc limit 2) +select * from t order by a ) as dt; +a +7 +4 +# 9.5. derived table as tailed union with CTE +select * from +( with t as (select * from t1 where a <=3) +select a from t1 where a=1 union select a from t where a=3 ) as dt; +a +1 +3 +select * from +( with t as (select * from t1 where a <=3) +(select a from t) union (select b from t2) ) as dt; +a +3 +1 +2 +30 +70 +10 +20 +40 +select * from +( with t as (select * from t1 where a <=3) +(select a from t) union (select b as a from t2) order by a desc ) as dt; +a +70 +40 +30 +20 +10 +3 +2 +1 +select * from +( with t as (select * from t1 where a < 3 union select * from t1 where a > 3) +select a from t1 where a=1 union select a from t where a=7 ) as dt; +a +1 +7 +select * from +( with t as +( select * from t1 where a < 3 +union +select * from t1 where a > 3 +order by a desc limit 3 ) +select a from t1 where a=4 union select a from t where a=7 ) as dt; +a +4 +7 +select * from +( with t as +( select * from t1 where a < 3 +union +select * from t1 where a > 3 +order by a desc limit 3 ) +select a from t1 where a=4 +union +select a from t where a=7 +order by a desc ) as dt; +a +7 +4 +select * from +( with t as +( (select * from t1 where a < 3) +union +(select * from t1 where a > 3) +order by a desc limit 3 ) +select a from t1 where a=4 +union select a from t where a=7 +order by a desc ) dt; +a +7 +4 +select * from +( with t as +( (select * from t1 where a < 3) +union +(select * from t1 where a > 3) +order by a desc limit 3 ) +(select a from t1 where a=4 +union +select a from t where a=7 +order by a desc) ) as dt; +a +7 +4 +select * from +( with t as +( (select * from t1 where a < 3) +union +(select * from t1 where a > 3) +order by a desc limit 3 ) +((select a from t1 where a=4 +union +select a from t where a=7) order by a desc) ) as dt; +a +7 +4 +select * from +( with t as +( select * from t1 where a < 3 +union +values (4), (7) +order by a desc limit 3 ) +select a from t1 where a=4 +union +select a from t where a=7 +order by a desc ) dt; +a +7 +4 +select * from +( with t(a) as +( values (2), (1) +union +(values (4), (7)) +order by 1 desc limit 3 ) +select a from t1 where a=4 +union select a from t where a=7 +order by a desc ) as dt; +a +7 +4 +select * from +( with t(a) as +( (values (2), (1)) +union +(values (4), (7) order by 1 desc) +order by 1 desc limit 3 ) +select a from t1 where a=1 +union +select a from t where a=7 order by a desc ) as dt; +a +7 +1 +select * from +( with t(a) as +( (values (2), (1)) +union +(values (4), (7) order by 1 desc) +order by 1 limit 3 ) +select a from t where a=1 union values (7) order by a desc ) as dt; +a +7 +1 +select * from +( with t(a) as +( (values (2), (1)) +union +(values (4), (7) order by 1 desc ) ) +select a from t where a=1 union select 7 order by a desc ) as dt; +a +7 +1 +select * from +( with t as (select * from t1 where a < 3), +s as (select * from t1 where a > 3) +select a from t where a=1 +union select a from s where a=7 +order by a desc ) dt; +a +7 +1 +select * from +( with t as (select * from t1 where a < 3), +s as (select * from t1 where a > 3) +(select a from t where a=1 +union +select a from s where a=7 order by a desc) ) dt; +a +7 +1 +select * from +( with t as (select * from t1 where a < 3), +s as (select * from t1 where a > 3) +(select a from t where a=1 +union +select a from s where a=7) +order by a desc ) dt; +a +7 +1 +10. view +10.1. view as simple select +create view v1 as +select * from t1; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci +select * from v1; +a +3 +7 +1 +2 +4 +drop view v1; +create view v1 as +select 2*a as c from t1; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 2 * `t1`.`a` AS `c` from `t1` latin1 latin1_swedish_ci +select * from v1; +c +6 +14 +2 +4 +8 +drop view v1; +create view v1(c) as +select 2*a from t1; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 2 * `t1`.`a` AS `c` from `t1` latin1 latin1_swedish_ci +select * from v1; +c +6 +14 +2 +4 +8 +drop view v1; +create view v1 as +((select * from t1)); +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a` from `t1`) latin1 latin1_swedish_ci +select * from v1; +a +3 +7 +1 +2 +4 +drop view v1; +10.2. view as tailed simple select +create view v1 as +select * from t1 order by a; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` order by `t1`.`a` latin1 latin1_swedish_ci +select * from v1; +a +1 +2 +3 +4 +7 +drop view v1; +create view v1 as +(select * from t2 order by a limit 2) order by b desc; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `__3`.`a` AS `a`,`__3`.`b` AS `b` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`a` limit 2) `__3` order by `__3`.`b` desc latin1 latin1_swedish_ci +select * from v1; +a b +2 20 +1 10 +drop view v1; +10.3. view as union +create view v1 as +select a from t1 union select b from t2; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` union select `t2`.`b` AS `b` from `t2` latin1 latin1_swedish_ci +select * from v1; +a +3 +7 +1 +2 +4 +30 +70 +10 +20 +40 +drop view v1; +create view v1 as +(select a from t1) union (select b from t2); +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a` from `t1`) union (select `t2`.`b` AS `b` from `t2`) latin1 latin1_swedish_ci +select * from v1; +a +3 +7 +1 +2 +4 +30 +70 +10 +20 +40 +drop view v1; +create view v1 as +(select a from t1 where a=1) union select a from t1 where a=3; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 1) union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3 latin1 latin1_swedish_ci +select * from v1; +a +1 +3 +drop view v1; +create view v1 as +((select a from t1 where a<=3) union (select a from t1 where a=3)); +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a` from `t1` where `t1`.`a` <= 3) union (select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3) latin1 latin1_swedish_ci +select * from v1; +a +3 +1 +2 +drop view v1; +create view v1 as +select a from t1 where a=1 +union +select a from t1 where a=3 +union +select a from t1 where a=7; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 1 union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3 union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 7 latin1 latin1_swedish_ci +select * from v1; +a +1 +3 +7 +drop view v1; +create view v1 as +( ( select a from t1 where a!=3 order by a desc limit 3) +union +select a from t1 where a=3 ); +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a` from `t1` where `t1`.`a` <> 3 order by `t1`.`a` desc limit 3) union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3 latin1 latin1_swedish_ci +select * from v1; +a +7 +4 +2 +3 +drop view v1; +create view v1 as +( select a from t1 where a <=3 except select a from t1 where a >=3 ) +union +select a from t1 where a=7; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `__5`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` <= 3 except select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` >= 3) `__5` union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 7 latin1 latin1_swedish_ci +select * from v1; +a +1 +2 +7 +drop view v1; +create view v1 as +(select a from t1 limit 2) union select a from t1 where a=3 order by a desc; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a` from `t1` limit 2) union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3 order by `a` desc latin1 latin1_swedish_ci +select * from v1; +a +7 +3 +drop view v1; +create view v1 as +select a from t1 where a=1 +union +( select a from t1 where a=3 +union +( select a from t1 where a=2 +union +( select a from t1 where a=4 ) ) ); +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 union select `__7`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 3 union select `__6`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 union (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 4)) `__6`) `__7` latin1 latin1_swedish_ci +select * from v1; +a +1 +3 +2 +4 +drop view v1; +create view v1 as +( ( select a from t1 where a >= 2 +union +select a from t1 where a=1 order by a desc limit 2 ) +union +select a from t1 where a=3 order by a limit 2 ) +union +select a from t1 where a=1; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `__7`.`a` AS `a` from (select `__5`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` >= 2 union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 order by `a` desc limit 2) `__5` union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 3 order by `a` limit 2) `__7` union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 latin1 latin1_swedish_ci +select * from v1; +a +3 +4 +1 +drop view v1; +10.4. view as [tailed] TVC +create view v1 as +values (3), (7), (1); +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS values (3),(7),(1) latin1 latin1_swedish_ci +select * from v1; +3 +3 +7 +1 +drop view v1; +create view v1 as +(((values (3), (7), (1))) order by 1); +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (values (3),(7),(1) order by 1) latin1 latin1_swedish_ci +select * from v1; +3 +1 +3 +7 +drop view v1; +10.5. view as [tailed] union of TVCs +create view v1 as +values (3), (7), (1) union values (3), (4), (2); +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS values (3),(7),(1) union values (3),(4),(2) latin1 latin1_swedish_ci +select * from v1; +3 +3 +7 +1 +4 +2 +drop view v1; +create view v1 as +(values (3), (7), (1) union values (3), (4), (2)) order by 1; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS values (3),(7),(1) union values (3),(4),(2) order by 1 latin1 latin1_swedish_ci +select * from v1; +3 +1 +2 +3 +4 +7 +drop view v1; +create view v1 as +(values (3), (7), (1) order by 1 limit 2) +union +(values (3), (4), (2) order by 1 desc limit 2); +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (values (3),(7),(1) order by 1 limit 2) union (values (3),(4),(2) order by 1 desc limit 2) latin1 latin1_swedish_ci +select * from v1; +3 +1 +3 +4 +drop view v1; +create view v1 as +(values (3), (7), (1) order by 1 limit 2) +union +values (3), (4), (2) +order by 1; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (values (3),(7),(1) order by 1 limit 2) union values (3),(4),(2) order by 1 latin1 latin1_swedish_ci +select * from v1; +3 +1 +2 +3 +4 +drop view v1; +10.6. view as [tailed] union of [tailed] select and tailed TVC +create view v1 as +( (((select a from t1 where a <=3) order by a) limit 2) +union +(((values (3), (4), (2)) order by 1 desc) limit 2) ) +order by a; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a` from `t1` where `t1`.`a` <= 3 order by `t1`.`a` limit 2) union (values (3),(4),(2) order by 1 desc limit 2) order by `a` latin1 latin1_swedish_ci +select * from v1; +a +1 +2 +3 +4 +drop view v1; +create view v1 as +( select a from t1 where a=1 +union +values (3), (4), (2) order by 1 desc ) +union +select a from t1 where a=2 order by a desc limit 3; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `__5`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 union values (3),(4),(2) order by 1 desc) `__5` union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 order by `a` desc limit 3 latin1 latin1_swedish_ci +select * from v1; +a +4 +3 +2 +drop view v1; +10.7. view as select with CTE +create view v1 as +with t as (select * from t1 where a <=3) +select * from t; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` <= 3)select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci +select * from v1; +a +3 +1 +2 +drop view v1; +create view v1 as +with t as +( select * from t1 where a < 3 +union +select * from t1 where a > 3 +order by a desc limit 3 ) +select a from t1 where a=4 union select a from t where a=7; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3 union select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3 order by `a` desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 latin1 latin1_swedish_ci +select * from v1; +a +4 +7 +drop view v1; +10.8. view as union with CTE +create view v1 as +with t as +( (select * from t1 where a < 3) +union +(select * from t1 where a > 3) +order by a desc limit 3 ) +(select a from t1 where a=4 union select a from t where a=7 order by a desc); +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as ((select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3) union (select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3) order by `a` desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci +select * from v1; +a +7 +4 +drop view v1; +create view v1 as +with t as +( (select * from t1 where a < 3) +union +(select * from t1 where a > 3) +order by a desc limit 3 ) +(select a from t where a=4 union select a from t where a=7 order by a desc); +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as ((select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3) union (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 3) order by `a` desc limit 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 4 union select `t`.`a` AS `a` from ((select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3) union (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 3) order by `a` desc limit 3) `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci +select * from v1; +a +7 +4 +drop view v1; +create view v1 as +with t(a) as (values (2), (1)) select a from t; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(a) as (values (2),(1))select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci +select * from v1; +a +2 +1 +drop view v1; +create view v1 as +with t(a) as +( values (2), (1) +union +(values (4), (7)) +order by 1 desc limit 3 ) +select a from t1 where a=4 union select a from t where a=7 order by a desc; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(a) as (values (2),(1) union (values (4),(7)) order by 1 desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci +select * from v1; +a +7 +4 +drop view v1; +create view v1 as +with t(a) as +( (values (2), (1)) +union +(values (4), (7) order by 1 desc) +order by 1 desc limit 3 ) +select a from t1 where a=1 union select a from t where a=7 order by a desc; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(a) as ((values (2),(1)) union (values (4),(7) order by 1 desc) order by 1 desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 1 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci +select * from v1; +a +7 +1 +drop view v1; +create view v1 as +with t as (select * from t1 where a < 3), +s as (select * from t1 where a > 3) +select a from t where a=1 union select a from s where a=7 order by a desc; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3), s as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci +select * from v1; +a +7 +1 +drop view v1; +create view v1 as +with t as (select * from t1 where a < 3), +s as (select * from t where a > 3) +select a from t where a=1 union select a from s where a=7 order by a desc; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3), s as (select `t`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3) `t` where `t`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci +select * from v1; +a +1 +drop view v1; +drop table t1,t2; +# End of 10.4 tests |