From b44171428ab2ea25db82f7cd27349e67812e4921 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 20 Sep 2019 09:03:38 -0700 Subject: MDEV-19956 Queries with subqueries containing UNION are not parsed Shift-Reduce conflicts prevented parsing some queries with subqueries that used set operations when the subqueries occurred in expressions or in IN predicands. The grammar rules for query expression were transformed in order to avoid these conflicts. New grammar rules employ an idea taken from MySQL 8.0. --- mysql-test/main/brackets.result | 4022 +++++++++++++++++++++- mysql-test/main/brackets.test | 2318 +++++++++++++ mysql-test/main/except.result | 4 +- mysql-test/main/intersect.result | 6 +- mysql-test/main/parser.result | 2 +- mysql-test/main/parser.test | 1 - mysql-test/main/subselect.result | 15 +- mysql-test/main/subselect.test | 4 - mysql-test/main/subselect_no_exists_to_in.result | 15 +- mysql-test/main/subselect_no_mat.result | 15 +- mysql-test/main/subselect_no_opts.result | 15 +- mysql-test/main/subselect_no_scache.result | 15 +- mysql-test/main/subselect_no_semijoin.result | 15 +- sql/item_subselect.cc | 3 +- sql/sql_lex.cc | 295 +- sql/sql_lex.h | 48 +- sql/sql_table.cc | 2 +- sql/sql_tvc.cc | 4 +- sql/sql_union.cc | 48 +- sql/sql_yacc.yy | 437 ++- sql/sql_yacc_ora.yy | 423 ++- 21 files changed, 7163 insertions(+), 544 deletions(-) diff --git a/mysql-test/main/brackets.result b/mysql-test/main/brackets.result index e14bef956a9..dedd9a2a2bf 100644 --- a/mysql-test/main/brackets.result +++ b/mysql-test/main/brackets.result @@ -355,7 +355,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 UNION t1 ALL NULL NULL NULL NULL 3 100.00 NULL UNION RESULT 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 +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 { @@ -494,4 +494,4024 @@ a 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 diff --git a/mysql-test/main/brackets.test b/mysql-test/main/brackets.test index 9ca86b87032..b7bb616bd05 100644 --- a/mysql-test/main/brackets.test +++ b/mysql-test/main/brackets.test @@ -176,5 +176,2323 @@ select * from t1; drop table t1; +--echo # +--echo # MDEV-19956: query expressions in different contexts +--echo # + +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); + + +--echo # 1. select + +--echo # 1.1. simple select + +select * from t1; +(select * from t1); +((select * from t1)); +--echo # 1.2. select with tail +select * from t1 order by a; +select a from t1 order by a; +select a from t1 order by 1; +select * from t1 order by t1.a; +(select * from t1 order by t1.a); +((select * from t1 order by t1.a)); +(select * from t1 order by t1.a limit 2); +(select a from t1 where a=1) order by 1 desc; + +--echo # 1.2. select with several tails + +(select * from t2 order by a limit 2) order by b desc; +(select * from t2 order by t2.a limit 2) order by b desc; +((select * from t2 order by t2.a limit 2) order by b desc); +(((select * from t2 order by t2.a) limit 2) order by b desc); + + +--echo # 2. union + +--echo # 2.1 simple union + +select a from t1 union select a from t1; +select a from t1 union all select a from t1; +select a from t1 union select b from t2; +(select a from t1) union (select a from t1); +(select a from t1) union (select b from t2); +select a from t1 where a=1 union select a from t1 where a=3; +(select a from t1 where a=1) union select a from t1 where a=3; +((select a from t1 where a=1) union select a from t1 where a=3); +((select a from t1 where a<=3) union (select a from t1 where a=3)); +select a from t1 where a=1 union (select a from t1 where a=3); +(select a from t1 where a=1 union (select a from t1 where a=3)); +((select a from t1 where a=1 union (select a from t1 where a=3))); + +select a from t1 where a=1 +union +select a from t1 where a=3 +union +select a from t1 where a=7; + +( select a from t1 where a=1 + union + select a from t1 where a=3 + union + select a from t1 where a=7 ); + +(select a from t1 where a=1 order by a) union select a from t1 where a=3; +(select a from t1 where a!=3 order by a desc) union select a from t1 where a=3; +((select a from t1 where a=1 order by a) union select a from t1 where a=3); +(select a from t1 where a!=3 order by a desc) union select a from t1 where a=3; + +( ( select a from t1 where a!=3 order by a desc limit 3) + union + select a from t1 where a=3 ); + +( select a from t1 where a <=3 except select a from t1 where a >=3 ) + union + select a from t1 where a=7; + +( ( select a from t1 where a <=3 + except + select a from t1 where a >=3 ) + union + select a from t1 where a=7 ); + +( select a from t1 where a <=3 + except + ( select a from t1 where a >=3 + union + select a from t1 where a=7 ) ); + +( ( select a from t1 where a <=3 ) + except + ( select a from t1 where a >=3 + union + select a from t1 where a=7 ) ); + +--echo # 2.2. union with tail + +select a from t1 where a=1 union select a from t1 where a=3 order by a desc; +(select a from t1 limit 2) union select a from t1 where a=3 order by a desc; + +select a from t1 where a=4 union (select a from t1 where a <=4 limit 2) +order by a desc; + +select a from t1 where a=4 +union +(select a from t1 where a <=4 order by a limit 2) +order by a desc; + +( select a from t1 where a=4 + union + ( select a from t1 where a <=4 order by a limit 2 ) ) +order by a desc; + +( 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; + +( select a from t1 where a!=3 order by a desc ) + union + select a from t1 where a=3 + order by a desc; + +(select a from t1 where a=1) +union +(select a from t1 where a=3) +order by a desc; + +( select a from t1 where a=1 + union + select a from t1 where a=3 ) +order by a desc; + +( ( select a from t1 where a=1 ) + union + ( select a from t1 where a=3 ) ) +order by a desc; + +( select a from t1 where a=1 + union + select a from t1 where a=3 ) +order by 1 desc; + +((select a from t1 where a=1 union select a from t1 where a=3)) order by 1 desc; +(((select a from t1 where a=1) union (select a from t1 where a=3))) +order by 1 desc; + +( (select a from t1 where a=1 ) + union + (select a from t1 where a=3) ) +order by 1 desc; + +--echo # 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; + +( 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; + +(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); +(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); + +( ( 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; +( ( ( 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; + +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); + +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 ) ); + +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 ) ) ); + +--echo # 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 ); + +( ( 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; + +( 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; + +( 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; + +( ( 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; + + +--echo # 3. TVC + +--echo # 3.1. simple TVC + +values (3), (7), (1); +(values (3), (7), (1)); +((values (3), (7), (1))); + +--echo # 3.2. simple TVC with tail(s) + +values (3), (7), (1) order by 1; +(values (3), (7), (1)) order by 1; +((values (3), (7), (1))) order by 1; +(((values (3), (7), (1))) order by 1); +(values (3), (7), (1) limit 2) order by 1 desc; +((values (3), (7), (1)) order by 1 desc) limit 2; +(((values (3), (7), (1)) order by 1 desc) limit 2); + +--echo # 3.3. union of TVCs + +values (3), (7), (1) union values (3), (4), (2); +values (3), (7), (1) union all values (3), (4), (2); +values (3), (7), (1) union values (3), (4), (2); +values (3), (7), (1) except values (3), (4), (2); +(values (3), (7), (1)) union (values (3), (4), (2)); +(values (3), (7), (1)) union (values (3), (4), (2)) union values (5), (7); +(values (3), (7), (1)) union (values (3), (4), (2)) union (values (5), (7)); +(values (3), (7), (1) union values (3), (4), (2)) union values (5), (7); +values (3), (7), (1) union (values (3), (4), (2) union values (5), (7)); +(values (3), (7), (1) union ((values (3), (4), (2) union values (5), (7)))); + +--echo # 3.4. tailed union of TVCs + +values (3), (7), (1) union values (3), (4), (2) order by 1; +(values (3), (7), (1) union values (3), (4), (2)) order by 1; +(values (3), (7), (1) union values (3), (4), (2)) order by 1; +values (3), (7), (1) union (values (3), (4), (2)) order by 1; +(values (3), (7), (1) union values (3), (4), (2)) order by 1; +((values (3), (7), (1)) union values (3), (4), (2)) order by 1; + +--echo # 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); + +((values (3), (7), (1) order by 1) limit 2) +union +((values (3), (4), (2) order by 1 desc) limit 2); + +(((values (3), (7), (1)) order by 1) limit 2) +union +(((values (3), (4), (2)) order by 1 desc) limit 2); + +--echo # 3.6. tailed union of tailed TVCs + +(values (3), (7), (1) order by 1 limit 2) +union +values (3), (4), (2) +order by 1; + +((values (3), (7), (1)) order by 1 limit 2) +union +((values (3), (4), (2) order by 1 desc) limit 2) +order by 1; + +--echo # 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); + +((select a from t1 where a <=3) order by 1 limit 2) +union +(values (3), (4), (2) order by 1 desc limit 2); + +(((select a from t1 where a <=3) order by a) limit 2) +union +(((values (3), (4), (2)) order by 1 desc) limit 2); + +( (((select a from t1 where a <=3) order by a) limit 2) + union + (((values (3), (4), (2)) order by 1 desc) limit 2) ); + +(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; + +((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; + +(((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; + +(((values (3), (4), (2)) order by 1 desc) limit 2); +( (((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; + +(values (3), (4), (2) order by 1 desc limit 2) +union +(select a from t1 where a <=3 order by 1 limit 2); + +(values (3), (4), (2) order by 1 desc limit 2) +union +((select a from t1 where a <=3) order by 1 limit 2); + +(((values (3), (4), (2)) order by 1 desc) limit 2) +union +(((select a from t1 where a <=3) order by 1) limit 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; + +( 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; + + +--echo 4. CTE + +--echo 4.1. simple select with simple CTE + +with t as (select * from t1 where a <=3) +select * from t; + +with t as (select * from t1 where a <=3) +(select * from t); + +with t as (select * from t1 where a <=3) +((select * from t)); + +with t as ((select * from t1 where a <=3)) +select * from t; +with t as (((select * from t1 where a <=3))) +select * from t; + +--echo 4.2. tailed select with simple CTE + +with t as (select * from t1 where a <=3) +select * from t order by a; + +with t as (select * from t1 where a <=3) +(select * from t) order by a; + +with t as (select * from t1 where a <=3) +(select * from t) order by a desc limit 2; + +--echo 4.3. [tailed] select with tailed CTE + +with t as (select * from t1 where a >=2 order by a limit 2) +select * from t; + +with t as (((select * from t1 where a >=2) order by a desc) limit 2) +select * from t; + +with t as (select * from t1 where a >=2 order by a desc limit 2) +select * from t order by a; + +--echo 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; + +with t as (select * from t1 where a <=3) +(select a from t) union (select b from t2); + +with t as (select * from t1 where a <=3) +(select a from t) union (select b as a from t2) order by a desc; + +--echo 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; + +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; + +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; + +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; + +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); + +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); + +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; + + +--echo 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; + +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; + +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; + +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; + +--echo 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; + +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); + +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; + +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; + + +--echo # 5. single-row subquery in expression + +--echo # 5.1. [tailed] simple select in expression + +select (a+1) + b as r from t2; +select ((a+1) + b) as r from t2; +select (b + (select 1)) as r from t2; +select (select a from t1 where a <=3 order by a desc limit 1) as r from t2; + +select +(select a from t1 where a <=3 order by a desc limit 1) as r from t2; + +select (select 100) as r from t2; +select ((select 100)) as r from t2; +select (select 100) + t2.b as r from t2; +select ((select 100) + t2.b) as r from t2; + +--echo # 5.2. [tailed] TVC in expression + +select (values (200)) as r from t2; +select ((values (200))) as r from t2; +select (values (200)) + t2.b as r from t2; +select ((values (200)) + t2.b) as r from t2; +select (values (200), (300) order by 1 desc limit 1) as r from t2; +select ((values (200), (300)) order by 1 desc limit 1) as r from t2; +select (select * from t1 limit 1) as r from t2; +select (select * from t1 order by a limit 1) as r from t2; +select ((select * from t1 order by a limit 1)) as r from t2; +((select ((select * from t1 order by a limit 1)) as r from t2)); +select (select * from t1 order by a limit 1) + t2.b as r from t2; + +--echo # 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; + +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; + +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; + +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; + +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; + +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; + +--echo # 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; + +select +( with t as (select * from t1 where a <=3) + select a from t limit 1) + t2.b as r +from t2; + +select +t2.b +( with t as (select * from t1 where a <=3) + select a from t limit 1) as r +from t2; + +select +((( with t as (select * from t1 where a <=3) + select a from t limit 1) + t2.b)) as r +from t2; + +select +( with t as (select * from t1 where a <=3) + select a from t limit 1) + 100 as r +from t2; + +select +( with t as (select * from t1 where a <=3) + select a from t limit 1) + (select 100) as r +from t2; + +select +( with t as (select * from t1 where a <=3) + select a from t limit 1) + t2.b + (select 100) as r +from t2; + +select +( with t as (select * from t1 where a <=3) + select a from t limit 1 ) + (t2.b + (select 100)) as r +from t2; + +select +( with t as (select * from t1 where a <=3) + select a from t limit 1 ) + t2.b + (values (100)) as r +from t2; + +--echo # 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; + +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; + +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; + +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; + +--echo # 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; + +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; + +--echo # 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; + +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; + + +--echo # 6. subquery + +--echo # 6.1. TVC in IN subquery + +select a from t1 where a in (1,8,7); +select a from t1 where a in (values (1), (8), (7)); + +--echo # 6.2. simple select in IN subquery + +select a from t1 where a in (select a from t2 where a <= 3); +select a from t1 where a in ((select a from t2 where a <= 3)); + +--echo # 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); + +select a from t1 +where a in (select a from t1 where a<=2 union (select a from t2 where b>40)); + +select a from t1 +where a in ((select a from t1 where a<=2) union select a from t2 where b>40); + +select a from t1 +where a in ((select a from t1 where a<=2) union (select a from t2 where b>40)); + +--echo # 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)); + +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)); + +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)); + + +--echo # 6.5. NOT IN subquery + +select a from t1 where a not in (1,8,7); +select a from t1 where a not in (values (1), (8), (7)); +select a from t1 where a not in (select a from t2 where a <= 3); +select a from t1 where a not in ((select a from t2 where a <= 3)); + +select a from t1 +where a not in (select a from t1 where a<=2 + union + select a from t2 where b>40); + +select a from t1 +where a not in (select a from t1 where a<=2 + union + (select a from t2 where b>40)); + +select a from t1 +where a not in ((select a from t1 where a<=2) + union + select a from t2 where b>40); + +select a from t1 +where a not in ((select a from t1 where a<=2) + union + (select a from t2 where b>40)); + +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)); + +--echo # 6.6. IN subquery in expression + +select 1 in (select a from t1) as r, b from t2 where b > 30; +select (1 in (select a from t1)) as r, b from t2 where b > 30; +select 1 in ((select a from t1)) as r, b from t2 where b > 30; +select ((1 in ((select a from t1)))) as r, b from t2 where b > 30; +select ((1 in ((select a from t1)))) as r, b from t2 where b > 30; +select b, if (a in (select a from t1 where a > 3),10,20) as r from t2; +select b, if (a in ((select a from t1 where a > 3)),10,20) as r from t2; + +--echo # 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; +drop function f1; +delimiter |; +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 | +delimiter ;| +select b, f2(a) from t2 where b > 20; +drop function f2; + +--echo # 6.8. EXISTS subquery + +select exists (select a from t1 where t1.a=t2.a) as r, b from t2 where b > 30; +select exists ((select a from t1 where t1.a=t2.a)) as r, b from t2 where b > 30; +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)); +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)); + +--echo # 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; +drop function f1; + +delimiter |; +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 | +delimiter ;| +select b, f2(a) from t2 where b > 20; +drop function f2; + +--echo # 6.10. subquery with ANY + +select a from t1 where a = any(select a from t2 where a <= 3); +select a from t1 where a = any((select a from t2 where a <= 3)); + +select a from t1 +where a = any (select a from t1 where a<=2 + union + select a from t2 where b>40); + +select a from t1 +where a = any(select a from t1 where a<=2 + union + (select a from t2 where b>40)); + +select a from t1 +where a = any((select a from t1 where a<=2) + union + select a from t2 where b>40); + +select a from t1 +where a = any((select a from t1 where a<=2) + union + (select a from t2 where b>40)); + + +--echo # 7. create table as + +--echo # 7.1. create table as simple select + +create table t as select * from t1 where a <=3; +select * from t; +drop table t; + +create table t select * from t1 where a <=3; +select * from t; +drop table t; + +create table t as (select * from t1 where a <=3); +select * from t; +drop table t; + +create table t (select * from t1 where a <=3); +select * from t; +drop table t; + +create table t as ((select * from t1 where a <=3)); +select * from t; +drop table t; + +create table t ((select * from t1 where a <=3)); +select * from t; +drop table t; + +create table t(a decimal(10,2)) as select * from t1 where a <=3; +select * from t; +drop table t; + +create table t(a decimal(10,2)) select * from t1 where a <=3; +select * from t; +drop table t; + +create table t(a decimal(10,2)) as (select * from t1 where a <=3); +select * from t; +drop table t; + +create table t(a decimal(10,2)) (select * from t1 where a <=3); +select * from t; +drop table t; + +create table t(a decimal(10,2)) as ((select * from t1 where a <=3)); +select * from t; +drop table t; + +create table t(a decimal(10,2)) ((select * from t1 where a <=3)); +select * from t; +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; +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; +drop table t; + +--echo # 7.2. create table as tailed select + +create table t as select * from t1 where a <=3 order by 1; +select * from t; +drop table t; + +create table t select * from t1 where a <=3 order by 1; +select * from t; +drop table t; + +create table t as select * from t1 where a <=3 order by 1 desc limit 2; +select * from t; +drop table t; + +create table t select * from t1 where a <=3 order by 1 desc limit 2; +select * from t; +drop table t; + +create table t as ((select * from t1 where a <=3) order by 1 desc) limit 2; +select * from t; +drop table t; + +create table t ((select * from t1 where a <=3) order by 1 desc) limit 2; +select * from t; +drop table t; + +--echo # 7.3. create table as select wihout from clause + +create table t as select 10; +select * from t; +drop table t; + +create table t select 10; +select * from t; +drop table t; + +--echo # 7.4. create table as union of selects wihout from clause + +create table t as select 10 union select 70; +select * from t; +drop table t; + +create table t select 10 union select 70; +select * from t; +drop table t; + +--echo # 7.5. create table as TVC + +create table t as values (7), (3), (8); +select * from t; +drop table t; + +create table t values (7), (3), (8); +select * from t; +drop table t; + +create table t as (values (7), (3), (8)); +select * from t; +drop table t; + +create table t (values (7), (3), (8)); +select * from t; +drop table t; + +create table t as ((values (7), (3), (8))); +select * from t; +drop table t; + +create table t ((values (7), (3), (8))); +select * from t; +drop table t; + +--echo # 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; +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; +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; +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; +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; +drop table t; + +--echo # 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; +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; +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; +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; +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; +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; +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; +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; +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; +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; +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; +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; +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; +drop table t; + + +--echo # 8. insert + +create table t (c int, d int); + +--echo # 8.1. insert simple select + +insert into t select * from t2 where a <=3; +select * from t; +delete from t; + +insert into t(c) select t2.a from t2 where a <=3; +select * from t; +delete from t; + +insert into t (select * from t2 where a <=3); +select * from t; +delete from t; + +insert into t(c) (select t2.a from t2 where a <=3); +select * from t; +delete from t; + +insert into t ((select * from t2 where a <=3)); +select * from t; +delete from t; + +insert into t(c) ((select t2.a from t2 where a <=3)); +select * from t; +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; +delete from t; + +insert into t(c) select * from t1 where a <=3; +select * from t; +delete from t; + +insert into t (select * from t1 where a <=3); +select * from t; +delete from t; + +insert into t(c) (select * from t1 where a <=3); +select * from t; +delete from t; + +insert into t ((select * from t1 where a <=3)); +select * from t; +delete from t; + +insert into t(c) ((select * from t1 where a <=3)); +select * from t; +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; +delete from t; + +insert into t(a) ((select a from t2 where a <=3)); +select * from t; +delete from t; + +drop table t; +create table t(c int, d int); + +--echo # 8.2. insert tailed select + +insert into t select * from t2 where a <=3 order by 1; +select * from t; +delete from t; + +insert into t(c) select a from t2 where a <=3 order by 1; +select * from t; +delete from t; + +insert into t select * from t2 where a <=3 order by 1 desc limit 2; +select * from t; +delete from t; + +insert into t(c) select a from t2 where a <=3 order by 1 desc limit 2; +select * from t; +delete from t; + +insert into t ((select * from t2 where a <=3) order by 1 desc) limit 2; +select * from t; +delete from t; + +insert into t(c) ((select a from t2 where a <=3) order by 1 desc) limit 2; +select * from t; +delete from t; + +--echo # 8.3. insert select without from clause + +insert into t select 10, 20; +select * from t; +delete from t; + +insert into t(c) select 10; +select * from t; +delete from t; + +--echo # 8.4. insert union of selects without from clause + +insert into t select 10,20 union select 70,80; +select * from t; +delete from t; + +insert into t(c) select 10 union select 70; +select * from t; +delete from t; + +--echo # 8.5. insert TVC + +insert into t values (7,70), (3,30), (8,80); +select * from t; +delete from t; + +insert into t(c) values (7), (3), (8); +select * from t; +delete from t; + +insert into t (values (7,70), (3,30), (8,80)); +select * from t; +delete from t; + +insert into t(c) (values (7), (3), (8)); +select * from t; +delete from t; + +insert into t ((values (7,70), (3,30), (8,80))); +select * from t; +delete from t; + +insert into t(c) ((values (7), (3), (8))); +select * from t; +delete from t; + +--echo # 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; +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; +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; +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; +delete from t; + +--echo # 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; +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; +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; +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; +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; +delete from t; + +drop table t; + + +--echo # 9. derived table + +--echo # 9.1. derived table as [tailed] simple select + +select * from (select * from t1) as dt; +select * from ((select * from t1)) as dt; +select * from (((select * from t1))) as dt; +select * from (select * from t1 order by a) as dt; +select * from (select a from t1 order by a) as dt; +select * from (select a from t1 order by 1) as dt; +select * from (select a from t1 order by t1.a) as dt; +select * from ((select * from t1 order by t1.a limit 2)) as dt; +select * from ((select * from t2 order by a limit 2) order by b desc) dt; +select * from ((select a from t1 where a=1) order by 1 desc) dt; + +--echo # 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; + +select * from +((select * from t2 order by t2.a limit 2) order by b desc) as dt; + +select * from +(((select * from t2 order by t2.a limit 2) order by b desc )) as dt; + +select * from +(((select * from t2 order by t2.a) limit 2) order by b desc) dt; + +select * from +((select * from t2 order by a limit 2) order by b desc) dt; + +select * from +((select a from t1 where a=1) order by 1 desc) as dt; + +select * from +((select * from t2 order by t2.a limit 2) order by b desc) as dt; + + +--echo # 9.3. derived table as union + +select * from (select a from t1 union select a from t1) as dt; +select * from (select a from t1 union all select a from t1) as dt; +select * from (select a from t1 union select b from t2) as dt; + +select * from +((select a from t1) union (select a from t1)) as dt; + +select * from +((select a from t1) union (select b from t2)) as dt; + +select * from +(select a from t1 where a=1 union select a from t1 where a=3) dt; + +select * from +((select a from t1 where a=1) union select a from t1 where a=3) dt; + +select * from +(((select a from t1 where a=1) union select a from t1 where a=3)) dt; + +select * from +(((select a from t1 where a<=3) union (select a from t1 where a=3))) as dt; + +select * from +(select a from t1 where a=1 union (select a from t1 where a=3)) as dt; + +select * from +((select a from t1 where a=1 union (select a from t1 where a=3))) as dt; + +select * from +(((select a from t1 where a=1 union (select a from t1 where a=3)))) as dt; + +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; + +select * from +( (select a from t1 where a=1 order by a) + union + select a from t1 where a=3 ) as dt; + +select * from +( (select a from t1 where a!=3 order by a desc) + union + select a from t1 where a=3 ) as dt; + +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; + +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; + +select * from +( select a from t1 where a=1 + union + select a from t1 where a=3 + order by a desc) as dt; + +select *from +( (select a from t1 limit 2) + union + select a from t1 where a=3 + order by a desc) as dt; + +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; + +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; + +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; + +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; + +select * from +( (select a from t1 where a=1) + union + (select a from t1 where a=3) + order by a desc ) as dt; + +select * from +( ( select a from t1 where a=1 + union + select a from t1 where a=3 ) + order by a desc ) as dt; + +select * from +( ( ( select a from t1 where a=1 ) + union + ( select a from t1 where a=3 ) ) + order by a desc ) as dt; + +select * from +( ( select a from t1 where a=1 + union + select a from t1 where a=3 ) + order by 1 desc ) as dt; + +select * from +( ( (select a from t1 where a=1 + union + select a from t1 where a=3) ) order by 1 desc ) as dt; + +select * from +((((select a from t1 where a=1) union (select a from t1 where a=3))) + order by 1 desc ) as dt; + +select * from +( ( (select a from t1 where a=1 ) + union + (select a from t1 where a=3) ) + order by 1 desc ) as dt; + +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; + +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; + +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; + +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; + +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; + +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; + +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; + +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; + +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; + +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; + +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; + +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; + +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; + +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; + +--echo # 9.3. derived table as [tailed] TVC + +select * from +( values (3), (7), (1) ) as dt; + +select * from +( (values (3), (7), (1)) ) as dt; + +select * from +(((values (3), (7), (1)))) as dt; + +select * from +( values (3), (7), (1) order by 1 limit 2 ) as dt; + +select * from +( (values (3), (7), (1)) order by 1 limit 2 ) as dt; + +select * from +( ((values (3), (7), (1))) order by 1 limit 2 ) as dt; + +select * from +( (((values (3), (7), (1))) order by 1 limit 2) ) as dt; + +select * from +( ( (values (3), (7), (1) limit 2) order by 1 desc) ) as dt; + +select * from +( ((values (3), (7), (1)) order by 1 desc) limit 2 ) as dt; + +select * from +( (((values (3), (7), (1)) order by 1 desc) limit 2) ) as dt; + +--echo # 9.3. derived table as union of TVCs + +select * from +( values (3), (7), (1) union values (3), (4), (2) ) dt; + +select * from +( values (3), (7), (1) union all values (3), (4), (2) ) as dt; + +select * from +( values (3), (7), (1) union values (3), (4), (2) ) as dt; + +select * from +( values (3), (7), (1) except values (3), (4), (2) ) as dt; + +select * from +( (values (3), (7), (1)) union (values (3), (4), (2)) ) as dt; + +select * from +( (values (3), (7), (1)) + union + (values (3), (4), (2)) + union values (5), (7) ) dt; + +select * from +( (values (3), (7), (1)) + union + (values (3), (4), (2)) + union + (values (5), (7)) ) as dt; + +select * from +( (values (3), (7), (1) + union + values (3), (4), (2)) + union + values (5), (7) ) as dt; + +select * from +( values (3), (7), (1) + union (values (3), (4), (2) + union + values (5), (7)) ) as dt; + +select * from +( (values (3), (7), (1) + union + ((values (3), (4), (2) + union values (5), (7)))) ) dt; + +select * from +( values (3), (7), (1) + union + values (3), (4), (2) + order by 1 ) as dt; + +select * from +( (values (3), (7), (1) union values (3), (4), (2)) order by 1 ) as dt; + +select * from +( (values (3), (7), (1) union values (3), (4), (2)) order by 1 ) as dt; + +select * from +( values (3), (7), (1) union (values (3), (4), (2)) order by 1 ) as dt; + +select * from +( (values (3), (7), (1) union values (3), (4), (2)) order by 1 ) as dt; + +select * from +( ((values (3), (7), (1)) union values (3), (4), (2)) order by 1 ) as dt; + +select * from +( (values (3), (7), (1) order by 1 limit 2) + union + (values (3), (4), (2) order by 1 desc limit 2) ) as dt; + +select * from +( ((values (3), (7), (1) order by 1) limit 2) + union + ((values (3), (4), (2) order by 1 desc) limit 2) ) as dt; + +select * from +( (((values (3), (7), (1)) order by 1) limit 2) + union + (((values (3), (4), (2)) order by 1 desc) limit 2) ) as dt; + +select * from +( (values (3), (7), (1) order by 1 limit 2) + union + values (3), (4), (2) + order by 1 limit 3 ) as dt; + +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; + +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; + +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; + +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; + +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; + +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; + +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; + +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; + +select * from +( (((values (3), (4), (2)) order by 1 desc) limit 2) ) as dt; + +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; + +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; + +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; + +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; + +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; + +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; + + +--echo # 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; + +select * from +( with t as (select * from t1 where a <=3) + (select * from t) ) as dt; + +select * from +( with t as (select * from t1 where a <=3) + ((select * from t)) ) as dt; + +select * from +( with t as ((select * from t1 where a <=3)) + select * from t ) as dt; + +select * from +( with t as (((select * from t1 where a <=3))) + select * from t ) as dt; + +select * from +( with t as (select * from t1 where a <=3) + select * from t order by a ) as dt; + +select * from +( with t as (select * from t1 where a <=3) + (select * from t) order by a ) as dt; + +select * from +( with t as (select * from t1 where a <=3) + (select * from t) order by a desc limit 2 ) as dt; + +select * from +( with t as (select * from t1 where a >=2 order by a limit 2) + select * from t ) as dt; + +select * from +( with t as (((select * from t1 where a >=2) order by a desc) limit 2) + select * from t ) as dt; + +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; + +--echo # 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; + +select * from +( with t as (select * from t1 where a <=3) + (select a from t) union (select b from t2) ) as dt; + +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; + +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; + +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; + +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; + +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; + +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; + +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; + +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; + +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; + +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; + +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; + +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; + +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; + +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; + +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; + + +--echo 10. view + +--echo 10.1. view as simple select + +create view v1 as +select * from t1; +show create view v1; +select * from v1; +drop view v1; + +create view v1 as +select 2*a as c from t1; +show create view v1; +select * from v1; +drop view v1; + +create view v1(c) as +select 2*a from t1; +show create view v1; +select * from v1; +drop view v1; + +create view v1 as +((select * from t1)); +show create view v1; +select * from v1; +drop view v1; + +--echo 10.2. view as tailed simple select + +create view v1 as +select * from t1 order by a; +show create view v1; +select * from v1; +drop view v1; + +create view v1 as +(select * from t2 order by a limit 2) order by b desc; +show create view v1; +select * from v1; +drop view v1; + +--echo 10.3. view as union + +create view v1 as +select a from t1 union select b from t2; +show create view v1; +select * from v1; +drop view v1; + +create view v1 as +(select a from t1) union (select b from t2); +show create view v1; +select * from v1; +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; +select * from v1; +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; +select * from v1; +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; +select * from v1; +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; +select * from v1; +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; +select * from v1; +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; +select * from v1; +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; +select * from v1; +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; +select * from v1; +drop view v1; + +--echo 10.4. view as [tailed] TVC + +create view v1 as +values (3), (7), (1); +show create view v1; +select * from v1; +drop view v1; +create view v1 as +(((values (3), (7), (1))) order by 1); +show create view v1; +select * from v1; +drop view v1; + +--echo 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; +select * from v1; +drop view v1; +create view v1 as +(values (3), (7), (1) union values (3), (4), (2)) order by 1; +show create view v1; +select * from v1; +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; +select * from v1; +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; +select * from v1; +drop view v1; + +--echo 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; +select * from v1; +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; +select * from v1; +drop view v1; + +--echo 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; +select * from v1; +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; +select * from v1; +drop view v1; + +--echo 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; +select * from v1; +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; +select * from v1; +drop view v1; + +create view v1 as +with t(a) as (values (2), (1)) select a from t; +show create view v1; +select * from v1; +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; +select * from v1; +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; +select * from v1; +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; +select * from v1; +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; +select * from v1; +drop view v1; + +drop table t1,t2; + --echo # End of 10.4 tests diff --git a/mysql-test/main/except.result b/mysql-test/main/except.result index bac3d3ddaa5..f48a25f93bd 100644 --- a/mysql-test/main/except.result +++ b/mysql-test/main/except.result @@ -25,7 +25,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 EXCEPT t2 ALL NULL NULL NULL NULL 2 100.00 NULL EXCEPT RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 /* select#1 */ select `a`.`a` AS `a`,`a`.`b` AS `b` from (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` except (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`)) `a` +Note 1003 /* select#1 */ select `a`.`a` AS `a`,`a`.`b` AS `b` from ((/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) except (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`)) `a` EXPLAIN format=json (select a,b from t1) except (select c,d from t2); EXPLAIN { @@ -230,7 +230,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 EXCEPT t4 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) NULL EXCEPT RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 /* select#1 */ select `a`.`a` AS `a`,`a`.`b` AS `b`,`a`.`e` AS `e`,`a`.`f` AS `f` from (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` join `test`.`t3` except (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t4`.`g` AS `g`,`test`.`t4`.`h` AS `h` from `test`.`t2` join `test`.`t4`)) `a` +Note 1003 /* select#1 */ select `a`.`a` AS `a`,`a`.`b` AS `b`,`a`.`e` AS `e`,`a`.`f` AS `f` from ((/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` join `test`.`t3`) except (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t4`.`g` AS `g`,`test`.`t4`.`h` AS `h` from `test`.`t2` join `test`.`t4`)) `a` EXPLAIN format=json (select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4); EXPLAIN { diff --git a/mysql-test/main/intersect.result b/mysql-test/main/intersect.result index 6cf0f476970..5990fcce110 100644 --- a/mysql-test/main/intersect.result +++ b/mysql-test/main/intersect.result @@ -39,7 +39,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 INTERSECT t3 ALL NULL NULL NULL NULL 3 100.00 NULL INTERSECT RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 /* select#1 */ select `a`.`a` AS `a`,`a`.`b` AS `b` from (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` intersect (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect (/* select#4 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `a` +Note 1003 /* select#1 */ select `a`.`a` AS `a`,`a`.`b` AS `b` from ((/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) intersect (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect (/* select#4 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `a` EXPLAIN format=json (select a,b from t1) intersect (select c,d from t2) intersect (select e,f from t3); EXPLAIN { @@ -280,7 +280,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 INTERSECT t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) NULL INTERSECT RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 /* select#1 */ select `a`.`a` AS `a`,`a`.`b` AS `b` from (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` intersect (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t3`.`e` AS `e` from `test`.`t2` join `test`.`t3`)) `a` +Note 1003 /* select#1 */ select `a`.`a` AS `a`,`a`.`b` AS `b` from ((/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) intersect (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t3`.`e` AS `e` from `test`.`t2` join `test`.`t3`)) `a` set @@optimizer_switch='optimize_join_buffer_size=off'; EXPLAIN format=json (select a,b from t1) intersect (select c,e from t2,t3); EXPLAIN @@ -724,7 +724,7 @@ a b drop procedure p1; 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`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union select `__6`.`c` AS `c`,`__6`.`d` AS `d` from (select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` intersect (select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__6` union (select 4 AS `4`,4 AS `4`) latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union select `__6`.`c` AS `c`,`__6`.`d` AS `d` from ((select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect (select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__6` union (select 4 AS `4`,4 AS `4`) latin1 latin1_swedish_ci drop view v1; drop tables t1,t2,t3; # diff --git a/mysql-test/main/parser.result b/mysql-test/main/parser.result index 2b4a0bb7163..b39f496e3db 100644 --- a/mysql-test/main/parser.result +++ b/mysql-test/main/parser.result @@ -1776,7 +1776,7 @@ End of 10.3 tests # create table t1 (a int); (select * from t1) for update; -ERROR HY000: Incorrect usage of lock options and SELECT in brackets +a (select * from t1) union (select * from t1) for update; ERROR HY000: Incorrect usage of lock options and SELECT in brackets (select * from t1 for update); diff --git a/mysql-test/main/parser.test b/mysql-test/main/parser.test index b3cfcfb4fcc..c6e9f13cdaf 100644 --- a/mysql-test/main/parser.test +++ b/mysql-test/main/parser.test @@ -1544,7 +1544,6 @@ SELECT @@GLOBAL.role; --echo # create table t1 (a int); ---error ER_WRONG_USAGE (select * from t1) for update; --error ER_WRONG_USAGE (select * from t1) union (select * from t1) for update; diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index f2836d36c80..349e7dca129 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -3736,8 +3736,11 @@ WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); i explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12)) from t1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'union (select t12.i from t1 t12)) -from t1' at line 1 +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 Const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table +NULL UNION RESULT ALL NULL NULL NULL NULL NULL explain select * from t1 where not exists ((select t11.i from t1 t11) union (select t12.i from t1 t12)); id select_type table type possible_keys key key_len ref rows Extra @@ -5305,7 +5308,7 @@ SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ) 1 SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) 1 @@ -5335,7 +5338,8 @@ SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); a 1 SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1 +a +1 SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); a 1 @@ -5343,7 +5347,8 @@ SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); a 1 SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1 +a +1 SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); a 1 diff --git a/mysql-test/main/subselect.test b/mysql-test/main/subselect.test index 53773eb5e1e..be17254202e 100644 --- a/mysql-test/main/subselect.test +++ b/mysql-test/main/subselect.test @@ -2611,8 +2611,6 @@ SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS SELECT * FROM t1 WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); -#TODO:not supported ---error ER_PARSE_ERROR explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12)) from t1; @@ -4414,11 +4412,9 @@ SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); ---error ER_PARSE_ERROR SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); ---error ER_PARSE_ERROR SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result index 1cf1a3373a3..84c415d1ce1 100644 --- a/mysql-test/main/subselect_no_exists_to_in.result +++ b/mysql-test/main/subselect_no_exists_to_in.result @@ -3739,8 +3739,11 @@ WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); i explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12)) from t1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'union (select t12.i from t1 t12)) -from t1' at line 1 +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 Const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table +NULL UNION RESULT ALL NULL NULL NULL NULL NULL explain select * from t1 where not exists ((select t11.i from t1 t11) union (select t12.i from t1 t12)); id select_type table type possible_keys key key_len ref rows Extra @@ -5307,7 +5310,7 @@ SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ) 1 SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) 1 @@ -5337,7 +5340,8 @@ SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); a 1 SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1 +a +1 SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); a 1 @@ -5345,7 +5349,8 @@ SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); a 1 SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1 +a +1 SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); a 1 diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index 92ddbe34b6f..93035e235f7 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -3739,8 +3739,11 @@ WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); i explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12)) from t1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'union (select t12.i from t1 t12)) -from t1' at line 1 +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 Const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table +NULL UNION RESULT ALL NULL NULL NULL NULL NULL explain select * from t1 where not exists ((select t11.i from t1 t11) union (select t12.i from t1 t12)); id select_type table type possible_keys key key_len ref rows Extra @@ -5305,7 +5308,7 @@ SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ) 1 SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) 1 @@ -5335,7 +5338,8 @@ SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); a 1 SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1 +a +1 SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); a 1 @@ -5343,7 +5347,8 @@ SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); a 1 SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1 +a +1 SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); a 1 diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result index a2788715194..09f664d3c28 100644 --- a/mysql-test/main/subselect_no_opts.result +++ b/mysql-test/main/subselect_no_opts.result @@ -3735,8 +3735,11 @@ WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); i explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12)) from t1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'union (select t12.i from t1 t12)) -from t1' at line 1 +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 Const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table +NULL UNION RESULT ALL NULL NULL NULL NULL NULL explain select * from t1 where not exists ((select t11.i from t1 t11) union (select t12.i from t1 t12)); id select_type table type possible_keys key key_len ref rows Extra @@ -5301,7 +5304,7 @@ SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ) 1 SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) 1 @@ -5331,7 +5334,8 @@ SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); a 1 SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1 +a +1 SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); a 1 @@ -5339,7 +5343,8 @@ SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); a 1 SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1 +a +1 SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); a 1 diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index f5c5a1dead6..765bb15a3df 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -3742,8 +3742,11 @@ WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); i explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12)) from t1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'union (select t12.i from t1 t12)) -from t1' at line 1 +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 Const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table +NULL UNION RESULT ALL NULL NULL NULL NULL NULL explain select * from t1 where not exists ((select t11.i from t1 t11) union (select t12.i from t1 t12)); id select_type table type possible_keys key key_len ref rows Extra @@ -5311,7 +5314,7 @@ SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ) 1 SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) 1 @@ -5341,7 +5344,8 @@ SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); a 1 SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1 +a +1 SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); a 1 @@ -5349,7 +5353,8 @@ SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); a 1 SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1 +a +1 SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); a 1 diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result index bef256f789f..97d2f3b058f 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -3735,8 +3735,11 @@ WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); i explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12)) from t1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'union (select t12.i from t1 t12)) -from t1' at line 1 +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 Const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table +NULL UNION RESULT ALL NULL NULL NULL NULL NULL explain select * from t1 where not exists ((select t11.i from t1 t11) union (select t12.i from t1 t12)); id select_type table type possible_keys key key_len ref rows Extra @@ -5301,7 +5304,7 @@ SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ) 1 SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) 1 @@ -5331,7 +5334,8 @@ SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); a 1 SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1 +a +1 SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); a 1 @@ -5339,7 +5343,8 @@ SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); a 1 SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1 +a +1 SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); a 1 diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index dd1ffd20b47..86c607fb894 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -122,7 +122,8 @@ void Item_subselect::init(st_select_lex *select_lex, parsing_place= (outer_select->in_sum_expr ? NO_MATTER : outer_select->parsing_place); - if (unit->is_unit_op() && unit->first_select()->next_select()) + if (unit->is_unit_op() && + (unit->first_select()->next_select() or unit->fake_select_lex)) engine= new subselect_union_engine(unit, result, this); else engine= new subselect_single_select_engine(select_lex, result, this); diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 7952b2a267e..826e7a6def0 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -1444,7 +1444,7 @@ int Lex_input_stream::lex_token(YYSTYPE *yylval, THD *thd) return LEFT_PAREN_LIKE; if (token == WITH) return LEFT_PAREN_WITH; - if (token != left_paren && token != SELECT_SYM) + if (token != left_paren && token != SELECT_SYM && token != VALUES) return LEFT_PAREN_ALT; else return left_paren; @@ -5339,10 +5339,9 @@ LEX::create_unit(SELECT_LEX *first_sel) SELECT_LEX_UNIT *unit; DBUG_ENTER("LEX::create_unit"); - if (first_sel->master_unit()) - DBUG_RETURN(first_sel->master_unit()); + unit = first_sel->master_unit(); - if (!(unit= alloc_unit())) + if (!unit && !(unit= alloc_unit())) DBUG_RETURN(NULL); unit->register_select_chain(first_sel); @@ -9001,7 +9000,8 @@ bool LEX::insert_select_hack(SELECT_LEX *sel) builtin_select.link_prev= NULL; // indicator of removal } - set_main_unit(sel->master_unit()); + if (set_main_unit(sel->master_unit())) + return true; DBUG_ASSERT(builtin_select.table_list.elements == 1); TABLE_LIST *insert_table= builtin_select.table_list.first; @@ -9045,9 +9045,10 @@ bool LEX::insert_select_hack(SELECT_LEX *sel) } -/* +/** Create an Item_singlerow_subselect for a query expression. */ + Item *LEX::create_item_query_expression(THD *thd, st_select_lex_unit *unit) { @@ -9062,118 +9063,17 @@ Item *LEX::create_item_query_expression(THD *thd, SELECT_LEX *curr_sel= select_stack_head(); DBUG_ASSERT(current_select == curr_sel); if (!curr_sel) + { curr_sel= &builtin_select; - curr_sel->register_unit(unit, &curr_sel->context); - curr_sel->add_statistics(unit); + curr_sel->register_unit(unit, &curr_sel->context); + curr_sel->add_statistics(unit); + } return new (thd->mem_root) Item_singlerow_subselect(thd, unit->first_select()); } -/** - Process unit parsed in brackets -*/ - -bool LEX::parsed_unit_in_brackets(SELECT_LEX_UNIT *unit) -{ - SELECT_LEX *first_in_nest= unit->pre_last_parse->next_select()->first_nested; - if (first_in_nest->first_nested != first_in_nest) - { - /* There is a priority jump starting from first_in_nest */ - if (create_priority_nest(first_in_nest) == NULL) - return true; - unit->fix_distinct(); - } - push_select(unit->fake_select_lex); - return false; -} - - - -/** - Process tail of unit parsed in brackets -*/ -SELECT_LEX *LEX::parsed_unit_in_brackets_tail(SELECT_LEX_UNIT *unit, - Lex_order_limit_lock * l) -{ - pop_select(); - if (l) - { - (l)->set_to(unit->fake_select_lex); - } - return unit->first_select(); -} - - -/** - Process select parsed in brackets -*/ - -SELECT_LEX *LEX::parsed_select(SELECT_LEX *sel, Lex_order_limit_lock * l) -{ - pop_select(); - if (l) - { - if (sel->next_select()) - { - SELECT_LEX_UNIT *unit= sel->master_unit(); - if (!unit) - unit= create_unit(sel); - if (!unit) - return NULL; - if (!unit->fake_select_lex->is_set_query_expr_tail) - l->set_to(unit->fake_select_lex); - else - { - if (!l->order_list && !unit->fake_select_lex->explicit_limit) - { - sel= unit->fake_select_lex; - l->order_list= &sel->order_list; - } - else - sel= wrap_unit_into_derived(unit); - if (!sel) - return NULL; - l->set_to(sel); - } - } - else if (!sel->is_set_query_expr_tail) - { - l->set_to(sel); - } - else - { - if (!l->order_list && !sel->explicit_limit) - l->order_list= &sel->order_list; - else - { - SELECT_LEX_UNIT *unit= create_unit(sel); - if (!unit) - return NULL; - sel= wrap_unit_into_derived(unit); - } - if (!sel) - return NULL; - l->set_to(sel); - } - } - return sel; -} - - -/** - Process select parsed in brackets -*/ - -SELECT_LEX *LEX::parsed_select_in_brackets(SELECT_LEX *sel, - Lex_order_limit_lock * l) -{ - sel->braces= TRUE; - return parsed_select(sel, l); -} - - SELECT_LEX_UNIT *LEX::parsed_select_expr_start(SELECT_LEX *s1, SELECT_LEX *s2, enum sub_select_type unit_type, bool distinct) @@ -9204,6 +9104,7 @@ SELECT_LEX_UNIT *LEX::parsed_select_expr_start(SELECT_LEX *s1, SELECT_LEX *s2, if (res == NULL) return NULL; res->pre_last_parse= sel1; + push_select(res->fake_select_lex); return res; } @@ -9216,12 +9117,6 @@ SELECT_LEX_UNIT *LEX::parsed_select_expr_cont(SELECT_LEX_UNIT *unit, SELECT_LEX *sel1; if (!s2->next_select()) sel1= s2; - else - { - sel1= wrap_unit_into_derived(s2->master_unit()); - if (!sel1) - return NULL; - } SELECT_LEX *last= unit->pre_last_parse->next_select(); int cmp= oracle? 0 : cmp_unit_op(unit_type, last->get_linkage()); @@ -9253,41 +9148,73 @@ SELECT_LEX_UNIT *LEX::parsed_select_expr_cont(SELECT_LEX_UNIT *unit, return unit; } + /** - Process parsed select in body + Add primary expression as the next term in a given query expression body + pruducing a new query expression body */ -SELECT_LEX_UNIT *LEX::parsed_body_select(SELECT_LEX *sel, - Lex_order_limit_lock * l) +SELECT_LEX_UNIT * +LEX::add_primary_to_query_expression_body(SELECT_LEX_UNIT *unit, + SELECT_LEX *sel, + enum sub_select_type unit_type, + bool distinct, + bool oracle) { - if (sel->braces && l && l->lock.defined_lock) + SELECT_LEX *sel2= sel; + if (sel->master_unit() && sel->master_unit()->first_select()->next_select()) { - my_error(ER_WRONG_USAGE, MYF(0), "lock options", - "SELECT in brackets"); - return NULL; + sel2= wrap_unit_into_derived(sel->master_unit()); + if (!sel2) + return NULL; } - if (!(sel= parsed_select(sel, l))) - return NULL; + SELECT_LEX *sel1= unit->first_select(); + if (!sel1->next_select()) + unit= parsed_select_expr_start(sel1, sel2, unit_type, distinct); + else + unit= parsed_select_expr_cont(unit, sel2, unit_type, distinct, oracle); + return unit; +} - SELECT_LEX_UNIT *res= create_unit(sel); - if (res && sel->tvc && sel->order_list.elements) + +/** + Add query primary to a parenthesized query primary + pruducing a new query expression body +*/ + +SELECT_LEX_UNIT * +LEX::add_primary_to_query_expression_body_ext_parens( + SELECT_LEX_UNIT *unit, + SELECT_LEX *sel, + enum sub_select_type unit_type, + bool distinct) +{ + SELECT_LEX *sel1= unit->first_select(); + if (unit->first_select()->next_select()) { - if (res->add_fake_select_lex(thd)) + sel1= wrap_unit_into_derived(unit); + if (!sel1) + return NULL; + if (!create_unit(sel1)) return NULL; - SELECT_LEX *fake= res->fake_select_lex; - fake->order_list= sel->order_list; - fake->explicit_limit= sel->explicit_limit; - fake->select_limit= sel->select_limit; - fake->offset_limit= sel->offset_limit; } - return res; + SELECT_LEX *sel2= sel; + if (sel->master_unit() && sel->master_unit()->first_select()->next_select()) + { + sel2= wrap_unit_into_derived(sel->master_unit()); + if (!sel2) + return NULL; + } + unit= parsed_select_expr_start(sel1, sel2, unit_type, distinct); + return unit; } + /** - Process parsed unit in body + Process multi-operand query expression body */ -bool LEX::parsed_body_unit(SELECT_LEX_UNIT *unit) +bool LEX::parsed_multi_operand_query_expression_body(SELECT_LEX_UNIT *unit) { SELECT_LEX *first_in_nest= unit->pre_last_parse->next_select()->first_nested; @@ -9298,27 +9225,60 @@ bool LEX::parsed_body_unit(SELECT_LEX_UNIT *unit) return true; unit->fix_distinct(); } - push_select(unit->fake_select_lex); return false; } + /** - Process parsed tail of unit in body + Add non-empty tail to a query expression body +*/ - TODO: make processing for double tail case +SELECT_LEX_UNIT *LEX::add_tail_to_query_expression_body(SELECT_LEX_UNIT *unit, + Lex_order_limit_lock *l) +{ + DBUG_ASSERT(l != NULL); + pop_select(); + SELECT_LEX *sel= unit->first_select()->next_select() ? unit->fake_select_lex : + unit->first_select(); + l->set_to(sel); + return unit; +} + + +/** + Add non-empty tail to a parenthesized query primary */ -SELECT_LEX_UNIT *LEX::parsed_body_unit_tail(SELECT_LEX_UNIT *unit, - Lex_order_limit_lock * l) +SELECT_LEX_UNIT * +LEX::add_tail_to_query_expression_body_ext_parens(SELECT_LEX_UNIT *unit, + Lex_order_limit_lock *l) { + SELECT_LEX *sel= unit->first_select()->next_select() ? unit->fake_select_lex : + unit->first_select(); + + DBUG_ASSERT(l != NULL); + pop_select(); - if (l) + if (sel->is_set_query_expr_tail) { - (l)->set_to(unit->fake_select_lex); + if (!l->order_list && !sel->explicit_limit) + l->order_list= &sel->order_list; + else + { + if (!unit) + return NULL; + sel= wrap_unit_into_derived(unit); + if (!sel) + return NULL; + if (!create_unit(sel)) + return NULL; + } } - return unit; + l->set_to(sel); + return sel->master_unit(); } + /** Process subselect parsing */ @@ -9345,7 +9305,6 @@ SELECT_LEX *LEX::parsed_subselect(SELECT_LEX_UNIT *unit) } - /** Process INSERT-like select */ @@ -9400,40 +9359,8 @@ SELECT_LEX *LEX::parsed_TVC_end() } -TABLE_LIST *LEX::parsed_derived_select(SELECT_LEX *sel, int for_system_time, - LEX_CSTRING *alias) -{ - TABLE_LIST *res; - derived_tables|= DERIVED_SUBQUERY; - sel->set_linkage(DERIVED_TABLE_TYPE); - sel->braces= FALSE; - // Add the subtree of subquery to the current SELECT_LEX - SELECT_LEX *curr_sel= select_stack_head(); - DBUG_ASSERT(current_select == curr_sel); - SELECT_LEX_UNIT *unit= sel->master_unit(); - if (!unit) - { - unit= create_unit(sel); - if (!unit) - return NULL; - } - curr_sel->register_unit(unit, &curr_sel->context); - curr_sel->add_statistics(unit); - - Table_ident *ti= new (thd->mem_root) Table_ident(unit); - if (ti == NULL) - return NULL; - if (!(res= curr_sel->add_table_to_list(thd, ti, alias, 0, - TL_READ, MDL_SHARED_READ))) - return NULL; - if (for_system_time) - { - res->vers_conditions= vers_conditions; - } - return res; -} -TABLE_LIST *LEX::parsed_derived_unit(SELECT_LEX_UNIT *unit, +TABLE_LIST *LEX::parsed_derived_table(SELECT_LEX_UNIT *unit, int for_system_time, LEX_CSTRING *alias) { @@ -9444,8 +9371,6 @@ TABLE_LIST *LEX::parsed_derived_unit(SELECT_LEX_UNIT *unit, // Add the subtree of subquery to the current SELECT_LEX SELECT_LEX *curr_sel= select_stack_head(); DBUG_ASSERT(current_select == curr_sel); - curr_sel->register_unit(unit, &curr_sel->context); - curr_sel->add_statistics(unit); Table_ident *ti= new (thd->mem_root) Table_ident(unit); if (ti == NULL) @@ -9463,7 +9388,8 @@ TABLE_LIST *LEX::parsed_derived_unit(SELECT_LEX_UNIT *unit, bool LEX::parsed_create_view(SELECT_LEX_UNIT *unit, int check) { SQL_I_List *save= &first_select_lex()->table_list; - set_main_unit(unit); + if (set_main_unit(unit)) + return true; if (check_main_unit_semantics()) return true; first_select_lex()->table_list.push_front(save); @@ -9486,7 +9412,8 @@ bool LEX::select_finalize(st_select_lex_unit *expr) sql_command= SQLCOM_SELECT; selects_allow_into= TRUE; selects_allow_procedure= TRUE; - set_main_unit(expr); + if (set_main_unit(expr)) + return true; return check_main_unit_semantics(); } @@ -9497,6 +9424,7 @@ bool LEX::select_finalize(st_select_lex_unit *expr, Lex_select_lock l) select_finalize(expr); } + /* "IN" and "EXISTS" subselect can appear in two statement types: @@ -9529,7 +9457,6 @@ void LEX::relink_hack(st_select_lex *select_lex) } - bool SELECT_LEX_UNIT::set_lock_to_the_last_select(Lex_select_lock l) { if (l.defined_lock) diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 8296f9e2dec..8d09407606a 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -4414,9 +4414,6 @@ public: insert_list= 0; } - bool make_select_in_brackets(SELECT_LEX* dummy_select, - SELECT_LEX *nselect, bool automatic); - SELECT_LEX_UNIT *alloc_unit(); SELECT_LEX *alloc_select(bool is_select); SELECT_LEX_UNIT *create_unit(SELECT_LEX*); @@ -4426,7 +4423,7 @@ public: bool insert_select_hack(SELECT_LEX *sel); SELECT_LEX *create_priority_nest(SELECT_LEX *first_in_nest); - void set_main_unit(st_select_lex_unit *u) + bool set_main_unit(st_select_lex_unit *u) { unit.options= u->options; unit.uncacheable= u->uncacheable; @@ -4436,16 +4433,10 @@ public: unit.union_distinct= u->union_distinct; unit.set_with_clause(u->with_clause); builtin_select.exclude_from_global(); + return false; } bool check_main_unit_semantics(); - // reaction on different parsed parts (bodies are in sql_yacc.yy) - bool parsed_unit_in_brackets(SELECT_LEX_UNIT *unit); - SELECT_LEX *parsed_select(SELECT_LEX *sel, Lex_order_limit_lock * l); - SELECT_LEX *parsed_unit_in_brackets_tail(SELECT_LEX_UNIT *unit, - Lex_order_limit_lock * l); - SELECT_LEX *parsed_select_in_brackets(SELECT_LEX *sel, - Lex_order_limit_lock * l); SELECT_LEX_UNIT *parsed_select_expr_start(SELECT_LEX *s1, SELECT_LEX *s2, enum sub_select_type unit_type, bool distinct); @@ -4453,20 +4444,35 @@ public: SELECT_LEX *s2, enum sub_select_type unit_type, bool distinct, bool oracle); - SELECT_LEX_UNIT *parsed_body_select(SELECT_LEX *sel, - Lex_order_limit_lock * l); - bool parsed_body_unit(SELECT_LEX_UNIT *unit); - SELECT_LEX_UNIT *parsed_body_unit_tail(SELECT_LEX_UNIT *unit, - Lex_order_limit_lock * l); + bool parsed_multi_operand_query_expression_body(SELECT_LEX_UNIT *unit); + SELECT_LEX_UNIT *add_tail_to_query_expression_body(SELECT_LEX_UNIT *unit, + Lex_order_limit_lock *l); + SELECT_LEX_UNIT * + add_tail_to_query_expression_body_ext_parens(SELECT_LEX_UNIT *unit, + Lex_order_limit_lock *l); + SELECT_LEX_UNIT *parsed_body_ext_parens_primary(SELECT_LEX_UNIT *unit, + SELECT_LEX *primary, + enum sub_select_type unit_type, + bool distinct); + SELECT_LEX_UNIT * + add_primary_to_query_expression_body(SELECT_LEX_UNIT *unit, + SELECT_LEX *sel, + enum sub_select_type unit_type, + bool distinct, + bool oracle); + SELECT_LEX_UNIT * + add_primary_to_query_expression_body_ext_parens( + SELECT_LEX_UNIT *unit, + SELECT_LEX *sel, + enum sub_select_type unit_type, + bool distinct); SELECT_LEX *parsed_subselect(SELECT_LEX_UNIT *unit); bool parsed_insert_select(SELECT_LEX *firs_select); bool parsed_TVC_start(); SELECT_LEX *parsed_TVC_end(); - TABLE_LIST *parsed_derived_select(SELECT_LEX *sel, int for_system_time, - LEX_CSTRING *alias); - TABLE_LIST *parsed_derived_unit(SELECT_LEX_UNIT *unit, - int for_system_time, - LEX_CSTRING *alias); + TABLE_LIST *parsed_derived_table(SELECT_LEX_UNIT *unit, + int for_system_time, + LEX_CSTRING *alias); bool parsed_create_view(SELECT_LEX_UNIT *unit, int check); bool select_finalize(st_select_lex_unit *expr); bool select_finalize(st_select_lex_unit *expr, Lex_select_lock l); diff --git a/sql/sql_table.cc b/sql/sql_table.cc index cf1be27ef3b..9353ec33bbe 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -11261,7 +11261,7 @@ bool Sql_cmd_create_table_like::execute(THD *thd) } #endif - if (select_lex->item_list.elements) // With select + if (select_lex->item_list.elements || select_lex->tvc) // With select or TVC { select_result *result; diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index bf72a8515f1..e7379a77049 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -599,8 +599,8 @@ static bool create_tvc_name(THD *thd, st_select_lex *parent_select, bool table_value_constr::to_be_wrapped_as_with_tail() { - return select_lex->master_unit()->first_select()->next_select() && - select_lex->order_list.elements && select_lex->explicit_limit; + return select_lex->master_unit()->first_select()->next_select() && + select_lex->order_list.elements && select_lex->explicit_limit; } diff --git a/sql/sql_union.cc b/sql/sql_union.cc index c7b70c14c2d..da25fa774d0 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -831,8 +831,8 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, bool is_union_select; bool have_except= FALSE, have_intersect= FALSE; bool instantiate_tmp_table= false; - bool single_tvc= !first_sl->next_select() && first_sl->tvc && - !fake_select_lex; + bool single_tvc= !first_sl->next_select() && first_sl->tvc; + bool single_tvc_wo_order= single_tvc && !first_sl->order_list.elements; DBUG_ENTER("st_select_lex_unit::prepare"); DBUG_ASSERT(thd == current_thd); @@ -924,8 +924,9 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, if (is_union_select || is_recursive) { - if ((is_unit_op() && !union_needs_tmp_table() && - !have_except && !have_intersect) || single_tvc) + if ((single_tvc_wo_order && !fake_select_lex) || + (is_unit_op() && !union_needs_tmp_table() && + !have_except && !have_intersect && !single_tvc)) { SELECT_LEX *last= first_select(); while (last->next_select()) @@ -940,7 +941,7 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, else { if (!is_recursive) - union_result= new (thd->mem_root) select_unit(thd); + union_result= new (thd->mem_root) select_unit(thd); else { with_element->rec_result= @@ -986,17 +987,40 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, if (sl->tvc && sl->order_list.elements && !sl->tvc->to_be_wrapped_as_with_tail()) { + SELECT_LEX_UNIT *unit= sl->master_unit(); if (thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_VIEW) { - sl->master_unit()->fake_select_lex= 0; - sl->master_unit()->saved_fake_select_lex= 0; + unit->fake_select_lex= 0; + unit->saved_fake_select_lex= 0; } else { - sl->order_list.empty(); - sl->explicit_limit= 0; - sl->select_limit= 0; - sl->offset_limit= 0; + if (!unit->first_select()->next_select()) + { + if (!unit->fake_select_lex) + { + Query_arena *arena, backup_arena; + arena= thd->activate_stmt_arena_if_needed(&backup_arena); + bool rc= unit->add_fake_select_lex(thd); + if (arena) + thd->restore_active_arena(arena, &backup_arena); + if (rc) + goto err; + } + SELECT_LEX *fake= unit->fake_select_lex; + fake->order_list= sl->order_list; + fake->explicit_limit= sl->explicit_limit; + fake->select_limit= sl->select_limit; + fake->offset_limit= sl->offset_limit; + sl->order_list.empty(); + sl->explicit_limit= 0; + sl->select_limit= 0; + sl->offset_limit= 0; + if (describe) + fake->options|= SELECT_DESCRIBE; + } + else if (!sl->explicit_limit) + sl->order_list.empty(); } } @@ -1021,7 +1045,7 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, goto err; } else if (sl->tvc->prepare(thd, sl, tmp_result, this)) - goto err; + goto err; } else if (prepare_join(thd, sl, tmp_result, additional_options, is_union_select)) diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index b61511f42c1..3e8e478e28f 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -817,10 +817,10 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %parse-param { THD *thd } %lex-param { THD *thd } /* - Currently there are 47 shift/reduce conflicts. + Currently there are 38 shift/reduce conflicts. We should not introduce new conflicts any more. */ -%expect 47 +%expect 38 /* Comments for TOKENS. @@ -1638,6 +1638,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %left MYSQL_CONCAT_SYM %left NEG '~' NOT2_SYM BINARY %left COLLATE_SYM +%left SUBQUERY_AS_EXPR /* Tokens that can change their meaning from identifier to something else @@ -1728,7 +1729,9 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); ALTER TABLE t1 ADD SYSTEM VERSIONING; */ %left PREC_BELOW_CONTRACTION_TOKEN2 -%left TEXT_STRING '(' VALUE_SYM VERSIONING_SYM +%left TEXT_STRING '(' ')' VALUE_SYM VERSIONING_SYM +%left EMPTY_FROM_CLAUSE +%right INTO %type DECIMAL_NUM FLOAT_NUM NUM LONG_NUM @@ -1991,16 +1994,18 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); query_specification table_value_constructor simple_table + query_simple query_primary - query_primary_parens + subquery select_into_query_specification - %type - query_specification_start - query_expression_body query_expression - query_expression_unit + query_expression_no_with_clause + query_expression_body_ext + query_expression_body_ext_parens + query_expression_body + query_specification_start %type comp_op @@ -2025,7 +2030,9 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %type query_expression_tail + opt_query_expression_tail order_or_limit + order_limit_lock opt_order_limit_lock %type opt_order_clause order_clause order_list @@ -2178,7 +2185,7 @@ END_OF_INPUT THEN_SYM WHEN_SYM DIV_SYM MOD_SYM OR2_SYM AND_AND_SYM DELETE_SYM MYSQL_CONCAT_SYM ORACLE_CONCAT_SYM -%type opt_with_clause with_clause +%type with_clause %type query_name @@ -5265,7 +5272,7 @@ create_select_query_expression: if (Lex->parsed_insert_select($1->first_select())) MYSQL_YYABORT; } - | LEFT_PAREN_WITH with_clause query_expression_body ')' + | LEFT_PAREN_WITH with_clause query_expression_no_with_clause ')' { SELECT_LEX *first_select= $3->first_select(); $3->set_with_clause($2); @@ -6773,12 +6780,7 @@ parse_vcol_expr: ; parenthesized_expr: - query_expression - { - if (!($$= Lex->create_item_query_expression(thd, $1))) - MYSQL_YYABORT; - } - | expr + expr | expr ',' expr_list { $3->push_front($1, thd->mem_root); @@ -6797,6 +6799,16 @@ virtual_column_func: MYSQL_YYABORT; $$= v; } + | subquery + { + Item *item; + if (!(item= new (thd->mem_root) Item_singlerow_subselect(thd, $1))) + MYSQL_YYABORT; + Virtual_column_info *v= add_virtual_expression(thd, item); + if (unlikely(!v)) + MYSQL_YYABORT; + $$= v; + } ; expr_or_literal: column_default_non_parenthesized_expr | signed_literal ; @@ -9152,8 +9164,9 @@ opt_ignore_leaves: Select : retrieve data from table */ + select: - query_expression_body + query_expression_no_with_clause { if (Lex->push_select($1->fake_select_lex ? $1->fake_select_lex : @@ -9163,10 +9176,11 @@ select: opt_procedure_or_into { Lex->pop_select(); + $1->set_with_clause(NULL); if (Lex->select_finalize($1, $3)) MYSQL_YYABORT; } - | with_clause query_expression_body + | with_clause query_expression_no_with_clause { if (Lex->push_select($2->fake_select_lex ? $2->fake_select_lex : @@ -9183,7 +9197,6 @@ select: } ; - select_into: select_into_query_specification { @@ -9192,14 +9205,15 @@ select_into: } opt_order_limit_lock { - st_select_lex_unit *unit; - if (!(unit= Lex->parsed_body_select($1, $3))) + SELECT_LEX_UNIT *unit; + if (!(unit = Lex->create_unit($1))) MYSQL_YYABORT; + if ($3) + unit= Lex->add_tail_to_query_expression_body(unit, $3); if (Lex->select_finalize(unit)) MYSQL_YYABORT; - } - ; - + } + ; simple_table: query_specification { $$= $1; } @@ -9265,92 +9279,191 @@ select_into_query_specification: } ; -opt_from_clause: - /* Empty */ - | from_clause +/** + + The following grammar for query expressions conformant to + the latest SQL Standard is supported: + + ::= + [ ] + [ ] [ ] [ ] + + ::= + WITH [ RECURSIVE ] ::= + [ { }... ] + + ::= + [ '(' ')' ] + AS + + ::= + + + :: + + | UNION [ ALL | DISTINCT ] + | EXCEPT [ DISTINCT ] + + ::= + + | INTERSECT [ DISTINCT ] + + ::= + + | '(' + [ ] [ ] [ ] + ')' + + + + |
+ + + '(' ')' + +*/ + +/* + query_expression produces the same expressions as + +*/ + +query_expression: + query_expression_no_with_clause + { + $1->set_with_clause(NULL); + $$= $1; + } + | with_clause + query_expression_no_with_clause + { + $2->set_with_clause($1); + $1->attach_to($2->first_select()); + $$= $2; + } ; +/* + query_expression_no_with_clause produces the same expressions as + without [ ] +*/ -query_primary: - simple_table - { $$= $1; } - | query_primary_parens - { $$= $1; } +query_expression_no_with_clause: + query_expression_body_ext { $$= $1; } + | query_expression_body_ext_parens { $$= $1; } ; -query_primary_parens: - '(' query_expression_unit +/* + query_expression_body_ext produces the same expressions as + + [ ] [ ] [ ] + | '('... + [ ] [ ] [ ] + ')'... + Note: number of ')' must be equal to the number of '(' in the rule above +*/ + +query_expression_body_ext: + query_expression_body { - if (Lex->parsed_unit_in_brackets($2)) - MYSQL_YYABORT; + if ($1->first_select()->next_select()) + { + if (Lex->parsed_multi_operand_query_expression_body($1)) + MYSQL_YYABORT; + } } - query_expression_tail ')' + opt_query_expression_tail { - $$= Lex->parsed_unit_in_brackets_tail($2, $4); + if (!$3) + $$= $1; + else + $$= Lex->add_tail_to_query_expression_body($1, $3); } - | '(' query_primary + | query_expression_body_ext_parens { - Lex->push_select($2); + Lex->push_select(!$1->first_select()->next_select() ? + $1->first_select() : $1->fake_select_lex); } - query_expression_tail ')' + query_expression_tail { - if (!($$= Lex->parsed_select_in_brackets($2, $4))) - YYABORT; + if (!($$= Lex->add_tail_to_query_expression_body_ext_parens($1, $3))) + MYSQL_YYABORT; } ; -query_expression_unit: - query_primary - unit_type_decl - query_primary - { - if (!($$= Lex->parsed_select_expr_start($1, $3, $2.unit_type, - $2.distinct))) - YYABORT; - } - | query_expression_unit - unit_type_decl - query_primary +query_expression_body_ext_parens: + '(' query_expression_body_ext_parens ')' + { $$= $2; } + | '(' query_expression_body_ext ')' { - if (!($$= Lex->parsed_select_expr_cont($1, $3, $2.unit_type, - $2.distinct, FALSE))) - YYABORT; + SELECT_LEX *sel= $2->first_select()->next_select() ? + $2->fake_select_lex : $2->first_select(); + sel->braces= true; + $$= $2; } ; +/* + query_expression_body produces the same expressions as + +*/ + query_expression_body: - query_primary + query_simple { Lex->push_select($1); + if (!($$= Lex->create_unit($1))) + MYSQL_YYABORT; } - query_expression_tail + | query_expression_body + unit_type_decl { - if (!($$= Lex->parsed_body_select($1, $3))) - MYSQL_YYABORT; + if (!$1->first_select()->next_select()) + { + Lex->pop_select(); + } } - | query_expression_unit + query_primary { - if (Lex->parsed_body_unit($1)) + if (!($$= Lex->add_primary_to_query_expression_body($1, $4, + $2.unit_type, + $2.distinct, + FALSE))) MYSQL_YYABORT; } - query_expression_tail + | query_expression_body_ext_parens + unit_type_decl + query_primary { - if (!($$= Lex->parsed_body_unit_tail($1, $3))) + if (!($$= Lex->add_primary_to_query_expression_body_ext_parens( + $1, $3, + $2.unit_type, + $2.distinct))) MYSQL_YYABORT; } ; -query_expression: - opt_with_clause - query_expression_body - { - if ($1) - { - $2->set_with_clause($1); - $1->attach_to($2->first_select()); - } - $$= $2; - } +/* + query_primary produces the same expressions as + +*/ + +query_primary: + query_simple + { $$= $1; } + | query_expression_body_ext_parens + { $$= $1->first_select(); } + ; + +/* + query_simple produces the same expressions as + +*/ + +query_simple: + simple_table { $$= $1;} ; subselect: @@ -9361,11 +9474,63 @@ subselect: } ; - -/** -
, as in the SQL standard. +/* + subquery produces the same expressions as + + + Consider the production rule of the SQL Standard + subquery: + '(' query_expression')' + + This rule is equivalent to the rule + subquery: + '(' query_expression_no_with_clause ')' + | '(' with_clause query_expression_no_with_clause ')' + that in its turn is equivalent to + subquery: + '(' query_expression_body_ext ')' + | query_expression_body_ext_parens + | '(' with_clause query_expression_no_with_clause ')' + + The latter can be re-written into + subquery: + query_expression_body_ext_parens ')' + | '(' with_clause query_expression_no_with_clause ')' + + The last rule allows us to resolve properly the shift/reduce conflict + when subquery is used in expressions such as in the following queries + select (select * from t1 limit 1) + t2.a from t2 + select * from t1 where t1.a [not] in (select t2.a from t2) + + In the rule below %prec SUBQUERY_AS_EXPR forces the parser to perform a shift + operation rather then a reduce operation when ')' is encountered and can be + considered as the last symbol a query expression. */ +subquery: + query_expression_body_ext_parens %prec SUBQUERY_AS_EXPR + { + if (!$1->fake_select_lex) + $1->first_select()->braces= false; + else + $1->fake_select_lex->braces= false; + if (!($$= Lex->parsed_subselect($1))) + YYABORT; + } + | '(' with_clause query_expression_no_with_clause ')' + { + $3->set_with_clause($2); + $2->attach_to($3->first_select()); + if (!($$= Lex->parsed_subselect($3))) + YYABORT; + } + ; + +opt_from_clause: + /* empty */ %prec EMPTY_FROM_CLAUSE + | from_clause + ; + from_clause: FROM table_reference_list ; @@ -9529,6 +9694,7 @@ select_lock_type: } ; + opt_select_lock_type: /* empty */ { @@ -9540,6 +9706,7 @@ opt_select_lock_type: } ; + opt_lock_wait_timeout_new: /* empty */ { @@ -9826,15 +9993,15 @@ bool_pri: ; predicate: - bit_expr IN_SYM '(' subselect ')' + bit_expr IN_SYM subquery { - $$= new (thd->mem_root) Item_in_subselect(thd, $1, $4); + $$= new (thd->mem_root) Item_in_subselect(thd, $1, $3); if (unlikely($$ == NULL)) MYSQL_YYABORT; } - | bit_expr not IN_SYM '(' subselect ')' + | bit_expr not IN_SYM subquery { - Item *item= new (thd->mem_root) Item_in_subselect(thd, $1, $5); + Item *item= new (thd->mem_root) Item_in_subselect(thd, $1, $4); if (unlikely(item == NULL)) MYSQL_YYABORT; $$= negate_expression(thd, item); @@ -10353,6 +10520,11 @@ primary_expr: column_default_non_parenthesized_expr | explicit_cursor_attr | '(' parenthesized_expr ')' { $$= $2; } + | subquery + { + if (!($$= Lex->create_item_query_expression(thd, $1->master_unit()))) + MYSQL_YYABORT; + } ; string_factor_expr: @@ -12151,35 +12323,12 @@ table_primary_ident: } ; - -/* - Represents a flattening of the following rules from the SQL:2003 - standard. This sub-rule corresponds to the sub-rule -
::= ... | [ AS ] - - ::=
-
::= - ::= - ::= [ ] - - For the time being we use the non-standard rule - select_derived_union which is a compromise between the standard - and our parser. Possibly this rule could be replaced by our - query_expression_body. -*/ - table_primary_derived: - query_primary_parens opt_for_system_time_clause table_alias_clause + subquery + opt_for_system_time_clause table_alias_clause { - if (!($$= Lex->parsed_derived_select($1, $2, $3))) - YYABORT; - } - | '(' - query_expression - ')' opt_for_system_time_clause table_alias_clause - { - if (!($$= Lex->parsed_derived_unit($2, $4, $5))) - YYABORT; + if (!($$= Lex->parsed_derived_table($1->master_unit(), $2, $3))) + MYSQL_YYABORT; } ; @@ -12315,7 +12464,6 @@ table_alias: opt_table_alias_clause: /* empty */ { $$=0; } - | table_alias_clause { $$= $1; } ; @@ -12449,7 +12597,7 @@ opt_window_clause: {} | WINDOW_SYM window_def_list - {} + {} ; window_def_list: @@ -12778,10 +12926,8 @@ delete_limit_clause: | LIMIT limit_option ROWS_SYM EXAMINED_SYM { thd->parse_error(); MYSQL_YYABORT; } ; -opt_order_limit_lock: - /* empty */ - { $$= NULL; } - | order_or_limit +order_limit_lock: + order_or_limit { $$= $1; $$->lock.empty(); @@ -12801,32 +12947,45 @@ opt_order_limit_lock: $$->lock= $1; } ; + +opt_order_limit_lock: + /* empty */ + { + Lex->pop_select(); + $$= NULL; + } + | order_limit_lock { $$= $1; } + ; + query_expression_tail: + order_limit_lock + ; + +opt_query_expression_tail: opt_order_limit_lock ; opt_procedure_or_into: - /* empty */ - { - $$.empty(); - } + /* empty */ + { + $$.empty(); + } | procedure_clause opt_select_lock_type - { - $$= $2; - } + { + $$= $2; + } | into opt_select_lock_type - { - push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, - ER_WARN_DEPRECATED_SYNTAX, - ER_THD(thd, ER_WARN_DEPRECATED_SYNTAX), - " INTO " - " FROM...'"); - $$= $2; - } + { + push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, + ER_WARN_DEPRECATED_SYNTAX, + ER_THD(thd, ER_WARN_DEPRECATED_SYNTAX), + " INTO " + " FROM...'"); + $$= $2; + } ; - order_or_limit: order_clause opt_limit_clause { @@ -15215,16 +15374,6 @@ temporal_literal: } ; - -opt_with_clause: - /*empty */ { $$= 0; } - | with_clause - { - $$= $1; - } - ; - - with_clause: WITH opt_recursive { diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index dad1960257c..812f2032c18 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -295,10 +295,10 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %parse-param { THD *thd } %lex-param { THD *thd } /* - Currently there are 50 shift/reduce conflicts. + Currently there are 41 shift/reduce conflicts. We should not introduce new conflicts any more. */ -%expect 50 +%expect 41 /* Comments for TOKENS. @@ -1115,6 +1115,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %left '^' %left MYSQL_CONCAT_SYM %left NEG '~' NOT2_SYM BINARY +%left SUBQUERY_AS_EXPR %left COLLATE_SYM /* @@ -1206,7 +1207,9 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); ALTER TABLE t1 ADD SYSTEM VERSIONING; */ %left PREC_BELOW_CONTRACTION_TOKEN2 -%left TEXT_STRING '(' VALUE_SYM VERSIONING_SYM +%left TEXT_STRING '(' ')' VALUE_SYM VERSIONING_SYM +%left EMPTY_FROM_CLAUSE +%right INTO %type DECIMAL_NUM FLOAT_NUM NUM LONG_NUM @@ -1478,16 +1481,18 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); query_specification table_value_constructor simple_table + query_simple query_primary - query_primary_parens + subquery select_into_query_specification - %type - query_specification_start - query_expression_body query_expression - query_expression_unit + query_expression_no_with_clause + query_expression_body_ext + query_expression_body_ext_parens + query_expression_body + query_specification_start %type comp_op @@ -1512,7 +1517,9 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %type query_expression_tail + opt_query_expression_tail order_or_limit + order_limit_lock opt_order_limit_lock %type opt_order_clause order_clause order_list @@ -1681,7 +1688,7 @@ END_OF_INPUT THEN_SYM WHEN_SYM DIV_SYM MOD_SYM OR2_SYM AND_AND_SYM DELETE_SYM MYSQL_CONCAT_SYM ORACLE_CONCAT_SYM -%type opt_with_clause with_clause +%type with_clause %type query_name @@ -5273,7 +5280,7 @@ create_select_query_expression: if (Lex->parsed_insert_select($1->first_select())) MYSQL_YYABORT; } - | LEFT_PAREN_WITH with_clause query_expression_body ')' + | LEFT_PAREN_WITH with_clause query_expression_no_with_clause ')' { SELECT_LEX *first_select= $3->first_select(); $3->set_with_clause($2); @@ -6782,12 +6789,7 @@ parse_vcol_expr: ; parenthesized_expr: - query_expression - { - if (!($$= Lex->create_item_query_expression(thd, $1))) - MYSQL_YYABORT; - } - | expr + expr | expr ',' expr_list { $3->push_front($1, thd->mem_root); @@ -6806,6 +6808,16 @@ virtual_column_func: MYSQL_YYABORT; $$= v; } + | subquery + { + Item *item; + if (!(item= new (thd->mem_root) Item_singlerow_subselect(thd, $1))) + MYSQL_YYABORT; + Virtual_column_info *v= add_virtual_expression(thd, item); + if (unlikely(!v)) + MYSQL_YYABORT; + $$= v; + } ; expr_or_literal: column_default_non_parenthesized_expr | signed_literal ; @@ -9254,7 +9266,7 @@ opt_ignore_leaves: */ select: - query_expression_body + query_expression_no_with_clause { if (Lex->push_select($1->fake_select_lex ? $1->fake_select_lex : @@ -9264,10 +9276,11 @@ select: opt_procedure_or_into { Lex->pop_select(); + $1->set_with_clause(NULL); if (Lex->select_finalize($1, $3)) MYSQL_YYABORT; } - | with_clause query_expression_body + | with_clause query_expression_no_with_clause { if (Lex->push_select($2->fake_select_lex ? $2->fake_select_lex : @@ -9293,9 +9306,11 @@ select_into: } opt_order_limit_lock { - st_select_lex_unit *unit; - if (!(unit= Lex->parsed_body_select($1, $3))) + SELECT_LEX_UNIT *unit; + if (!(unit = Lex->create_unit($1))) MYSQL_YYABORT; + if ($3) + unit= Lex->add_tail_to_query_expression_body(unit, $3); if (Lex->select_finalize(unit)) MYSQL_YYABORT; } @@ -9366,92 +9381,191 @@ select_into_query_specification: } ; -opt_from_clause: - /* Empty */ - | from_clause +/** + + The following grammar for query expressions conformant to + the latest SQL Standard is supported: + + ::= + [ ] + [ ] [ ] [ ] + + ::= + WITH [ RECURSIVE ] ::= + [ { }... ] + + ::= + [ '(' ')' ] + AS
+ + ::= + + + :: + + | UNION [ ALL | DISTINCT ] + | EXCEPT [ DISTINCT ] + + ::= + + | INTERSECT [ DISTINCT ] + + ::= + + | '(' + [ ] [ ] [ ] + ')' + + + + |
+ + + '(' ')' + +*/ + +/* + query_expression produces the same expressions as + +*/ + +query_expression: + query_expression_no_with_clause + { + $1->set_with_clause(NULL); + $$= $1; + } + | with_clause + query_expression_no_with_clause + { + $2->set_with_clause($1); + $1->attach_to($2->first_select()); + $$= $2; + } ; +/* + query_expression_no_with_clause produces the same expressions as + without [ ] +*/ -query_primary: - simple_table - { $$= $1; } - | query_primary_parens - { $$= $1; } +query_expression_no_with_clause: + query_expression_body_ext { $$= $1; } + | query_expression_body_ext_parens { $$= $1; } ; -query_primary_parens: - '(' query_expression_unit +/* + query_expression_body_ext produces the same expressions as + + [ ] [ ] [ ] + | '('... + [ ] [ ] [ ] + ')'... + Note: number of ')' must be equal to the number of '(' in the rule above +*/ + +query_expression_body_ext: + query_expression_body { - if (Lex->parsed_unit_in_brackets($2)) - MYSQL_YYABORT; + if ($1->first_select()->next_select()) + { + if (Lex->parsed_multi_operand_query_expression_body($1)) + MYSQL_YYABORT; + } } - query_expression_tail ')' + opt_query_expression_tail { - $$= Lex->parsed_unit_in_brackets_tail($2, $4); + if (!$3) + $$= $1; + else + $$= Lex->add_tail_to_query_expression_body($1, $3); } - | '(' query_primary + | query_expression_body_ext_parens { - Lex->push_select($2); + Lex->push_select(!$1->first_select()->next_select() ? + $1->first_select() : $1->fake_select_lex); } - query_expression_tail ')' + query_expression_tail { - if (!($$= Lex->parsed_select_in_brackets($2, $4))) - YYABORT; + if (!($$= Lex->add_tail_to_query_expression_body_ext_parens($1, $3))) + MYSQL_YYABORT; } ; -query_expression_unit: - query_primary - unit_type_decl - query_primary - { - if (!($$= Lex->parsed_select_expr_start($1, $3, $2.unit_type, - $2.distinct))) - YYABORT; - } - | query_expression_unit - unit_type_decl - query_primary +query_expression_body_ext_parens: + '(' query_expression_body_ext_parens ')' + { $$= $2; } + | '(' query_expression_body_ext ')' { - if (!($$= Lex->parsed_select_expr_cont($1, $3, $2.unit_type, - $2.distinct, TRUE))) - YYABORT; + SELECT_LEX *sel= $2->first_select()->next_select() ? + $2->fake_select_lex : $2->first_select(); + sel->braces= true; + $$= $2; } ; +/* + query_expression_body produces the same expressions as + +*/ + query_expression_body: - query_primary + query_simple { Lex->push_select($1); + if (!($$= Lex->create_unit($1))) + MYSQL_YYABORT; } - query_expression_tail + | query_expression_body + unit_type_decl { - if (!($$= Lex->parsed_body_select($1, $3))) - MYSQL_YYABORT; + if (!$1->first_select()->next_select()) + { + Lex->pop_select(); + } } - | query_expression_unit + query_primary { - if (Lex->parsed_body_unit($1)) + if (!($$= Lex->add_primary_to_query_expression_body($1, $4, + $2.unit_type, + $2.distinct, + TRUE))) MYSQL_YYABORT; } - query_expression_tail + | query_expression_body_ext_parens + unit_type_decl + query_primary { - if (!($$= Lex->parsed_body_unit_tail($1, $3))) + if (!($$= Lex->add_primary_to_query_expression_body_ext_parens( + $1, $3, + $2.unit_type, + $2.distinct))) MYSQL_YYABORT; } ; -query_expression: - opt_with_clause - query_expression_body - { - if ($1) - { - $2->set_with_clause($1); - $1->attach_to($2->first_select()); - } - $$= $2; - } +/* + query_primary produces the same expressions as + +*/ + +query_primary: + query_simple + { $$= $1; } + | query_expression_body_ext_parens + { $$= $1->first_select(); } + ; + +/* + query_simple produces the same expressions as + +*/ + +query_simple: + simple_table { $$= $1;} ; subselect: @@ -9462,11 +9576,63 @@ subselect: } ; - -/** -
, as in the SQL standard. +/* + subquery produces the same expressions as + + + Consider the production rule of the SQL Standard + subquery: + '(' query_expression ')' + + This rule is equivalent to the rule + subquery: + '(' query_expression_no_with_clause ')' + | '(' with_clause query_expression_no_with_clause ')' + that in its turn is equivalent to + subquery: + '(' query_expression_body_ext ')' + | query_expression_body_ext_parens + | '(' with_clause query_expression_no_with_clause ')' + + The latter can be re-written into + subquery: + query_expression_body_ext_parens + | '(' with_clause query_expression_no_with_clause ')' + + The last rule allows us to resolve properly the shift/reduce conflict + when subquery is used in expressions such as in the following queries + select (select * from t1 limit 1) + t2.a from t2 + select * from t1 where t1.a [not] in (select t2.a from t2) + + In the rule below %prec SUBQUERY_AS_EXPR forces the parser to perform a shift + operation rather then a reduce operation when ')' is encountered and can be + considered as the last symbol a query expression. */ +subquery: + query_expression_body_ext_parens %prec SUBQUERY_AS_EXPR + { + if (!$1->fake_select_lex) + $1->first_select()->braces= false; + else + $1->fake_select_lex->braces= false; + if (!($$= Lex->parsed_subselect($1))) + YYABORT; + } + | '(' with_clause query_expression_no_with_clause ')' + { + $3->set_with_clause($2); + $2->attach_to($3->first_select()); + if (!($$= Lex->parsed_subselect($3))) + YYABORT; + } + ; + +opt_from_clause: + /* empty */ %prec EMPTY_FROM_CLAUSE + | from_clause + ; + from_clause: FROM table_reference_list ; @@ -9936,15 +10102,15 @@ bool_pri: ; predicate: - bit_expr IN_SYM '(' subselect ')' + bit_expr IN_SYM subquery { - $$= new (thd->mem_root) Item_in_subselect(thd, $1, $4); + $$= new (thd->mem_root) Item_in_subselect(thd, $1, $3); if (unlikely($$ == NULL)) MYSQL_YYABORT; } - | bit_expr not IN_SYM '(' subselect ')' + | bit_expr not IN_SYM subquery { - Item *item= new (thd->mem_root) Item_in_subselect(thd, $1, $5); + Item *item= new (thd->mem_root) Item_in_subselect(thd, $1, $4); if (unlikely(item == NULL)) MYSQL_YYABORT; $$= negate_expression(thd, item); @@ -10463,6 +10629,11 @@ primary_expr: column_default_non_parenthesized_expr | explicit_cursor_attr | '(' parenthesized_expr ')' { $$= $2; } + | subquery + { + if (!($$= Lex->create_item_query_expression(thd, $1->master_unit()))) + MYSQL_YYABORT; + } ; string_factor_expr: @@ -12261,37 +12432,15 @@ table_primary_ident: } ; - -/* - Represents a flattening of the following rules from the SQL:2003 - standard. This sub-rule corresponds to the sub-rule -
::= ... | [ AS ] - - ::=
-
::= - ::= - ::= [ ] - - For the time being we use the non-standard rule - select_derived_union which is a compromise between the standard - and our parser. Possibly this rule could be replaced by our - query_expression_body. -*/ - table_primary_derived: - query_primary_parens opt_for_system_time_clause table_alias_clause + subquery + opt_for_system_time_clause table_alias_clause { - if (!($$= Lex->parsed_derived_select($1, $2, $3))) - YYABORT; - } - | '(' - query_expression - ')' opt_for_system_time_clause table_alias_clause - { - if (!($$= Lex->parsed_derived_unit($2, $4, $5))) - YYABORT; + if (!($$= Lex->parsed_derived_table($1->master_unit(), $2, $3))) + MYSQL_YYABORT; } ; + ; opt_outer: /* empty */ {} @@ -12425,7 +12574,6 @@ table_alias: opt_table_alias_clause: /* empty */ { $$=0; } - | table_alias_clause { $$= $1; } ; @@ -12888,10 +13036,8 @@ delete_limit_clause: | LIMIT limit_option ROWS_SYM EXAMINED_SYM { thd->parse_error(); MYSQL_YYABORT; } ; -opt_order_limit_lock: - /* empty */ - { $$= NULL; } - | order_or_limit +order_limit_lock: + order_or_limit { $$= $1; $$->lock.empty(); @@ -12911,29 +13057,42 @@ opt_order_limit_lock: $$->lock= $1; } ; +opt_order_limit_lock: + /* empty */ + { + Lex->pop_select(); + $$= NULL; + } + | order_limit_lock { $$= $1; } + ; + query_expression_tail: + order_limit_lock + ; + +opt_query_expression_tail: opt_order_limit_lock ; opt_procedure_or_into: /* empty */ - { - $$.empty(); - } + { + $$.empty(); + } | procedure_clause opt_select_lock_type - { - $$= $2; - } + { + $$= $2; + } | into opt_select_lock_type - { - push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, - ER_WARN_DEPRECATED_SYNTAX, - ER_THD(thd, ER_WARN_DEPRECATED_SYNTAX), - " INTO " - " FROM...'"); - $$= $2; - } + { + push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, + ER_WARN_DEPRECATED_SYNTAX, + ER_THD(thd, ER_WARN_DEPRECATED_SYNTAX), + " INTO " + " FROM...'"); + $$= $2; + } ; @@ -15347,16 +15506,6 @@ temporal_literal: } ; - -opt_with_clause: - /*empty */ { $$= 0; } - | with_clause - { - $$= $1; - } - ; - - with_clause: WITH opt_recursive { -- cgit v1.2.1