revision-id: 8209c39b72c9cb22b52f977327ba34bf015e0812 (mariadb-10.4.5-120-g8209c39) parent(s): e8392e58b2a5a69f9c0bd5b5aed90348b4a0ccb3 author: Igor Babaev committer: Igor Babaev timestamp: 2019-09-20 09:03:38 -0700 message: MDEV-19956 Queries with subqueries containing UNION are not parsed Shift-Reduce conflicts prevented parsing some queries with subqueries that used set operations when the subqueries occurred in expressions or in IN predicands. The grammar rules for query expression were transformed in order to avoid these conflicts. New grammar rules employ an idea taken from MySQL 8.0. --- mysql-test/main/brackets.result | 4022 +++++++++++++++++++- mysql-test/main/brackets.test | 2318 +++++++++++ mysql-test/main/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 +- mysql-test/suite/compat/oracle/r/ps.result | 8 +- .../suite/compat/oracle/r/statement-expr.result | 16 +- 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 ++- 29 files changed, 7226 insertions(+), 592 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/mysql-test/suite/compat/oracle/r/ps.result b/mysql-test/suite/compat/oracle/r/ps.result index 73aa04b..4410438 100644 --- a/mysql-test/suite/compat/oracle/r/ps.result +++ b/mysql-test/suite/compat/oracle/r/ps.result @@ -47,7 +47,7 @@ EXECUTE stmt USING @a, @b; # PREPARE stmt FROM 'SELECT :1 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() RETURN VARCHAR AS @@ -155,7 +155,7 @@ DROP TABLE t1; # Testing disallowed expressions in USING # EXECUTE IMMEDIATE 'SELECT :1 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() RETURN VARCHAR AS BEGIN @@ -182,9 +182,9 @@ ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2 PREPARE stmt FROM _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_operator_oracle' 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/suite/compat/oracle/r/statement-expr.result b/mysql-test/suite/compat/oracle/r/statement-expr.result index ea3bd52..6208c58 100644 --- a/mysql-test/suite/compat/oracle/r/statement-expr.result +++ b/mysql-test/suite/compat/oracle/r/statement-expr.result @@ -13,9 +13,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) AS BEGIN NULL; END; @@ -47,21 +47,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/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..911d1a0 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 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')' + + 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..1158250 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 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 ')' + + 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 {