[Commits] c541808: MDEV-19956 Queries with subqueries containing UNION are not parsed

revision-id: c541808629d20345c4fc0a0b6495dd0616557250 (mariadb-10.4.4-234-gc541808) parent(s): e8392e58b2a5a69f9c0bd5b5aed90348b4a0ccb3 author: Igor Babaev committer: Igor Babaev timestamp: 2019-09-14 12:36:56 -0700 message: MDEV-19956 Queries with subqueries containing UNION are not parsed Some Shift-Reduce conflicts prevented parsing queries with subqueries in expressions and in IN predicands. This patch re-wrote the grammar rules to avoid these conflicts using an idea taken from MySQL 8.0. --- mysql-test/main/brackets.result | 4022 +++++++++++++++++++++- mysql-test/main/brackets.test | 2318 +++++++++++++ mysql-test/main/cte_nonrecursive.result | 4 +- mysql-test/main/cte_recursive.result | 6 +- 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/ps.result | 8 +- mysql-test/main/statement-expr.result | 16 +- mysql-test/main/subselect.result | 19 +- mysql-test/main/subselect.test | 4 - mysql-test/main/subselect_no_exists_to_in.result | 19 +- mysql-test/main/subselect_no_mat.result | 19 +- mysql-test/main/subselect_no_opts.result | 19 +- mysql-test/main/subselect_no_scache.result | 19 +- mysql-test/main/subselect_no_semijoin.result | 19 +- mysql-test/main/subselect_notembedded.result | 2 +- sql/item_subselect.cc | 3 +- sql/sql_cte.cc | 16 + sql/sql_lex.cc | 295 +- sql/sql_lex.h | 48 +- sql/sql_table.cc | 2 +- sql/sql_tvc.cc | 4 +- sql/sql_union.cc | 49 +- sql/sql_yacc.yy | 442 ++- sql/sql_yacc_ora.yy | 428 ++- 27 files changed, 7214 insertions(+), 580 deletions(-) diff --git a/mysql-test/main/brackets.result b/mysql-test/main/brackets.result index e14bef9..dedd9a2 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 <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort Warnings: -Note 1003 (/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 20) union /* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `a` desc limit 1 +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 9ca86b8..b7bb616 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/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result index 2556fd4..f0f69a7 100644 --- a/mysql-test/main/cte_nonrecursive.result +++ b/mysql-test/main/cte_nonrecursive.result @@ -606,7 +606,7 @@ with t(c) as (select a from t1 where b >= 'c') select * from t r1 where r1.c=4; show create view v3; View Create View character_set_client collation_connection -v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS with t as (select `t1`.`a` AS `c` from `t1` where `t1`.`b` >= 'c')select `r1`.`c` AS `c` from `t` `r1` where `r1`.`c` = 4 latin1 latin1_swedish_ci +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS with t(c) as (select `t1`.`a` AS `c` from `t1` where `t1`.`b` >= 'c')select `r1`.`c` AS `c` from `t` `r1` where `r1`.`c` = 4 latin1 latin1_swedish_ci select * from v3; c 4 @@ -618,7 +618,7 @@ with t(c) as (select a from t1 where b >= 'c') select * from t r1, t r2 where r1.c=r2.c and r2.c=4; show create view v4; View Create View character_set_client collation_connection -v4 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS with t as (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `r1`.`c` AS `c`,`r2`.`c` AS `d` from (`t` `r1` join (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c') `r2`) where `r1`.`c` = `r2`.`c` and `r2`.`c` = 4 latin1 latin1_swedish_ci +v4 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS with t(c) as (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `r1`.`c` AS `c`,`r2`.`c` AS `d` from (`t` `r1` join (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c') `r2`) where `r1`.`c` = `r2`.`c` and `r2`.`c` = 4 latin1 latin1_swedish_ci select * from v4; c d 4 4 diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index 0b22da8..b88f0ff 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -699,7 +699,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 5 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) NULL UNION RESULT <union3,4,5> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 with recursive ancestor_couple_ids as (/* select#2 */ select `a`.`father` AS `h_id`,`a`.`mother` AS `w_id` from `coupled_ancestors` `a` where `a`.`father` is not null and `a`.`mother` is not null), coupled_ancestors as (/* select#3 */ select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union all /* select#4 */ select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `fa` where `test`.`p`.`id` = `fa`.`h_id` union all /* select#5 */ select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `ma` where `test`.`p`.`id` = `ma`.`w_ id`)/* select#1 */ select `h`.`name` AS `name`,`h`.`dob` AS `dob`,`w`.`name` AS `name`,`w`.`dob` AS `dob` from `ancestor_couple_ids` `c` join `coupled_ancestors` `h` join `coupled_ancestors` `w` where `h`.`id` = `c`.`h_id` and `w`.`id` = `c`.`w_id` +Note 1003 with recursive ancestor_couple_ids(h_id,w_id) as (/* select#2 */ select `a`.`father` AS `h_id`,`a`.`mother` AS `w_id` from `coupled_ancestors` `a` where `a`.`father` is not null and `a`.`mother` is not null), coupled_ancestors(id,name,dob,father,mother) as (/* select#3 */ select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union all /* select#4 */ select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `fa` where `test`.`p`.`id` = `fa`.`h_id` union all /* select#5 */ select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_cou ple_ids` `ma` where `test`.`p`.`id` = `ma`.`w_id`)/* select#1 */ select `h`.`name` AS `name`,`h`.`dob` AS `dob`,`w`.`name` AS `name`,`w`.`dob` AS `dob` from `ancestor_couple_ids` `c` join `coupled_ancestors` `h` join `coupled_ancestors` `w` where `h`.`id` = `c`.`h_id` and `w`.`id` = `c`.`w_id` # simple mutual recursion with recursive ancestor_couple_ids(h_id, w_id) @@ -3091,7 +3091,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 DEPENDENT SUBQUERY <derived2> ALL NULL NULL NULL NULL 16 100.00 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 with recursive destinations as (/* select#2 */ select `test`.`a`.`arrival` AS `city`,1 AS `legs` from `test`.`flights` `a` where `test`.`a`.`departure` = 'Cairo' union /* select#3 */ select `test`.`b`.`arrival` AS `arrival`,`r`.`legs` + 1 AS `r.legs + 1` from `destinations` `r` join `test`.`flights` `b` where `r`.`city` = `test`.`b`.`departure` and !<in_optimizer>(`test`.`b`.`arrival`,<exists>(/* select#4 */ select `destinations`.`city` from `destinations` where trigcond(`test`.`b`.`arrival` = `destinations`.`city` or `destinations`.`city` is null) having trigcond(`destinations`.`city` is null))))/* select#1 */ select `destinations`.`city` AS `city`,`destinations`.`legs` AS `legs` from `destinations` +Note 1003 with recursive destinations(city,legs) as (/* select#2 */ select `test`.`a`.`arrival` AS `city`,1 AS `legs` from `test`.`flights` `a` where `test`.`a`.`departure` = 'Cairo' union /* select#3 */ select `test`.`b`.`arrival` AS `arrival`,`r`.`legs` + 1 AS `r.legs + 1` from `destinations` `r` join `test`.`flights` `b` where `r`.`city` = `test`.`b`.`departure` and !<in_optimizer>(`test`.`b`.`arrival`,<exists>(/* select#4 */ select `destinations`.`city` from `destinations` where trigcond(`test`.`b`.`arrival` = `destinations`.`city` or `destinations`.`city` is null) having trigcond(`destinations`.`city` is null))))/* select#1 */ select `destinations`.`city` AS `city`,`destinations`.`legs` AS `legs` from `destinations` set standard_compliant_cte=default; drop table flights; # @@ -3378,7 +3378,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 with recursive rcte as (/* select#2 */ select 1 AS `a` union /* select#3 */ select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (/* select#4 */ select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (/* select#5 */ select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)/* select#1 */ select `cte1`.`c1` AS `c1`,`cte2`.`c2` AS `c2` from `cte1` join `cte2` +Note 1003 with recursive rcte(a) as (/* select#2 */ select 1 AS `a` union /* select#3 */ select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (/* select#4 */ select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (/* select#5 */ select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)/* select#1 */ select `cte1`.`c1` AS `c1`,`cte2`.`c2` AS `c2` from `cte1` join `cte2` prepare stmt from "with recursive rcte(a) as (select 1 union select cast(a+1 as unsigned) from rcte where a < 10), diff --git a/mysql-test/main/except.result b/mysql-test/main/except.result index 9c5a3ea..4c6a094 100644 --- a/mysql-test/main/except.result +++ b/mysql-test/main/except.result @@ -24,7 +24,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 <except2,3> 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 { @@ -229,7 +229,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 <except2,3> 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 bd88243..4b66580 100644 --- a/mysql-test/main/intersect.result +++ b/mysql-test/main/intersect.result @@ -37,7 +37,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 <intersect2,3,4> 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 { @@ -278,7 +278,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 <intersect2,3> 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` EXPLAIN format=json (select a,b from t1) intersect (select c,e from t2,t3); EXPLAIN { @@ -720,7 +720,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 2e234216..46ec3ea 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 35a2334..d024f06 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/ps.result b/mysql-test/main/ps.result index 1e84618..766abc3 100644 --- a/mysql-test/main/ps.result +++ b/mysql-test/main/ps.result @@ -4484,7 +4484,7 @@ DEALLOCATE PREPARE stmt; # PREPARE stmt FROM 'SELECT ? FROM DUAL'; EXECUTE stmt USING (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 '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 DEALLOCATE PREPARE stmt; CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test'; PREPARE stmt FROM 'SELECT ? FROM DUAL'; @@ -4683,7 +4683,7 @@ ERROR 21000: Operand should contain 1 column(s) # Testing disallowed expressions in USING # EXECUTE IMMEDIATE 'SELECT ? FROM DUAL' USING (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 '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 CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test'; EXECUTE IMMEDIATE 'SELECT ? FROM DUAL' USING f1(); ERROR 42000: EXECUTE IMMEDIATE does not support subqueries or stored functions @@ -4869,9 +4869,9 @@ ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2 PREPARE stmt FROM CONCAT(_latin1'SELECT 1 AS c FROM ', _latin2 'DUAL'); ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'concat' EXECUTE IMMEDIATE (SELECT '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 'SELECT '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 PREPARE stmt FROM (SELECT '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 'SELECT '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 EXECUTE IMMEDIATE a; ERROR 42S22: Unknown column 'a' in 'field list' PREPARE stmt FROM a; diff --git a/mysql-test/main/statement-expr.result b/mysql-test/main/statement-expr.result index c73ed28..d19a1ce 100644 --- a/mysql-test/main/statement-expr.result +++ b/mysql-test/main/statement-expr.result @@ -12,9 +12,9 @@ ROW(1, 7) IN (SELECT id, id1 FROM t1 WHERE id1= 8) 0 DROP TABLE t1; EXECUTE IMMEDIATE 'SELECT ?' USING (1 IN (SELECT * 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 'SELECT * FROM t1))' 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 EXECUTE IMMEDIATE 'SELECT ?' USING (SELECT * 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 'SELECT * FROM t1)' 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 CREATE TABLE t1 (id INT); INSERT INTO t1 VALUES (10); CREATE PROCEDURE p1(a INT) BEGIN END; @@ -45,21 +45,21 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SIGNAL SQLSTATE '01000'; END' at line 3 PREPARE stmt FROM (1 IN (SELECT * 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 'SELECT * FROM t1))' 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 PREPARE stmt FROM EXISTS (SELECT * 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 '(SELECT * FROM t1)' 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 EXECUTE IMMEDIATE (1 IN (SELECT * 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 'SELECT * FROM t1))' 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 EXECUTE IMMEDIATE EXISTS (SELECT * 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 '(SELECT * FROM t1)' 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 GET DIAGNOSTICS CONDITION (1 IN (SELECT * FROM t1)) @errno=MYSQL_ERRNO; 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 '(1 IN (SELECT * FROM t1)) @errno=MYSQL_ERRNO' at line 1 GET DIAGNOSTICS CONDITION EXISTS (SELECT * FROM t1) @errno=MYSQL_ERRNO; 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 'EXISTS (SELECT * FROM t1) @errno=MYSQL_ERRNO' at line 1 PURGE BINARY LOGS BEFORE (1 IN (SELECT * 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 'SELECT * FROM t1))' 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 PURGE BINARY LOGS BEFORE EXISTS (SELECT * 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 '(SELECT * FROM t1)' 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 CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2),(3); DO 1 IN (SELECT * FROM t1); diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index 1fdf8d1..28909ae 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -82,7 +82,7 @@ SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(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 'PROCEDURE ANALYSE(1))' at line 1 SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((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 '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) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; ERROR 42S22: Unknown column 'a' in 'field list' SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL; @@ -1132,7 +1132,7 @@ ERROR 42S02: Table 'test.t1' doesn't exist CREATE TABLE t1 (a int, KEY(a)); HANDLER t1 OPEN; HANDLER t1 READ a=((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 '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 HANDLER t1 CLOSE; drop table t1; create table t1 (a int); @@ -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 <union2,3> 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 @@ -5304,7 +5307,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 @@ -5334,7 +5337,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 @@ -5342,7 +5346,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 82823b4..994a2c1 100644 --- a/mysql-test/main/subselect.test +++ b/mysql-test/main/subselect.test @@ -2610,8 +2610,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; @@ -4413,11 +4411,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 613a0d8..1849210 100644 --- a/mysql-test/main/subselect_no_exists_to_in.result +++ b/mysql-test/main/subselect_no_exists_to_in.result @@ -86,7 +86,7 @@ SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(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 'PROCEDURE ANALYSE(1))' at line 1 SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((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 '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) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; ERROR 42S22: Unknown column 'a' in 'field list' SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL; @@ -1136,7 +1136,7 @@ ERROR 42S02: Table 'test.t1' doesn't exist CREATE TABLE t1 (a int, KEY(a)); HANDLER t1 OPEN; HANDLER t1 READ a=((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 '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 HANDLER t1 CLOSE; drop table t1; create table t1 (a int); @@ -3738,8 +3738,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 <union2,3> 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 @@ -5306,7 +5309,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 @@ -5336,7 +5339,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 @@ -5344,7 +5348,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 982e701..63784c1 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -89,7 +89,7 @@ SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(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 'PROCEDURE ANALYSE(1))' at line 1 SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((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 '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) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; ERROR 42S22: Unknown column 'a' in 'field list' SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL; @@ -1139,7 +1139,7 @@ ERROR 42S02: Table 'test.t1' doesn't exist CREATE TABLE t1 (a int, KEY(a)); HANDLER t1 OPEN; HANDLER t1 READ a=((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 '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 HANDLER t1 CLOSE; drop table t1; create table t1 (a int); @@ -3738,8 +3738,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 <union2,3> 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 @@ -5304,7 +5307,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 @@ -5334,7 +5337,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 @@ -5342,7 +5346,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 1937f2a..0923d3c 100644 --- a/mysql-test/main/subselect_no_opts.result +++ b/mysql-test/main/subselect_no_opts.result @@ -85,7 +85,7 @@ SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(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 'PROCEDURE ANALYSE(1))' at line 1 SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((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 '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) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; ERROR 42S22: Unknown column 'a' in 'field list' SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL; @@ -1135,7 +1135,7 @@ ERROR 42S02: Table 'test.t1' doesn't exist CREATE TABLE t1 (a int, KEY(a)); HANDLER t1 OPEN; HANDLER t1 READ a=((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 '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 HANDLER t1 CLOSE; drop table t1; create table t1 (a int); @@ -3734,8 +3734,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 <union2,3> 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 @@ -5300,7 +5303,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 @@ -5330,7 +5333,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 @@ -5338,7 +5342,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 929af9b..5184405 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -88,7 +88,7 @@ SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(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 'PROCEDURE ANALYSE(1))' at line 1 SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((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 '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) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; ERROR 42S22: Unknown column 'a' in 'field list' SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL; @@ -1138,7 +1138,7 @@ ERROR 42S02: Table 'test.t1' doesn't exist CREATE TABLE t1 (a int, KEY(a)); HANDLER t1 OPEN; HANDLER t1 READ a=((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 '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 HANDLER t1 CLOSE; drop table t1; create table t1 (a int); @@ -3741,8 +3741,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 <union2,3> 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 @@ -5310,7 +5313,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 @@ -5340,7 +5343,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 @@ -5348,7 +5352,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 52c81e5..a7ead73 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -85,7 +85,7 @@ SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(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 'PROCEDURE ANALYSE(1))' at line 1 SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((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 '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) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; ERROR 42S22: Unknown column 'a' in 'field list' SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL; @@ -1135,7 +1135,7 @@ ERROR 42S02: Table 'test.t1' doesn't exist CREATE TABLE t1 (a int, KEY(a)); HANDLER t1 OPEN; HANDLER t1 READ a=((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 '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 HANDLER t1 CLOSE; drop table t1; create table t1 (a int); @@ -3734,8 +3734,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 <union2,3> 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 @@ -5300,7 +5303,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 @@ -5330,7 +5333,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 @@ -5338,7 +5342,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_notembedded.result b/mysql-test/main/subselect_notembedded.result index 9153706..5822115 100644 --- a/mysql-test/main/subselect_notembedded.result +++ b/mysql-test/main/subselect_notembedded.result @@ -1,5 +1,5 @@ purge master logs before (select adddate(current_timestamp(), interval -4 day)); -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 'select adddate(current_timestamp(), interval -4 day))' 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 purge master logs before adddate(current_timestamp(), interval -4 day); drop table if exists t1; create table t1(a int,b int,key(a),key(b)); diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 1d950a7..54c63ea 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_cte.cc b/sql/sql_cte.cc index 6f5162b..a391adb 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1438,6 +1438,22 @@ void With_clause::print(String *str, enum_query_type query_type) void With_element::print(String *str, enum_query_type query_type) { str->append(query_name); + if (column_list.elements) + { + List_iterator_fast<LEX_CSTRING> li(column_list); + str->append('('); + for (LEX_CSTRING *col_name= li++; ; ) + { + str->append(col_name); + col_name= li++; + if (!col_name) + { + str->append(')'); + break; + } + str->append(','); + } + } str->append(STRING_WITH_LEN(" as ")); str->append('('); spec->print(str, query_type); diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 777391d..a7c20e3 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; @@ -5338,10 +5338,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); @@ -8990,7 +8989,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; @@ -9034,9 +9034,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, const char *tok_start, st_select_lex_unit *unit) @@ -9051,118 +9052,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) @@ -9193,6 +9093,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; } @@ -9205,12 +9106,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()); @@ -9242,41 +9137,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; @@ -9287,27 +9214,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 */ @@ -9333,7 +9293,6 @@ SELECT_LEX *LEX::parsed_subselect(SELECT_LEX_UNIT *unit, char *place) } - /** Process INSERT-like select */ @@ -9388,40 +9347,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) { @@ -9432,8 +9359,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) @@ -9451,7 +9376,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<TABLE_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); @@ -9474,7 +9400,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(); } @@ -9485,6 +9412,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: @@ -9517,7 +9445,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 0e1d17d..6ead29b 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -4429,9 +4429,6 @@ struct LEX: public Query_tables_list 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*); @@ -4441,7 +4438,7 @@ struct LEX: public Query_tables_list 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; @@ -4451,16 +4448,10 @@ struct LEX: public Query_tables_list 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); @@ -4468,20 +4459,35 @@ struct LEX: public Query_tables_list 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, char *place); 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 cf96bfa..ec9ec17 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -11190,7 +11190,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 ef8e15d..4022cb8 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 41f4234..da25fa7 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)) @@ -1875,6 +1899,7 @@ bool st_select_lex_unit::cleanup() DBUG_RETURN(FALSE); } } + columns_are_renamed= false; cleaned= 1; for (SELECT_LEX *sl= first_select(); sl; sl= sl->next_select()) diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 183a250..6f53937 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 48 shift/reduce conflicts. + Currently there are 39 shift/reduce conflicts. We should not introduce new conflicts any more. */ -%expect 48 +%expect 39 /* 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 <lex_str> 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 <select_lex_unit> - 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 <boolfunc2creator> comp_op @@ -2025,7 +2030,9 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %type <order_limit_lock> query_expression_tail + opt_query_expression_tail order_or_limit + order_limit_lock opt_order_limit_lock %type <select_order> opt_order_clause order_clause order_list @@ -2175,7 +2182,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 <with_clause> opt_with_clause with_clause +%type <with_clause> with_clause %type <lex_str_ptr> 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,13 +6780,7 @@ parse_vcol_expr: ; parenthesized_expr: - remember_tok_start - query_expression - { - if (!($$= Lex->create_item_query_expression(thd, $1, $2))) - MYSQL_YYABORT; - } - | expr + expr | expr ',' expr_list { $3->push_front($1, thd->mem_root); @@ -6798,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 ; @@ -9138,8 +9149,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 : @@ -9149,10 +9161,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 : @@ -9169,7 +9182,6 @@ select: } ; - select_into: select_into_query_specification { @@ -9178,14 +9190,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; } @@ -9251,108 +9264,258 @@ select_into_query_specification: } ; -opt_from_clause: - /* Empty */ - | from_clause +/** + + The following grammar for query expressions conformant to + the latest SQL Standard is supported: + + <query expression> ::= + [ <with clause> ] <query expression body> + [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ] + + <with clause> ::= + WITH [ RECURSIVE ] <with_list + + <with list> ::= + <with list element> [ { <comma> <with list element> }... ] + + <with list element> ::= + <query name> [ '(' <with column list> ')' ] + AS <table subquery> + + <with column list> ::= + <column name list> + + <query expression body> :: + <query term> + | <query expression body> UNION [ ALL | DISTINCT ] <query term> + | <query expression body> EXCEPT [ DISTINCT ] + + <query term> ::= + <query primary> + | <query term> INTERSECT [ DISTINCT ] <query primary> + + <query primary> ::= + <simple table> + | '(' <query expression body> + [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ] + ')' + + <simple table> + <query specification> + | <table value constructor> + + <subquery> + '(' <query_expression> ')' + +*/ + +/* + query_expression produces the same expressions as + <query expression> +*/ + +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 + <query expression> without [ <with clause> ] +*/ -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 + <query expression body> + [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ] + | '('... <query expression body> + [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ] + ')'... + 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_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_primary: + query_simple + { $$= $1; } + | query_expression_body_ext_parens + { $$= $1->first_select(); } + ; + +/* + query_simple produces the same expressions as + <simple table> +*/ + +query_simple: + simple_table { $$= $1;} ; subselect: - remember_tok_start query_expression { - if (!($$= Lex->parsed_subselect($2, $1))) + if (!($$= Lex->parsed_subselect($1, NULL))) YYABORT; } ; - -/** - <table expression>, as in the SQL standard. +/* + subquery produces the same expressions as + <subquery> + + Consider the production rule of the SQL Standard + subquery: + '(' query_expression_no_with_clause ')' + + 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, NULL))) + YYABORT; + } + | '(' with_clause query_expression_no_with_clause ')' + { + $3->set_with_clause($2); + $2->attach_to($3->first_select()); + if (!($$= Lex->parsed_subselect($3, NULL))) + YYABORT; + } + ; + +opt_from_clause: + /* empty */ %prec EMPTY_FROM_CLAUSE + | from_clause + ; + from_clause: FROM table_reference_list ; @@ -9516,6 +9679,7 @@ select_lock_type: } ; + opt_select_lock_type: /* empty */ { @@ -9527,6 +9691,7 @@ opt_select_lock_type: } ; + opt_lock_wait_timeout_new: /* empty */ { @@ -9819,15 +9984,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); @@ -10346,6 +10511,12 @@ primary_expr: column_default_non_parenthesized_expr | explicit_cursor_attr | '(' parenthesized_expr ')' { $$= $2; } + | subquery + { + if (!($$= Lex->create_item_query_expression(thd, NULL, + $1->master_unit()))) + MYSQL_YYABORT; + } ; string_factor_expr: @@ -12148,35 +12319,12 @@ table_primary_ident: } ; - -/* - Represents a flattening of the following rules from the SQL:2003 - standard. This sub-rule corresponds to the sub-rule - <table primary> ::= ... | <derived table> [ AS ] <correlation name> - - <derived table> ::= <table subquery> - <table subquery> ::= <subquery> - <subquery> ::= <left paren> <query expression> <right paren> - <query expression> ::= [ <with clause> ] <query expression body> - - 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; } ; @@ -12312,7 +12460,6 @@ table_alias: opt_table_alias_clause: /* empty */ { $$=0; } - | table_alias_clause { $$= $1; } ; @@ -12446,7 +12593,7 @@ opt_window_clause: {} | WINDOW_SYM window_def_list - {} + {} ; window_def_list: @@ -12774,10 +12921,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(); @@ -12797,32 +12942,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), - "<select expression> INTO <destination>;", - "'SELECT <select list> INTO <destination>" - " FROM...'"); - $$= $2; - } + { + push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, + ER_WARN_DEPRECATED_SYNTAX, + ER_THD(thd, ER_WARN_DEPRECATED_SYNTAX), + "<select expression> INTO <destination>;", + "'SELECT <select list> INTO <destination>" + " FROM...'"); + $$= $2; + } ; - order_or_limit: order_clause opt_limit_clause { @@ -15208,16 +15366,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 f789cc0..cbbee0d 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 51 shift/reduce conflicts. + Currently there are 42 shift/reduce conflicts. We should not introduce new conflicts any more. */ -%expect 51 +%expect 42 /* 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 <lex_str> 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 <select_lex_unit> - 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 <boolfunc2creator> comp_op @@ -1512,7 +1517,9 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %type <order_limit_lock> query_expression_tail + opt_query_expression_tail order_or_limit + order_limit_lock opt_order_limit_lock %type <select_order> opt_order_clause order_clause order_list @@ -1678,7 +1685,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 <with_clause> opt_with_clause with_clause +%type <with_clause> with_clause %type <lex_str_ptr> 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,13 +6789,7 @@ parse_vcol_expr: ; parenthesized_expr: - remember_tok_start - query_expression - { - if (!($$= Lex->create_item_query_expression(thd, $1, $2))) - MYSQL_YYABORT; - } - | expr + expr | expr ',' expr_list { $3->push_front($1, thd->mem_root); @@ -6807,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 ; @@ -9240,7 +9251,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 : @@ -9250,10 +9261,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 : @@ -9279,9 +9291,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; } @@ -9352,108 +9366,258 @@ select_into_query_specification: } ; -opt_from_clause: - /* Empty */ - | from_clause +/** + + The following grammar for query expressions conformant to + the latest SQL Standard is supported: + + <query expression> ::= + [ <with clause> ] <query expression body> + [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ] + + <with clause> ::= + WITH [ RECURSIVE ] <with_list + + <with list> ::= + <with list element> [ { <comma> <with list element> }... ] + + <with list element> ::= + <query name> [ '(' <with column list> ')' ] + AS <table subquery> + + <with column list> ::= + <column name list> + + <query expression body> :: + <query term> + | <query expression body> UNION [ ALL | DISTINCT ] <query term> + | <query expression body> EXCEPT [ DISTINCT ] + + <query term> ::= + <query primary> + | <query term> INTERSECT [ DISTINCT ] <query primary> + + <query primary> ::= + <simple table> + | '(' <query expression body> + [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ] + ')' + + <simple table> + <query specification> + | <table value constructor> + + <subquery> + '(' <query_expression> ')' + +*/ + +/* + query_expression produces the same expressions as + <query expression> +*/ + +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 + <query expression> without [ <with clause> ] +*/ -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 + <query expression body> + [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ] + | '('... <query expression body> + [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ] + ')'... + 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_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_primary: + query_simple + { $$= $1; } + | query_expression_body_ext_parens + { $$= $1->first_select(); } + ; + +/* + query_simple produces the same expressions as + <simple table> +*/ + +query_simple: + simple_table { $$= $1;} ; subselect: - remember_tok_start query_expression { - if (!($$= Lex->parsed_subselect($2, $1))) + if (!($$= Lex->parsed_subselect($1, NULL))) YYABORT; } ; - -/** - <table expression>, as in the SQL standard. +/* + subquery produces the same expressions as + <subquery> + + Consider the production rule of the SQL Standard + subquery: + '(' query_expression_no_with_clause ')' + + 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, NULL))) + YYABORT; + } + | '(' with_clause query_expression_no_with_clause ')' + { + $3->set_with_clause($2); + $2->attach_to($3->first_select()); + if (!($$= Lex->parsed_subselect($3, NULL))) + YYABORT; + } + ; + +opt_from_clause: + /* empty */ %prec EMPTY_FROM_CLAUSE + | from_clause + ; + from_clause: FROM table_reference_list ; @@ -9929,15 +10093,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); @@ -10456,6 +10620,12 @@ primary_expr: column_default_non_parenthesized_expr | explicit_cursor_attr | '(' parenthesized_expr ')' { $$= $2; } + | subquery + { + if (!($$= Lex->create_item_query_expression(thd, NULL, + $1->master_unit()))) + MYSQL_YYABORT; + } ; string_factor_expr: @@ -12258,37 +12428,15 @@ table_primary_ident: } ; - -/* - Represents a flattening of the following rules from the SQL:2003 - standard. This sub-rule corresponds to the sub-rule - <table primary> ::= ... | <derived table> [ AS ] <correlation name> - - <derived table> ::= <table subquery> - <table subquery> ::= <subquery> - <subquery> ::= <left paren> <query expression> <right paren> - <query expression> ::= [ <with clause> ] <query expression body> - - 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 - { - if (!($$= Lex->parsed_derived_select($1, $2, $3))) - YYABORT; - } - | '(' - query_expression - ')' opt_for_system_time_clause table_alias_clause + subquery + 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 */ {} @@ -12422,7 +12570,6 @@ table_alias: opt_table_alias_clause: /* empty */ { $$=0; } - | table_alias_clause { $$= $1; } ; @@ -12884,10 +13031,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(); @@ -12907,29 +13052,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), - "<select expression> INTO <destination>;", - "'SELECT <select list> INTO <destination>" - " FROM...'"); - $$= $2; - } + { + push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, + ER_WARN_DEPRECATED_SYNTAX, + ER_THD(thd, ER_WARN_DEPRECATED_SYNTAX), + "<select expression> INTO <destination>;", + "'SELECT <select list> INTO <destination>" + " FROM...'"); + $$= $2; + } ; @@ -15340,16 +15498,6 @@ temporal_literal: } ; - -opt_with_clause: - /*empty */ { $$= 0; } - | with_clause - { - $$= $1; - } - ; - - with_clause: WITH opt_recursive {