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.

13 files changed, 6405 insertions, 42 deletions

diff --git a/mysql-test/main/brackets.result b/mysql-test/main/brackets.result
index e14bef956a9..dedd9a2a2bf 100644
--- a/mysql-test/main/brackets.result
+++ b/mysql-test/main/brackets.result
@@ -355,7 +355,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 2	UNION	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
 NULL	UNION RESULT	<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 9ca86b87032..b7bb616bd05 100644
--- a/mysql-test/main/brackets.test
+++ b/mysql-test/main/brackets.test
@@ -176,5 +176,2323 @@ select * from t1;
 
 drop table t1;
 
+--echo #
+--echo # MDEV-19956: query expressions in different contexts
+--echo #
+
+create table t1 (a int);
+insert into t1 values (3), (7), (1), (2), (4);
+create table t2 (a int, b int);
+insert into t2  values (3,30), (7,70), (1,10), (2,20), (4,40);
+
+
+--echo # 1. select
+
+--echo # 1.1. simple select
+
+select * from t1;
+(select * from t1);
+((select * from t1));
+--echo # 1.2. select with tail
+select * from t1 order by a;
+select a from t1 order by a;
+select a from t1 order by 1;
+select * from t1 order by t1.a;
+(select * from t1 order by t1.a);
+((select * from t1 order by t1.a));
+(select * from t1 order by t1.a limit 2);
+(select a from t1 where a=1) order by 1 desc;
+
+--echo # 1.2. select with several tails
+
+(select * from t2 order by a limit 2) order by b desc;
+(select * from t2 order by t2.a limit 2) order by b desc;
+((select * from t2 order by t2.a limit 2) order by b desc);
+(((select * from t2 order by t2.a) limit 2) order by b desc);
+
+
+--echo # 2. union
+
+--echo # 2.1 simple union
+
+select a from t1 union select a from t1;
+select a from t1 union all select a from t1;
+select a from t1 union select b from t2;
+(select a from t1) union (select a from t1);
+(select a from t1) union (select b from t2);
+select a from t1 where a=1 union select a from t1 where a=3;
+(select a from t1 where a=1) union select a from t1 where a=3;
+((select a from t1 where a=1) union select a from t1 where a=3);
+((select a from t1 where a<=3) union (select a from t1 where a=3));
+select a from t1 where a=1 union (select a from t1 where a=3);
+(select a from t1 where a=1 union (select a from t1 where a=3));
+((select a from t1 where a=1 union (select a from t1 where a=3)));
+
+select a from t1 where a=1
+union
+select a from t1 where a=3
+union
+select a from t1 where a=7;
+
+( select a from t1 where a=1
+  union
+  select a from t1 where a=3
+  union
+  select a from t1 where a=7 );
+
+(select a from t1 where a=1 order by a) union select a from t1 where a=3;
+(select a from t1 where a!=3 order by a desc) union select a from t1 where a=3;
+((select a from t1 where a=1 order by a) union select a from t1 where a=3);
+(select a from t1 where a!=3 order by a desc) union select a from t1 where a=3;
+
+( ( select a from t1 where a!=3 order by a desc limit 3)
+  union
+  select a from t1 where a=3 );
+
+( select a from t1 where a <=3 except select a from t1 where a >=3 )
+  union
+  select a from t1 where a=7;
+
+( ( select a from t1 where a <=3
+    except
+    select a from t1 where a >=3 )
+   union
+   select a from t1 where a=7 );
+
+( select a from t1 where a <=3
+  except
+  ( select a from t1 where a >=3
+    union
+    select a from t1 where a=7 ) );
+
+( ( select a from t1 where a <=3 )
+  except
+  ( select a from t1 where a >=3
+    union
+    select a from t1 where a=7 ) );
+
+--echo # 2.2. union with tail
+
+select a from t1 where a=1 union select a from t1 where a=3 order by a desc;
+(select a from t1 limit 2) union select a from t1 where a=3 order by a desc;
+
+select a from t1 where a=4 union (select a from t1 where a <=4 limit 2)
+order by a desc;
+
+select a from t1 where a=4
+union
+(select a from t1 where a <=4 order by a limit 2)
+order by a desc;
+
+( select a from t1 where a=4
+  union
+  ( select a from t1 where a <=4 order by a limit 2 ) )
+order by a desc;
+
+( select a from t1 where a <=3 except select a from t1 where a >=3 )
+  union
+  select a from t1 where a=7 order by a desc;
+
+( select a from t1 where a!=3 order by a desc )
+  union
+  select a from t1 where a=3
+  order by a desc;
+
+(select a from t1 where a=1)
+union
+(select a from t1 where a=3)
+order by a desc;
+
+( select a from t1 where a=1
+  union
+  select a from t1 where a=3 )
+order by a desc;
+
+( ( select a from t1 where a=1 )
+  union
+  ( select a from t1 where a=3 ) )
+order by a desc;
+
+( select a from t1 where a=1
+  union
+  select a from t1 where a=3 )
+order by 1 desc;
+
+((select a from t1 where a=1 union select a from t1 where a=3)) order by 1 desc;
+(((select a from t1 where a=1) union (select a from t1 where a=3)))
+order by 1 desc;
+
+( (select a from t1 where a=1 )
+  union
+  (select a from t1 where a=3) )
+order by 1 desc;
+
+--echo # 2.3. complex union
+
+select a from t1 where a=1
+union
+select a from t1 where a=3
+union
+select a from t1 where a=2
+union
+select a from t1 where a=4;
+
+( select a from t1 where a=1
+  union
+  select a from t1 where a=3
+  union
+  select a from t1 where a=2 )
+union
+select a from t1 where a=4;
+
+(select a from t1 where a=1 union select a from t1 where a=3)
+union
+(select a from t1 where a=2 union select a from t1 where a=4);
+(select a from t1 where a=1 union (select a from t1 where a=3))
+union
+((select a from t1 where a=2) union select a from t1 where a=4);
+
+( ( select a from t1 where a=1)
+  union
+  select a from t1 where a=3 )
+union
+select a from t1 where a=2
+union
+select a from t1 where a=4;
+( ( ( select a from t1 where a=1)
+    union
+    select a from t1 where a=3 )
+  union
+  select a from t1 where a=2 )
+union
+select a from t1 where a=4;
+
+select a from t1 where a=1
+union
+select a from t1 where a=3
+union
+select a from t1 where a=2
+union
+(select a from t1 where a=4);
+
+select a from t1 where a=1
+union
+select a from t1 where a=3
+union
+( select a from t1 where a=2
+  union
+  ( select a from t1 where a=4 ) );
+
+select a from t1 where a=1
+union
+( select a from t1 where a=3
+  union
+  ( select a from t1 where a=2
+    union
+    ( select a from t1 where a=4 ) ) );
+
+--echo # 2.4. complex union with tail
+
+( ( select a from t1 where a=1 union select a from t1 where a=3 )
+  order by a desc )
+union
+( ( select a from t1 where a=2 union select a from t1 where a=4 )
+  order by a desc );
+
+( ( select a from t1 where a=1 union select a from t1 where a=3 )
+  order by a desc )
+union
+( ( select a from t1 where a=2 union select a from t1 where a=4 )
+  order by a desc )
+order by a;
+
+( select a from t1 where a=1
+  union
+  select a from t1 where a=3
+  union
+  select a from t1 where a=2  order by a desc limit 2 )
+union
+select a from t1 where a=4
+order by a;
+
+( select a from t1 where a=1
+  union
+  select a from t1 where a=3 order by a desc )
+union
+select a from t1 where a=2  order by a desc limit 2;
+
+( ( select a from t1 where a >= 2
+    union
+    select a from t1 where a=1 order by a desc limit 2 )
+  union
+  select a from t1 where a=3  order by a limit 2 )
+union
+select a from t1 where a=1;
+
+
+--echo # 3. TVC
+
+--echo # 3.1. simple TVC
+
+values (3), (7), (1);
+(values (3), (7), (1));
+((values (3), (7), (1)));
+
+--echo # 3.2. simple TVC with tail(s)
+
+values (3), (7), (1) order by 1;
+(values (3), (7), (1)) order by 1;
+((values (3), (7), (1))) order by 1;
+(((values (3), (7), (1))) order by 1);
+(values (3), (7), (1) limit 2) order by 1 desc;
+((values (3), (7), (1)) order by 1 desc) limit 2;
+(((values (3), (7), (1)) order by 1 desc) limit 2);
+
+--echo # 3.3. union of TVCs
+
+values (3), (7), (1) union values (3), (4), (2);
+values (3), (7), (1) union all values (3), (4), (2);
+values (3), (7), (1) union values (3), (4), (2);
+values (3), (7), (1) except values (3), (4), (2);
+(values (3), (7), (1)) union (values (3), (4), (2));
+(values (3), (7), (1)) union (values (3), (4), (2)) union values (5), (7);
+(values (3), (7), (1)) union (values (3), (4), (2)) union (values (5), (7));
+(values (3), (7), (1) union values (3), (4), (2)) union values (5), (7);
+values (3), (7), (1) union (values (3), (4), (2) union values (5), (7));
+(values (3), (7), (1) union ((values (3), (4), (2) union values (5), (7))));
+
+--echo # 3.4. tailed union of TVCs
+
+values (3), (7), (1) union values (3), (4), (2) order by 1;
+(values (3), (7), (1) union values (3), (4), (2)) order by 1;
+(values (3), (7), (1) union values (3), (4), (2)) order by 1;
+values (3), (7), (1) union (values (3), (4), (2)) order by 1;
+(values (3), (7), (1) union values (3), (4), (2)) order by 1;
+((values (3), (7), (1)) union values (3), (4), (2)) order by 1;
+
+--echo # 3.5. union of tailed TVCs
+
+(values (3), (7), (1) order by 1 limit 2)
+union
+(values (3), (4), (2) order by 1 desc limit 2);
+
+((values (3), (7), (1) order by 1) limit 2)
+union
+((values (3), (4), (2) order by 1 desc) limit 2);
+
+(((values (3), (7), (1)) order by 1) limit 2)
+union
+(((values (3), (4), (2)) order by 1 desc) limit 2);
+
+--echo # 3.6. tailed union of tailed TVCs
+
+(values (3), (7), (1) order by 1 limit 2)
+union
+values (3), (4), (2)
+order by 1;
+
+((values (3), (7), (1)) order by 1 limit 2)
+union
+((values (3), (4), (2) order by 1 desc) limit 2)
+order by 1;
+
+--echo # 3.7 [tailed] union of [tailed] select and [tailed] TVC
+
+(select a from t1 where a <=3 order by 1 limit 2)
+union
+(values (3), (4), (2) order by 1 desc limit 2);
+
+((select a from t1 where a <=3) order by 1 limit 2)
+union
+(values (3), (4), (2) order by 1 desc limit 2);
+
+(((select a from t1 where a <=3) order by a) limit 2)
+union
+(((values (3), (4), (2)) order by 1 desc) limit 2);
+
+( (((select a from t1 where a <=3) order by a) limit 2)
+  union
+  (((values (3), (4), (2)) order by 1 desc) limit 2) );
+
+(select a from t1 where a <=3 order by 1 limit 2)
+union
+(values (3), (4), (2) order by 1 desc limit 2)
+order by a;
+
+((select a from t1 where a <=3) order by 1 limit 2)
+union
+(values (3), (4), (2) order by 1 desc limit 2)
+order by a;
+
+(((select a from t1 where a <=3) order by a) limit 2)
+union
+(((values (3), (4), (2)) order by 1 desc) limit 2)
+order by a;
+
+(((values (3), (4), (2)) order by 1 desc) limit 2);
+( (((select a from t1 where a <=3) order by a) limit 2)
+  union
+  (((values (3), (4), (2)) order by 1 desc) limit 2) )
+order by a;
+
+(values (3), (4), (2) order by 1 desc limit 2)
+union
+(select a from t1 where a <=3 order by 1 limit 2);
+
+(values (3), (4), (2) order by 1 desc limit 2)
+union
+((select a from t1 where a <=3) order by 1 limit 2);
+
+(((values (3), (4), (2)) order by 1 desc) limit 2)
+union
+(((select a from t1 where a <=3) order by 1) limit 2);
+
+(((values (3), (4), (2)) order by 1 desc) limit 2)
+union
+(((select a from t1 where a <=3) order by a) limit 2)
+order by 1;
+
+( select a from t1 where a=1
+  union
+  values (3), (4), (2) order by 1 desc )
+union
+select a from t1 where a=2 order by a desc limit 3;
+
+
+--echo 4. CTE
+
+--echo 4.1. simple select with simple CTE
+
+with t as (select * from t1 where a <=3)
+select * from t;
+
+with t as (select * from t1 where a <=3)
+(select * from t);
+
+with t as (select * from t1 where a <=3)
+((select * from t));
+
+with t as ((select * from t1 where a <=3))
+select * from t;
+with t as (((select * from t1 where a <=3)))
+select * from t;
+
+--echo 4.2. tailed select with simple CTE
+
+with t as (select * from t1 where a <=3)
+select * from t order by a;
+
+with t as (select * from t1 where a <=3)
+(select * from t) order by a;
+
+with t as (select * from t1 where a <=3)
+(select * from t) order by a desc limit 2;
+
+--echo 4.3. [tailed] select with tailed CTE
+
+with t as (select * from t1 where a >=2 order by a limit 2)
+select * from t;
+
+with t as (((select * from t1 where a >=2) order by a desc) limit 2)
+select * from t;
+
+with t as (select * from t1 where a >=2 order by a desc limit 2)
+select * from t order by a;
+
+--echo 4.4. [tailed] union with CTE
+
+with t as (select * from t1 where a <=3)
+select a from t1 where a=1 union select a from t where a=3;
+
+with t as (select * from t1 where a <=3)
+(select a from t) union (select b from t2);
+
+with t as (select * from t1 where a <=3)
+(select a from t) union (select b as a from t2) order by a desc;
+
+--echo 4.5. [tailed] union with [tailed] union in CTE
+
+with t as (select * from t1 where a < 3 union select * from t1 where a > 3)
+select a from t1 where a=1 union select a from t where a=7;
+
+with t as
+( select * from t1 where a < 3
+  union
+  select * from t1 where a > 3
+  order by a desc limit 3 )
+select a from t1 where a=4 union select a from t where a=7;
+
+with t as
+( select * from t1 where a < 3
+  union
+  select * from t1 where a > 3
+  order by a desc limit 3 )
+select a from t1 where a=4 union select a from t where a=7 order by a desc;
+
+with t as
+( (select * from t1 where a < 3)
+  union
+  (select * from t1 where a > 3)
+  order by a desc limit 3 )
+select a from t1 where a=4 union select a from t where a=7 order by a desc;
+
+with t as
+( (select * from t1 where a < 3)
+  union
+  (select * from t1 where a > 3)
+  order by a desc limit 3 )
+(select a from t1 where a=4 union select a from t where a=7 order by a desc);
+
+with t as
+( (select * from t1 where a < 3)
+  union
+  (select * from t1 where a > 3)
+  order by a desc limit 3 )
+((select a from t1 where a=4 union select a from t where a=7) order by a desc);
+
+with t as
+( select * from t1 where a < 3
+  union
+  values (4), (7)
+  order by a desc limit 3 )
+select a from t1 where a=4 union select a from t where a=7 order by a desc;
+
+
+--echo 4.6. [tailed] union with [tailed] union of TVC in CTE
+
+with t(a) as
+( values (2), (1)
+  union
+  (values (4), (7))
+  order by 1 desc limit 3 )
+select a from t1 where a=4 union select a from t where a=7 order by a desc;
+
+with t(a) as
+( (values (2), (1))
+  union
+  (values (4), (7) order by 1 desc)
+  order by 1 desc limit 3 )
+select a from t1 where a=1 union select a from t where a=7 order by a desc;
+
+with t(a) as
+( (values (2), (1))
+  union
+  (values (4), (7) order by 1 desc)
+  order by 1 limit 3 )
+select a from t where a=1 union values (7) order by a desc;
+
+with t(a) as
+( (values (2), (1))
+  union
+  (values (4), (7) order by 1 desc ) )
+select a from t where a=1 union select 7 order by a desc;
+
+--echo 4.5. [tailed] union with two CTEs
+
+with t as (select * from t1 where a < 3),
+     s as (select * from t1 where a > 3)
+select a from t where a=1 union select a from s where a=7 order by a desc;
+
+with t as (select * from t1 where a < 3),
+     s as (select * from t1 where a > 3)
+(select a from t where a=1 union select a from s where a=7 order by a desc);
+
+with t as (select * from t1 where a < 3),
+     s as (select * from t1 where a > 3)
+(select a from t where a=1 union select a from s where a=7) order by a desc;
+
+with t as (select * from t1 where a < 3),
+     s as (select * from t where a > 3)
+select a from t where a=1 union select a from s where a=7 order by a desc;
+
+
+--echo # 5. single-row subquery in expression
+
+--echo # 5.1. [tailed] simple select in expression
+
+select (a+1) + b as r from t2;
+select ((a+1) + b) as r from t2;
+select (b + (select 1)) as r from t2;
+select (select a from t1 where a <=3 order by a desc limit 1) as r from t2;
+
+select
+(select a from t1 where a <=3 order by a desc limit 1) as r from t2;
+
+select (select 100) as r from t2;
+select ((select 100)) as r from t2;
+select (select 100) + t2.b as r from t2;
+select ((select 100) + t2.b) as r from t2;
+
+--echo # 5.2. [tailed] TVC in expression
+
+select (values (200)) as r from t2;
+select ((values (200))) as r from t2;
+select (values (200)) + t2.b as r from t2;
+select ((values (200)) + t2.b) as r from t2;
+select (values (200), (300) order by 1 desc limit 1) as r from t2;
+select ((values (200), (300)) order by 1 desc limit 1) as r from t2;
+select (select * from t1 limit 1) as r from t2;
+select (select * from t1 order by a limit 1) as r from t2;
+select ((select * from t1 order by a limit 1)) as r from t2;
+((select ((select * from t1 order by a limit 1)) as r from t2));
+select (select * from t1 order by a limit 1) + t2.b as r from t2;
+
+--echo # 5.3. [tailed] union in expression
+
+select
+( select a from t1 where a<3  union select a from t1 where a>4
+  order by a desc limit 1 ) as r
+from t1;
+
+select
+( (select a from t1 where a<3)  union (select a from t1 where a>4)
+  order by a desc limit 1 ) as r
+from t1;
+
+select
+( select a from t1 where a<3  union select a from t1 where a>4
+  order by a desc limit 1 ) + t1.a as r
+from t1;
+
+select
+t1.a +
+( select a from t1 where a<3  union select a from t1 where a>4
+  order by a desc limit 1 ) as r
+from t1;
+
+select
+( (select a from t1 where a<3 union select a from t1 where a>4
+  order by a desc limit 1 ) + t1.a) as r
+from t1;
+
+select
+( ( (select a from t1 where a<3)  union (select a from t1 where a>4)
+  order by a desc limit 1 ) + t1.a ) as r
+from t1;
+
+--echo # 5.4. [tailed] select with simple CTE in expression
+
+select
+( with t as (select * from t1 where a <=3)
+  select a from t limit 1) as r
+from t2;
+
+select
+( with t as (select * from t1 where a <=3)
+  select a from t limit 1) + t2.b as r
+from t2;
+
+select
+t2.b +( with t as (select * from t1 where a <=3)
+         select a from t limit 1) as r
+from t2;
+
+select
+((( with t as (select * from t1 where a <=3)
+  select a from t limit 1) + t2.b)) as r
+from t2;
+
+select
+( with t as (select * from t1 where a <=3)
+  select a from t limit 1) + 100 as r
+from t2;
+
+select
+( with t as (select * from t1 where a <=3)
+  select a from t limit 1) + (select 100) as r
+from t2;
+
+select
+( with t as (select * from t1 where a <=3)
+  select a from t limit 1) + t2.b + (select 100) as r
+from t2;
+
+select
+( with t as (select * from t1 where a <=3)
+  select a from t limit 1 ) + (t2.b + (select 100)) as r
+from t2;
+
+select
+( with t as (select * from t1 where a <=3)
+  select a from t limit 1 ) + t2.b + (values (100)) as r
+from t2;
+
+--echo # 5.5. [tailed] union with simple CTE in expression
+
+select
+( with t as (select * from t1 where a <=3)
+  select a from t union select b from t2 order by a desc limit 1) as r
+from t2;
+
+select
+( with t as (select * from t1 where a <=3)
+  (select a from t) union (select b from t2) order by a desc limit 1) as r
+from t2;
+
+select
+( with t as (select * from t1 where a <=3)
+  (select a from t) union (select b from t2) order by a desc limit 1) as r
+from t2;
+
+select
+( ( with t as (select * from t1 where a <=3)
+    (select a from t) union (select b from t2) order by a desc limit 1) +
+  t2.a ) as r
+from t2;
+
+--echo # 5.6. [tailed] union with CTE with union in expression
+
+select
+( with t as
+  ( select * from t1 where a < 3
+    union
+    select * from t1 where a > 3
+    order by a desc limit 3 )
+  select a from t1 where a=4 union select a from t where a=7 limit 1) as r
+from t2;
+
+select
+( with t as
+  ( select * from t1 where a < 3
+    union
+    select * from t1 where a > 3
+    order by a desc limit 3 )
+  select a from t1 where a=4 union select a from t where a=7 limit 1) +
+t2. b as r
+from t2;
+
+--echo # 5.7. [tailed] union of TVCs with CTE with union in expression
+
+select
+( with t(a) as
+  ( (values (2), (1))
+    union
+    (values (4), (7) order by 1 limit 1)
+    order by 1 desc limit 3 ) select * from t limit 1 ) + t2.b as r
+from t2;
+
+select
+( with t(a) as
+  ( select 2 union select 1
+    union
+    (values (4), (7) order by 1 limit 1)
+    order by 1 limit 3 ) select * from t limit 1 ) + t2.b as r
+from t2;
+
+
+--echo # 6. subquery
+
+--echo # 6.1. TVC in IN subquery
+
+select a from t1 where a in (1,8,7);
+select a from t1 where a in (values (1), (8), (7));
+
+--echo # 6.2. simple select in IN subquery
+
+select a from t1 where a in (select a from t2 where a <= 3);
+select a from t1 where a in ((select a from t2 where a <= 3));
+
+--echo # 6.3. union in IN subquery
+
+select a from t1
+where a in (select a from t1 where a<=2 union select a from t2 where b>40);
+
+select a from t1
+where a in (select a from t1 where a<=2 union (select a from t2 where b>40));
+
+select a from t1
+where a in ((select a from t1 where a<=2) union select a from t2 where b>40);
+
+select a from t1
+where a in ((select a from t1 where a<=2) union (select a from t2 where b>40));
+
+--echo # 6.4. select with CTE and union in IN subquery
+
+with t as (select a from t1 where a<=2)
+select a from t1
+where a in ((select a from t) union (select a from t2 where b>40));
+
+with t as ((select a from t1 where a<=2))
+select a from t1
+where a in ((select a from t) union (select a from t2 where b>40));
+
+with t as ((select a from t1 where a<=2) order by a desc limit 1)
+select a from t1
+where a in ((select a from t) union (select a from t2 where b>40));
+
+
+--echo # 6.5. NOT IN subquery
+
+select a from t1 where a not in (1,8,7);
+select a from t1 where a not in (values (1), (8), (7));
+select a from t1 where a not in (select a from t2 where a <= 3);
+select a from t1 where a not in ((select a from t2 where a <= 3));
+
+select a from t1
+where a not in (select a from t1 where a<=2
+                union
+                select a from t2 where b>40);
+
+select a from t1
+where a not in (select a from t1 where a<=2
+                union
+                (select a from t2 where b>40));
+
+select a from t1
+where a not in ((select a from t1 where a<=2)
+                union
+                select a from t2 where b>40);
+
+select a from t1
+where a not in ((select a from t1 where a<=2)
+                union
+                (select a from t2 where b>40));
+
+with t as ((select a from t1 where a<=2) order by a desc limit 1)
+select a from t1
+where a not in ((select a from t) union (select a from t2 where b>40));
+
+--echo # 6.6. IN subquery in expression
+
+select 1 in (select a from t1) as r, b from t2 where b > 30;
+select (1 in (select a from t1)) as r, b from t2 where b > 30;
+select 1 in ((select a from t1)) as r, b from t2 where b > 30;
+select ((1 in ((select a from t1)))) as r, b from t2 where b > 30;
+select ((1 in ((select a from t1)))) as r, b from t2 where b > 30;
+select b, if (a in (select a from t1 where a > 3),10,20) as r from t2;
+select b, if (a in ((select a from t1 where a > 3)),10,20) as r from t2;
+
+--echo # 6.7. IN subquery in SF and SP
+
+create function f1(x int) returns int
+return (x in ((select a from t1 where a <= 4)));
+select b, f1(a) from t2 where b > 20;
+drop function f1;
+delimiter |;
+create function f2(x int) returns int
+if x in ((select a from t1 where a <= 4))
+  then return 100;
+  else return 200;
+end if |
+delimiter ;|
+select b, f2(a) from t2 where b > 20;
+drop function f2;
+
+--echo # 6.8. EXISTS subquery
+
+select exists (select a from t1 where t1.a=t2.a) as r, b from t2 where b > 30;
+select exists ((select a from t1 where t1.a=t2.a)) as r, b from t2 where b > 30;
+with s as
+( (select * from t1 where a <=4 order by 1 desc limit 2)
+  union
+  values (3), (8), (7) )
+select * from t2 where exists ((select * from s where s.a=t2.a));
+with t as ((select a from t1 where a<=2) order by a desc limit 1)
+select a from t2
+where not exists ((select a from t where t.a=t2.a)
+                  except
+                  (select a from t where a>40));
+
+--echo # 6.9. EXISTS subquery with SF and SP
+
+create function f1(x int) returns int
+return exists (((select * from t1 where x=a and a <= 4)));
+select b, f1(a) from t2 where b > 20;
+drop function f1;
+
+delimiter |;
+create function f2(x int) returns int
+if not exists (((select * from t1 where x=a and a <= 4)))
+  then return 100;
+  else return 200;
+end if |
+delimiter ;|
+select b, f2(a) from t2 where b > 20;
+drop function f2;
+
+--echo # 6.10. subquery with ANY
+
+select a from t1 where a = any(select a from t2 where a <= 3);
+select a from t1 where a = any((select a from t2 where a <= 3));
+
+select a from t1
+where a = any (select a from t1 where a<=2
+               union
+               select a from t2 where b>40);
+
+select a from t1
+where a = any(select a from t1 where a<=2
+              union
+              (select a from t2 where b>40));
+
+select a from t1
+where a = any((select a from t1 where a<=2)
+              union
+              select a from t2 where b>40);
+
+select a from t1
+where a = any((select a from t1 where a<=2)
+              union
+              (select a from t2 where b>40));
+
+
+--echo # 7. create table as
+
+--echo # 7.1. create table as simple select
+
+create table t as select * from t1 where a <=3;
+select * from t;
+drop table t;
+
+create table t select * from t1 where a <=3;
+select * from t;
+drop table t;
+
+create table t as (select * from t1 where a <=3);
+select * from t;
+drop table t;
+
+create table t (select * from t1 where a <=3);
+select * from t;
+drop table t;
+
+create table t as ((select * from t1 where a <=3));
+select * from t;
+drop table t;
+
+create table t ((select * from t1 where a <=3));
+select * from t;
+drop table t;
+
+create table t(a decimal(10,2)) as select * from t1 where a <=3;
+select * from t;
+drop table t;
+
+create table t(a decimal(10,2)) select * from t1 where a <=3;
+select * from t;
+drop table t;
+
+create table t(a decimal(10,2)) as (select * from t1 where a <=3);
+select * from t;
+drop table t;
+
+create table t(a decimal(10,2)) (select * from t1 where a <=3);
+select * from t;
+drop table t;
+
+create table t(a decimal(10,2)) as ((select * from t1 where a <=3));
+select * from t;
+drop table t;
+
+create table t(a decimal(10,2)) ((select * from t1 where a <=3));
+select * from t;
+drop table t;
+
+create table t(a decimal(10,2), b int) as
+  ((select a, a as b from t1 where a <=3));
+select * from t;
+drop table t;
+
+create table t(a decimal(10,2), b int)
+  ((select a, a as b from t1 where a <=3));
+select * from t;
+drop table t;
+
+--echo # 7.2. create table as tailed select
+
+create table t as select * from t1 where a <=3 order by 1;
+select * from t;
+drop table t;
+
+create table t select * from t1 where a <=3 order by 1;
+select * from t;
+drop table t;
+
+create table t as select * from t1 where a <=3 order by 1 desc limit 2;
+select * from t;
+drop table t;
+
+create table t select * from t1 where a <=3 order by 1 desc limit 2;
+select * from t;
+drop table t;
+
+create table t as ((select * from t1 where a <=3) order by 1 desc) limit 2;
+select * from t;
+drop table t;
+
+create table t ((select * from t1 where a <=3) order by 1 desc) limit 2;
+select * from t;
+drop table t;
+
+--echo # 7.3. create table as select wihout from clause
+
+create table t as select 10;
+select * from t;
+drop table t;
+
+create table t select 10;
+select * from t;
+drop table t;
+
+--echo # 7.4. create table as union of selects wihout from clause
+
+create table t as select 10 union select 70;
+select * from t;
+drop table t;
+
+create table t select 10 union select 70;
+select * from t;
+drop table t;
+
+--echo # 7.5. create table as TVC
+
+create table t as values (7), (3), (8);
+select * from t;
+drop table t;
+
+create table t values (7), (3), (8);
+select * from t;
+drop table t;
+
+create table t as (values (7), (3), (8));
+select * from t;
+drop table t;
+
+create table t (values (7), (3), (8));
+select * from t;
+drop table t;
+
+create table t as ((values (7), (3), (8)));
+select * from t;
+drop table t;
+
+create table t ((values (7), (3), (8)));
+select * from t;
+drop table t;
+
+--echo # 7.6. create table as select with CTE
+
+create table t as
+with s(a) as (select * from t1 where a <=3 order by 1 desc limit 2)
+select * from s;
+select * from t;
+drop table t;
+
+create table t
+with s(a) as (select * from t1 where a <=3 order by 1 desc limit 2)
+select * from s;
+select * from t;
+drop table t;
+
+create table t as
+with s as
+( (select * from t1 where a <=4 order by 1 desc limit 2)
+  union
+  values (3), (8), (7) )
+select * from s;
+select * from t;
+drop table t;
+
+create table t
+with s as
+( (select * from t1 where a <=4 order by 1 desc limit 2)
+  union
+  values (3), (8), (7) )
+select * from s;
+select * from t;
+drop table t;
+
+create table t as
+with s(a) as (select * from t1 where a <=3 order by 1 desc limit 2)
+select * from s;
+select * from t;
+drop table t;
+
+--echo # 7.7. create table as union with CTE
+
+create table t as
+with s as
+( (select * from t1 where a <=4 order by 1 desc limit 2)
+  union
+  values (3), (8), (7) )
+select * from s where a>=7 union select a from t2 where b<40;
+select * from t;
+drop table t;
+
+create table t
+with s as
+( (select * from t1 where a <=4 order by 1 desc limit 2)
+  union
+  values (3), (8), (7) )
+select * from s where a>=7 union select a from t2 where b<40;
+select * from t;
+drop table t;
+
+create table t
+with s as
+( (select * from t1 where a <=4 order by 1 desc limit 2)
+  union
+  values (3), (8), (7) )
+select * from s where a>=7 union select a from t2 where b<40;
+select * from t;
+drop table t;
+
+create table t as
+with s as
+( ( (select * from t1 where a <=4 order by 1 desc limit 2)
+    union
+    values (3), (8), (7) ) )
+select * from s where a>=7 union select a from t2 where b<40;
+select * from t;
+drop table t;
+
+create table t
+with s as
+( ( (select * from t1 where a <=4 order by 1 desc limit 2)
+    union
+    values (3), (8), (7) ) )
+select * from s where a>=7 union select a from t2 where b<40;
+select * from t;
+drop table t;
+
+create table t as
+with s as
+( (select * from t1 where a <=4 order by 1 desc limit 2)
+  union
+  values (3), (8), (7) )
+select * from s where a>=7 union select a from s where a<4;
+select * from t;
+drop table t;
+
+create table t
+with s as
+( (select * from t1 where a <=4 order by 1 desc limit 2)
+  union
+  values (3), (8), (7) )
+select * from s where a>=7 union select a from s where a<4;
+select * from t;
+drop table t;
+
+create table t as
+with s as
+( select * from t1 where a <=4 or a=7 )
+select * from s where a>=7 union select a from s where a<3;
+select * from t;
+drop table t;
+
+create table t
+with s as
+(select * from t1 where a <=4 or a=7)
+select * from s where a>=7 union select a from s where a<3;
+select * from t;
+drop table t;
+
+create table t (a int)
+with s as
+( select * from t1 where a <=4 or a=7 )
+select * from s where a>=7 union select a from s where a<3;
+select * from t;
+drop table t;
+
+create table t (a int)
+with s as
+(select * from t1 where a <=4 or a=7)
+select * from s where a>=7 union select a from s where a<3;
+select * from t;
+drop table t;
+
+create table t
+with s as
+( select * from t1 where a <=4 or a=7 )
+select * from s where a>=7 union select a from s where a<3
+order by a desc limit 2;
+select * from t;
+drop table t;
+
+create table t
+( with s as
+  ( select * from t1 where a <=4 or a=7 )
+  select * from s where a>=7 union select a from s where a<3
+  order by a desc limit 2 );
+select * from t;
+drop table t;
+
+
+--echo # 8. insert
+
+create table t (c int, d int);
+
+--echo # 8.1. insert simple select
+
+insert into t select * from t2 where a <=3;
+select * from t;
+delete from t;
+
+insert into t(c) select t2.a from t2 where a <=3;
+select * from t;
+delete from t;
+
+insert into t (select * from t2 where a <=3);
+select * from t;
+delete from t;
+
+insert into t(c) (select t2.a from t2 where a <=3);
+select * from t;
+delete from t;
+
+insert into t ((select * from t2 where a <=3));
+select * from t;
+delete from t;
+
+insert into t(c) ((select t2.a from t2 where a <=3));
+select * from t;
+delete from t;
+
+drop table t;
+create table t(c decimal(10,2));
+
+insert into t select * from t1 where a <=3;
+select * from t;
+delete from t;
+
+insert into t(c) select * from t1 where a <=3;
+select * from t;
+delete from t;
+
+insert into t (select * from t1 where a <=3);
+select * from t;
+delete from t;
+
+insert into t(c) (select * from t1 where a <=3);
+select * from t;
+delete from t;
+
+insert into t ((select * from t1 where a <=3));
+select * from t;
+delete from t;
+
+insert into t(c) ((select * from t1 where a <=3));
+select * from t;
+delete from t;
+
+drop table t;
+create table t(a decimal(10,2), b int);
+
+insert into t ((select * from t2 where a <=3));
+select * from t;
+delete from t;
+
+insert into t(a) ((select a from t2 where a <=3));
+select * from t;
+delete from t;
+
+drop table t;
+create table t(c int, d int);
+
+--echo # 8.2. insert tailed select
+
+insert into t select * from t2 where a <=3 order by 1;
+select * from t;
+delete from t;
+
+insert into t(c) select a from t2 where a <=3 order by 1;
+select * from t;
+delete from t;
+
+insert into t select * from t2 where a <=3 order by 1 desc limit 2;
+select * from t;
+delete from t;
+
+insert into t(c) select a from t2 where a <=3 order by 1 desc limit 2;
+select * from t;
+delete from t;
+
+insert into t ((select * from t2 where a <=3) order by 1 desc) limit 2;
+select * from t;
+delete from t;
+
+insert into t(c) ((select a from t2 where a <=3) order by 1 desc) limit 2;
+select * from t;
+delete from t;
+
+--echo # 8.3. insert select without from clause
+
+insert into t select 10, 20;
+select * from t;
+delete from t;
+
+insert into t(c) select 10;
+select * from t;
+delete from t;
+
+--echo # 8.4. insert union of selects without from clause
+
+insert into t select 10,20 union select 70,80;
+select * from t;
+delete from t;
+
+insert into t(c) select 10 union select 70;
+select * from t;
+delete from t;
+
+--echo # 8.5. insert TVC
+
+insert into t values (7,70), (3,30), (8,80);
+select * from t;
+delete from t;
+
+insert into t(c) values (7), (3), (8);
+select * from t;
+delete from t;
+
+insert into t (values (7,70), (3,30), (8,80));
+select * from t;
+delete from t;
+
+insert into t(c) (values (7), (3), (8));
+select * from t;
+delete from t;
+
+insert into t ((values (7,70), (3,30), (8,80)));
+select * from t;
+delete from t;
+
+insert into t(c) ((values (7), (3), (8)));
+select * from t;
+delete from t;
+
+--echo # 8.7. insert simple select  with CTE
+
+insert into t
+with s(a,b) as (select * from t2 where a <=3 order by 1 desc limit 2)
+select * from s;
+select * from t;
+delete from t;
+
+insert into t(c)
+with s(a) as (select a from t2 where a <=3 order by 1 desc limit 2)
+select * from s;
+select * from t;
+delete from t;
+
+insert into t
+with s as
+( (select * from t2 where a <=4 order by 1 desc limit 2)
+  union
+  values (3,30), (8,80), (7,70) )
+select * from s;
+select * from t;
+delete from t;
+
+insert into t(c)
+with s as
+( (select a from t2 where a <=4 order by 1 desc limit 2)
+  union
+  values (3), (8), (7) )
+select * from s;
+select * from t;
+delete from t;
+
+--echo # 8.8. insert into union with CTE
+insert into t(c)
+with s as
+( (select a from t2 where a <=4 order by 1 desc limit 2)
+  union
+  values (3), (8), (7) )
+select * from s where a>=7 union select a from t2 where b<40;
+select * from t;
+delete from t;
+
+insert into t
+with s as
+( (select * from t2 where a <=4 order by 1 desc limit 2)
+  union
+  values (3,30), (8,80), (7,70) )
+select * from s where a>=7 union select * from s where a<4;
+select * from t;
+delete from t;
+
+insert into t(c)
+with s as
+( (select a from t2 where a <=4 order by 1 desc limit 2)
+  union
+  values (3), (8), (7) )
+select * from s where a>=7 union select * from s where a<4;
+select * from t;
+delete from t;
+
+insert into t
+with s as
+( select * from t2 where a <=4 or a=7 )
+select * from s where a>=7 union select * from s where a<3;
+select * from t;
+delete from t;
+
+insert into t(c)
+with s as
+( select a from t2 where a <=4 or a=7 )
+select * from s where a>=7 union select * from s where a<3;
+select * from t;
+delete from t;
+
+drop table t;
+
+
+--echo # 9. derived table
+
+--echo # 9.1. derived table as [tailed] simple select
+
+select * from (select * from t1) as dt;
+select * from ((select * from t1)) as dt;
+select * from (((select * from t1))) as dt;
+select * from (select * from t1 order by a) as dt;
+select * from (select a from t1 order by a) as dt;
+select * from (select a from t1 order by 1) as dt;
+select * from (select a from t1 order by t1.a) as dt;
+select * from ((select * from t1 order by t1.a limit 2)) as dt;
+select * from ((select * from t2 order by a limit 2) order by b desc) dt;
+select * from ((select a from t1 where a=1) order by 1 desc) dt;
+
+--echo # 9.2. derived table as select with two tails
+
+select * from
+((select * from t2 order by t2.a limit 2) order by b desc) dt;
+
+select * from
+((select * from t2 order by t2.a limit 2) order by b desc) as dt;
+
+select * from
+(((select * from t2 order by t2.a limit 2) order by b desc )) as dt;
+
+select * from
+(((select * from t2 order by t2.a) limit 2) order by b desc) dt;
+
+select * from
+((select * from t2 order by a limit 2) order by b desc) dt;
+
+select * from
+((select a from t1 where a=1) order by 1 desc) as dt;
+
+select * from
+((select * from t2 order by t2.a limit 2) order by b desc) as dt;
+
+
+--echo # 9.3. derived table as union
+
+select * from (select a from t1 union select a from t1) as dt;
+select * from (select a from t1 union all select a from t1) as dt;
+select * from (select a from t1 union select b from t2) as dt;
+
+select * from
+((select a from t1) union (select a from t1)) as dt;
+
+select * from
+((select a from t1) union (select b from t2)) as dt;
+
+select * from
+(select a from t1 where a=1 union select a from t1 where a=3) dt;
+
+select * from
+((select a from t1 where a=1) union select a from t1 where a=3) dt;
+
+select * from
+(((select a from t1 where a=1) union select a from t1 where a=3)) dt;
+
+select * from
+(((select a from t1 where a<=3) union (select a from t1 where a=3))) as dt;
+
+select * from
+(select a from t1 where a=1 union (select a from t1 where a=3)) as dt;
+
+select * from
+((select a from t1 where a=1 union (select a from t1 where a=3))) as dt;
+
+select * from
+(((select a from t1 where a=1 union (select a from t1 where a=3)))) as dt;
+
+select * from
+( select a from t1 where a=1
+    union
+  select a from t1 where a=3
+    union
+  select a from t1 where a=7 ) as dt;
+
+select * from
+( (select a from t1 where a=1 order by a)
+  union
+  select a from t1 where a=3 ) as dt;
+
+select * from
+( (select a from t1 where a!=3 order by a desc)
+  union
+  select a from t1 where a=3 ) as dt;
+
+select * from
+( ( select a from t1 where a <=3 except select a from t1 where a >=3 )
+  union
+  select a from t1 where a=7 ) as dt;
+
+select * from
+( ( ( select a from t1 where a <=3
+      except
+      select a from t1 where a >=3 )
+    union
+    select a from t1 where a=7 )  ) as dt;
+
+select * from
+( select a from t1 where a=1
+  union
+  select a from t1 where a=3
+  order by a desc) as dt;
+
+select *from
+( (select a from t1 limit 2)
+  union
+  select a from t1 where a=3
+  order by a desc) as dt;
+
+select * from
+( select a from t1 where a=4
+  union
+  (select a from t1 where a <=4 limit 2)
+  order by a desc ) as dt;
+
+select * from
+( ( select a from t1 where a=4
+    union
+    ( select a from t1 where a <=4 order by a ) )
+  order by a desc limit 2 ) as dt;
+
+select * from
+( ( select a from t1 where a <=3 except select a from t1 where a >=3 )
+    union
+    select a from t1 where a=7 order by a desc ) as dt;
+
+select * from
+( ( select a from t1 where a!=3 order by a desc )
+    union
+    select a from t1 where a=3
+    order by a desc ) as dt;
+
+select * from
+( (select a from t1 where a=1)
+  union
+  (select a from t1 where a=3)
+  order by a desc ) as dt;
+
+select * from
+( ( select a from t1 where a=1
+    union
+    select a from t1 where a=3 )
+  order by a desc ) as dt;
+
+select * from
+( ( ( select a from t1 where a=1 )
+    union
+    ( select a from t1 where a=3 ) )
+  order by a desc ) as dt;
+
+select * from
+( ( select a from t1 where a=1
+    union
+    select a from t1 where a=3 )
+  order by 1 desc ) as dt;
+
+select * from
+( ( (select a from t1 where a=1
+     union
+     select a from t1 where a=3) ) order by 1 desc ) as dt;
+
+select * from
+((((select a from t1 where a=1) union (select a from t1 where a=3)))
+ order by 1 desc ) as dt;
+
+select * from
+( ( (select a from t1 where a=1 )
+    union
+    (select a from t1 where a=3) )
+  order by 1 desc ) as dt;
+
+select * from
+( select a from t1 where a=1
+  union
+  select a from t1 where a=3
+  union
+  select a from t1 where a=2
+  union
+  select a from t1 where a=4 ) as dt;
+
+select * from
+( ( select a from t1 where a=1
+    union
+    select a from t1 where a=3
+    union
+    select a from t1 where a=2 )
+  union
+  select a from t1 where a=4 ) as dt;
+
+select * from
+( (select a from t1 where a=1 union select a from t1 where a=3)
+  union
+  (select a from t1 where a=2 union select a from t1 where a=4) ) as dt;
+
+select * from
+( (select a from t1 where a=1 union (select a from t1 where a=3))
+  union
+  ((select a from t1 where a=2) union select a from t1 where a=4) ) as dt;
+
+select * from
+( ( ( select a from t1 where a=1)
+    union
+    select a from t1 where a=3 )
+    union
+    select a from t1 where a=2
+    union
+    select a from t1 where a=4 ) as dt;
+
+select * from
+( ( ( ( select a from t1 where a=1)
+      union
+      select a from t1 where a=3 )
+    union
+    select a from t1 where a=2 )
+  union
+  select a from t1 where a=4 ) as dt;
+
+select * from
+( select a from t1 where a=1
+  union
+  select a from t1 where a=3
+  union
+  select a from t1 where a=2
+  union
+  (select a from t1 where a=4) ) as dt;
+
+select * from
+( select a from t1 where a=1
+  union
+  select a from t1 where a=3
+  union
+  ( select a from t1 where a=2
+    union
+    ( select a from t1 where a=4 ) ) ) as dt;
+
+select * from
+( select a from t1 where a=1
+  union
+  ( select a from t1 where a=3
+    union
+    ( select a from t1 where a=2
+      union
+      ( select a from t1 where a=4 ) ) ) ) as dt;
+
+select * from
+( ( ( select a from t1 where a=1 union select a from t1 where a=3 )
+    order by a desc limit 2 )
+  union
+  ( ( select a from t1 where a=2 union select a from t1 where a=4 )
+    order by a desc limit 1 ) ) as dt;
+
+select * from
+( ( ( select a from t1 where a=1 union select a from t1 where a=3 )
+    order by a desc limit 2 )
+  union
+  ( ( select a from t1 where a=2 union select a from t1 where a=4 )
+    order by a desc limit 2 )
+  order by a) as dt;
+
+select * from
+( ( select a from t1 where a=1
+    union
+    select a from t1 where a=3
+    union
+    select a from t1 where a=2  order by a desc limit 2 )
+  union
+  select a from t1 where a=4
+  order by a limit 3 ) as dt;
+
+select * from
+( ( select a from t1 where a=1
+    union
+    select a from t1 where a=3 order by a desc limit 2)
+  union
+  select a from t1 where a=2  order by a desc limit 2 ) as dt;
+
+select * from
+( ( ( select a from t1 where a >= 2
+      union
+      select a from t1 where a=1 order by a desc limit 2 )
+    union
+    select a from t1 where a=3  order by a limit 2 )
+  union
+  select a from t1 where a=1 ) as dt;
+
+--echo # 9.3. derived table as [tailed] TVC
+
+select * from
+( values (3), (7), (1) ) as dt;
+
+select * from
+( (values (3), (7), (1)) ) as dt;
+
+select * from
+(((values (3), (7), (1)))) as dt;
+
+select * from
+( values (3), (7), (1) order by 1 limit 2 ) as dt;
+
+select * from
+( (values (3), (7), (1)) order by 1 limit 2 ) as dt;
+
+select * from
+( ((values (3), (7), (1))) order by 1 limit 2 ) as dt;
+
+select * from
+( (((values (3), (7), (1))) order by 1 limit 2) ) as dt;
+
+select * from
+( ( (values (3), (7), (1) limit 2) order by 1 desc) ) as dt;
+
+select * from
+( ((values (3), (7), (1)) order by 1 desc) limit 2 ) as dt;
+
+select * from
+( (((values (3), (7), (1)) order by 1 desc) limit 2) ) as dt;
+
+--echo # 9.3. derived table as union of TVCs
+
+select * from
+( values (3), (7), (1) union values (3), (4), (2) ) dt;
+
+select * from
+( values (3), (7), (1) union all values (3), (4), (2) ) as dt;
+
+select * from
+( values (3), (7), (1) union values (3), (4), (2) ) as dt;
+
+select * from
+( values (3), (7), (1) except values (3), (4), (2) ) as dt;
+
+select * from
+( (values (3), (7), (1)) union (values (3), (4), (2)) ) as dt;
+
+select * from
+( (values (3), (7), (1))
+  union
+  (values (3), (4), (2))
+  union values (5), (7) ) dt;
+
+select * from
+( (values (3), (7), (1))
+  union
+  (values (3), (4), (2))
+  union
+  (values (5), (7)) ) as dt;
+
+select * from
+( (values (3), (7), (1)
+   union
+   values (3), (4), (2))
+   union
+   values (5), (7) ) as dt;
+
+select * from
+( values (3), (7), (1)
+  union (values (3), (4), (2)
+         union
+         values (5), (7)) ) as dt;
+
+select * from
+( (values (3), (7), (1)
+  union
+  ((values (3), (4), (2)
+    union values (5), (7)))) ) dt;
+
+select * from
+( values (3), (7), (1)
+  union
+  values (3), (4), (2)
+  order by 1 ) as dt;
+
+select * from
+( (values (3), (7), (1) union values (3), (4), (2)) order by 1 ) as dt;
+
+select * from
+( (values (3), (7), (1) union values (3), (4), (2)) order by 1 ) as dt;
+
+select * from
+( values (3), (7), (1) union (values (3), (4), (2)) order by 1 ) as dt;
+
+select * from
+( (values (3), (7), (1) union values (3), (4), (2)) order by 1 ) as dt;
+
+select * from
+( ((values (3), (7), (1)) union values (3), (4), (2)) order by 1 ) as dt;
+
+select * from
+( (values (3), (7), (1) order by 1 limit 2)
+  union
+  (values (3), (4), (2) order by 1 desc limit 2) ) as dt;
+
+select * from
+( ((values (3), (7), (1) order by 1) limit 2)
+  union
+  ((values (3), (4), (2) order by 1 desc) limit 2) ) as dt;
+
+select * from
+( (((values (3), (7), (1)) order by 1) limit 2)
+  union
+  (((values (3), (4), (2)) order by 1 desc) limit 2) ) as dt;
+
+select * from
+( (values (3), (7), (1) order by 1 limit 2)
+  union
+  values (3), (4), (2)
+  order by 1 limit 3 ) as dt;
+
+select * from
+( ((values (3), (7), (1)) order by 1 limit 2)
+  union
+  ((values (3), (4), (2) order by 1 desc) limit 2)
+  order by 1 limit 3 ) as dt;
+
+select * from
+( (select a from t1 where a <=3 order by 1 limit 2)
+  union
+  (values (3), (4), (2) order by 1 desc limit 2) ) dt;
+
+select * from
+( ((select a from t1 where a <=3) order by 1 limit 2)
+  union
+  (values (3), (4), (2) order by 1 desc limit 2) ) as dt;
+
+select * from
+( (((select a from t1 where a <=3) order by a) limit 2)
+  union
+  (((values (3), (4), (2)) order by 1 desc) limit 2) ) as dt;
+
+select * from
+ ( ( (((select a from t1 where a <=3) order by a) limit 2)
+   union
+   (((values (3), (4), (2)) order by 1 desc) limit 2) ) ) dt;
+
+select * from
+( (select a from t1 where a <=3 order by 1 limit 2)
+  union
+  (values (3), (4), (2) order by 1 desc limit 2)
+  order by a ) as dt;
+
+select * from
+( ((select a from t1 where a <=3) order by 1 limit 2)
+  union
+  (values (3), (4), (2) order by 1 desc limit 2)
+  order by a ) as dt;
+
+select * from
+( (((select a from t1 where a <=3) order by a) limit 2)
+  union
+  (((values (3), (4), (2)) order by 1 desc) limit 2)
+  order by a ) as dt;
+
+select * from
+( (((values (3), (4), (2)) order by 1 desc) limit 2) ) as dt;
+
+select * from
+( ( (((select a from t1 where a <=3) order by a) limit 2)
+    union
+    (((values (3), (4), (2)) order by 1 desc) limit 2) )
+  order by a ) as dt;
+
+select * from
+( (values (3), (4), (2) order by 1 desc limit 2)
+  union
+  (select a from t1 where a <=3 order by 1 limit 2) ) as dt;
+
+select * from
+( (values (3), (4), (2) order by 1 desc limit 2)
+  union
+  ((select a from t1 where a <=3) order by 1 limit 2) ) as dt;
+
+select * from
+( (((values (3), (4), (2)) order by 1 desc) limit 2)
+  union
+  (((select a from t1 where a <=3) order by 1) limit 2) ) as dt;
+
+select * from
+( (((values (3), (4), (2)) order by 1 desc) limit 2)
+  union
+  (((select a from t1 where a <=3) order by a) limit 2)
+  order by 1 ) as dt;
+
+select * from
+( ( select a from t1 where a=1
+    union
+    values (3), (4), (2) order by 1 desc )
+  union
+  select a from t1 where a=2 order by a desc limit 3 ) as dt;
+
+
+--echo # 9.4. derived table as [tailed] simple select with CTE
+
+
+select * from
+( with t as (select * from t1 where a <=3)
+  select * from t ) as dt;
+
+select * from
+( with t as (select * from t1 where a <=3)
+  (select * from t) ) as dt;
+
+select * from
+( with t as (select * from t1 where a <=3)
+  ((select * from t)) ) as dt;
+
+select * from
+( with t as ((select * from t1 where a <=3))
+  select * from t ) as dt;
+
+select * from
+( with t as (((select * from t1 where a <=3)))
+  select * from t ) as dt;
+
+select * from
+( with t as (select * from t1 where a <=3)
+  select * from t order by a ) as dt;
+
+select * from
+( with t as (select * from t1 where a <=3)
+  (select * from t) order by a ) as dt;
+
+select * from
+( with t as (select * from t1 where a <=3)
+  (select * from t) order by a desc limit 2 ) as dt;
+
+select * from
+( with t as (select * from t1 where a >=2 order by a limit 2)
+  select * from t ) as dt;
+
+select * from
+( with t as (((select * from t1 where a >=2) order by a desc) limit 2)
+  select * from t ) as dt;
+
+select * from
+( with t as (select * from t1 where a >=2 order by a desc limit 2)
+  select * from t order by a ) as dt;
+
+--echo # 9.5. derived table as tailed union with CTE
+
+select * from
+( with t as (select * from t1 where a <=3)
+  select a from t1 where a=1 union select a from t where a=3 ) as dt;
+
+select * from
+( with t as (select * from t1 where a <=3)
+  (select a from t) union (select b from t2) ) as dt;
+
+select * from
+( with t as (select * from t1 where a <=3)
+  (select a from t) union (select b as a from t2) order by a desc ) as dt;
+
+select * from
+( with t as (select * from t1 where a < 3 union select * from t1 where a > 3)
+  select a from t1 where a=1 union select a from t where a=7 ) as dt;
+
+select * from
+( with t as
+  ( select * from t1 where a < 3
+    union
+    select * from t1 where a > 3
+    order by a desc limit 3 )
+  select a from t1 where a=4 union select a from t where a=7 ) as dt;
+
+select * from
+( with t as
+  ( select * from t1 where a < 3
+    union
+    select * from t1 where a > 3
+    order by a desc limit 3 )
+  select a from t1 where a=4
+  union
+  select a from t where a=7
+  order by a desc ) as dt;
+
+select * from
+( with t as
+  ( (select * from t1 where a < 3)
+    union
+    (select * from t1 where a > 3)
+    order by a desc limit 3 )
+  select a from t1 where a=4
+  union select a from t where a=7
+  order by a desc ) dt;
+
+select * from
+( with t as
+  ( (select * from t1 where a < 3)
+    union
+    (select * from t1 where a > 3)
+    order by a desc limit 3 )
+  (select a from t1 where a=4
+   union
+   select a from t where a=7
+   order by a desc) ) as dt;
+
+select * from
+(  with t as
+  ( (select * from t1 where a < 3)
+    union
+    (select * from t1 where a > 3)
+    order by a desc limit 3 )
+  ((select a from t1 where a=4
+    union
+    select a from t where a=7) order by a desc) ) as dt;
+
+select * from
+( with t as
+  ( select * from t1 where a < 3
+    union
+    values (4), (7)
+    order by a desc limit 3 )
+  select a from t1 where a=4
+  union
+  select a from t where a=7
+  order by a desc ) dt;
+
+select * from
+( with t(a) as
+  ( values (2), (1)
+    union
+    (values (4), (7))
+    order by 1 desc limit 3 )
+  select a from t1 where a=4
+  union select a from t where a=7
+  order by a desc ) as dt;
+
+select * from
+( with t(a) as
+  ( (values (2), (1))
+    union
+    (values (4), (7) order by 1 desc)
+    order by 1 desc limit 3 )
+  select a from t1 where a=1
+  union
+  select a from t where a=7 order by a desc ) as dt;
+
+select * from
+( with t(a) as
+  ( (values (2), (1))
+    union
+    (values (4), (7) order by 1 desc)
+    order by 1 limit 3 )
+  select a from t where a=1 union values (7) order by a desc ) as dt;
+
+select * from
+( with t(a) as
+  ( (values (2), (1))
+    union
+    (values (4), (7) order by 1 desc ) )
+  select a from t where a=1 union select 7 order by a desc ) as dt;
+
+select * from
+( with t as (select * from t1 where a < 3),
+       s as (select * from t1 where a > 3)
+  select a from t where a=1
+  union select a from s where a=7
+  order by a desc ) dt;
+
+select * from
+( with t as (select * from t1 where a < 3),
+       s as (select * from t1 where a > 3)
+  (select a from t where a=1
+   union
+   select a from s where a=7 order by a desc) ) dt;
+
+select * from
+( with t as (select * from t1 where a < 3),
+       s as (select * from t1 where a > 3)
+  (select a from t where a=1
+   union
+   select a from s where a=7)
+   order by a desc ) dt;
+
+
+--echo 10. view
+
+--echo 10.1. view as simple select
+
+create view v1 as
+select * from t1;
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+select 2*a as c from t1;
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1(c) as
+select 2*a from t1;
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+((select * from t1));
+show create view v1;
+select * from v1;
+drop view v1;
+
+--echo 10.2. view as tailed simple select
+
+create view v1 as
+select * from t1 order by a;
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+(select * from t2 order by a limit 2) order by b desc;
+show create view v1;
+select * from v1;
+drop view v1;
+
+--echo 10.3. view as union
+
+create view v1 as
+select a from t1 union select b from t2;
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+(select a from t1) union (select b from t2);
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+(select a from t1 where a=1) union select a from t1 where a=3;
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+((select a from t1 where a<=3) union (select a from t1 where a=3));
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+select a from t1 where a=1
+union
+select a from t1 where a=3
+union
+select a from t1 where a=7;
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+( ( select a from t1 where a!=3 order by a desc limit 3)
+  union
+  select a from t1 where a=3 );
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+( select a from t1 where a <=3 except select a from t1 where a >=3 )
+  union
+  select a from t1 where a=7;
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+(select a from t1 limit 2) union select a from t1 where a=3 order by a desc;
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+select a from t1 where a=1
+union
+( select a from t1 where a=3
+  union
+  ( select a from t1 where a=2
+    union
+    ( select a from t1 where a=4 ) ) );
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+( ( select a from t1 where a >= 2
+    union
+    select a from t1 where a=1 order by a desc limit 2 )
+  union
+  select a from t1 where a=3  order by a limit 2 )
+union
+select a from t1 where a=1;
+show create view v1;
+select * from v1;
+drop view v1;
+
+--echo 10.4. view as [tailed] TVC
+
+create view v1 as
+values (3), (7), (1);
+show create view v1;
+select * from v1;
+drop view v1;
+create view v1 as
+(((values (3), (7), (1))) order by 1);
+show create view v1;
+select * from v1;
+drop view v1;
+
+--echo 10.5. view as [tailed] union of TVCs
+
+create view v1 as
+values (3), (7), (1) union values (3), (4), (2);
+show create view v1;
+select * from v1;
+drop view v1;
+create view v1 as
+(values (3), (7), (1) union values (3), (4), (2)) order by 1;
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+(values (3), (7), (1) order by 1 limit 2)
+union
+(values (3), (4), (2) order by 1 desc limit 2);
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+(values (3), (7), (1) order by 1 limit 2)
+union
+values (3), (4), (2)
+order by 1;
+show create view v1;
+select * from v1;
+drop view v1;
+
+--echo 10.6. view as [tailed] union of [tailed] select and tailed TVC
+
+create view v1 as
+( (((select a from t1 where a <=3) order by a) limit 2)
+  union
+  (((values (3), (4), (2)) order by 1 desc) limit 2) )
+order by a;
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+( select a from t1 where a=1
+  union
+  values (3), (4), (2) order by 1 desc )
+union
+select a from t1 where a=2 order by a desc limit 3;
+show create view v1;
+select * from v1;
+drop view v1;
+
+--echo 10.7. view as select with CTE
+
+create view v1 as
+with t as (select * from t1 where a <=3)
+select * from t;
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+with t as
+( select * from t1 where a < 3
+  union
+  select * from t1 where a > 3
+  order by a desc limit 3 )
+select a from t1 where a=4 union select a from t where a=7;
+show create view v1;
+select * from v1;
+drop view v1;
+
+--echo 10.8. view as union with CTE
+
+create view v1 as
+with t as
+( (select * from t1 where a < 3)
+  union
+  (select * from t1 where a > 3)
+  order by a desc limit 3 )
+(select a from t1 where a=4 union select a from t where a=7 order by a desc);
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+with t as
+( (select * from t1 where a < 3)
+  union
+  (select * from t1 where a > 3)
+  order by a desc limit 3 )
+(select a from t where a=4 union select a from t where a=7 order by a desc);
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+with t(a) as (values (2), (1)) select a from t;
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+with t(a) as
+( values (2), (1)
+  union
+  (values (4), (7))
+  order by 1 desc limit 3 )
+select a from t1 where a=4 union select a from t where a=7 order by a desc;
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+with t(a) as
+( (values (2), (1))
+  union
+  (values (4), (7) order by 1 desc)
+  order by 1 desc limit 3 )
+select a from t1 where a=1 union select a from t where a=7 order by a desc;
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+with t as (select * from t1 where a < 3),
+     s as (select * from t1 where a > 3)
+select a from t where a=1 union select a from s where a=7 order by a desc;
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+with t as (select * from t1 where a < 3),
+     s as (select * from t where a > 3)
+select a from t where a=1 union select a from s where a=7 order by a desc;
+show create view v1;
+select * from v1;
+drop view v1;
+
+drop table t1,t2;
+
 --echo # End of 10.4 tests
 
diff --git a/mysql-test/main/except.result b/mysql-test/main/except.result
index bac3d3ddaa5..f48a25f93bd 100644
--- a/mysql-test/main/except.result
+++ b/mysql-test/main/except.result
@@ -25,7 +25,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	EXCEPT	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
 NULL	EXCEPT RESULT	<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
 {
@@ -230,7 +230,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	EXCEPT	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (flat, BNL join)
 NULL	EXCEPT RESULT	<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 6cf0f476970..5990fcce110 100644
--- a/mysql-test/main/intersect.result
+++ b/mysql-test/main/intersect.result
@@ -39,7 +39,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 4	INTERSECT	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	
 NULL	INTERSECT RESULT	<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
 {
@@ -280,7 +280,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	INTERSECT	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (flat, BNL join)
 NULL	INTERSECT RESULT	<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`
 set @@optimizer_switch='optimize_join_buffer_size=off';
 EXPLAIN format=json (select a,b from t1) intersect (select c,e from t2,t3);
 EXPLAIN
@@ -724,7 +724,7 @@ a	b
 drop procedure p1;
 show create view v1;
 View	Create View	character_set_client	collation_connection
-v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union select `__6`.`c` AS `c`,`__6`.`d` AS `d` from (select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` intersect (select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__6` union (select 4 AS `4`,4 AS `4`)	latin1	latin1_swedish_ci
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union select `__6`.`c` AS `c`,`__6`.`d` AS `d` from ((select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect (select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__6` union (select 4 AS `4`,4 AS `4`)	latin1	latin1_swedish_ci
 drop view v1;
 drop tables t1,t2,t3;
 #
diff --git a/mysql-test/main/parser.result b/mysql-test/main/parser.result
index 2b4a0bb7163..b39f496e3db 100644
--- a/mysql-test/main/parser.result
+++ b/mysql-test/main/parser.result
@@ -1776,7 +1776,7 @@ End of 10.3 tests
 #
 create table t1 (a int);
 (select * from t1) for update;
-ERROR HY000: Incorrect usage of lock options and SELECT in brackets
+a
 (select * from t1) union (select * from t1) for update;
 ERROR HY000: Incorrect usage of lock options and SELECT in brackets
 (select * from t1 for update);
diff --git a/mysql-test/main/parser.test b/mysql-test/main/parser.test
index b3cfcfb4fcc..c6e9f13cdaf 100644
--- a/mysql-test/main/parser.test
+++ b/mysql-test/main/parser.test
@@ -1544,7 +1544,6 @@ SELECT @@GLOBAL.role;
 --echo #
 
 create table t1 (a int);
---error ER_WRONG_USAGE
 (select * from t1) for update;
 --error ER_WRONG_USAGE
 (select * from t1) union (select * from t1) for update;
diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result
index f2836d36c80..349e7dca129 100644
--- a/mysql-test/main/subselect.result
+++ b/mysql-test/main/subselect.result
@@ -3736,8 +3736,11 @@ WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
 i
 explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
 from t1;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'union (select t12.i from t1 t12))
-from t1' at line 1
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	Const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+NULL	UNION RESULT	<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
@@ -5305,7 +5308,7 @@ SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
 ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) )
 1
 SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1' at line 1
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
 SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
 ( SELECT 1 UNION SELECT 1 UNION SELECT 1 )
 1
@@ -5335,7 +5338,8 @@ SELECT * FROM t1 WHERE a IN    ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
 a
 1
 SELECT * FROM t1 WHERE a =     ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
+a
+1
 SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
 a
 1
@@ -5343,7 +5347,8 @@ SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
 a
 1
 SELECT * FROM t1 WHERE a IN    ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
+a
+1
 SELECT * FROM t1 WHERE a =     ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
 a
 1
diff --git a/mysql-test/main/subselect.test b/mysql-test/main/subselect.test
index 53773eb5e1e..be17254202e 100644
--- a/mysql-test/main/subselect.test
+++ b/mysql-test/main/subselect.test
@@ -2611,8 +2611,6 @@ SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS
 SELECT * FROM t1
 WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
 
-#TODO:not supported
---error ER_PARSE_ERROR
 explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
   from t1;
 
@@ -4414,11 +4412,9 @@ SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
 SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
 SELECT * FROM t1 WHERE a IN    ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
 
---error ER_PARSE_ERROR
 SELECT * FROM t1 WHERE a =     ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
 SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
 SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
---error ER_PARSE_ERROR
 SELECT * FROM t1 WHERE a IN    ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
 
 SELECT * FROM t1 WHERE a =     ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result
index 1cf1a3373a3..84c415d1ce1 100644
--- a/mysql-test/main/subselect_no_exists_to_in.result
+++ b/mysql-test/main/subselect_no_exists_to_in.result
@@ -3739,8 +3739,11 @@ WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
 i
 explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
 from t1;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'union (select t12.i from t1 t12))
-from t1' at line 1
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	Const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+NULL	UNION RESULT	<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
@@ -5307,7 +5310,7 @@ SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
 ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) )
 1
 SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1' at line 1
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
 SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
 ( SELECT 1 UNION SELECT 1 UNION SELECT 1 )
 1
@@ -5337,7 +5340,8 @@ SELECT * FROM t1 WHERE a IN    ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
 a
 1
 SELECT * FROM t1 WHERE a =     ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
+a
+1
 SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
 a
 1
@@ -5345,7 +5349,8 @@ SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
 a
 1
 SELECT * FROM t1 WHERE a IN    ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
+a
+1
 SELECT * FROM t1 WHERE a =     ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
 a
 1
diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result
index 92ddbe34b6f..93035e235f7 100644
--- a/mysql-test/main/subselect_no_mat.result
+++ b/mysql-test/main/subselect_no_mat.result
@@ -3739,8 +3739,11 @@ WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
 i
 explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
 from t1;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'union (select t12.i from t1 t12))
-from t1' at line 1
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	Const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+NULL	UNION RESULT	<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
@@ -5305,7 +5308,7 @@ SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
 ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) )
 1
 SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1' at line 1
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
 SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
 ( SELECT 1 UNION SELECT 1 UNION SELECT 1 )
 1
@@ -5335,7 +5338,8 @@ SELECT * FROM t1 WHERE a IN    ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
 a
 1
 SELECT * FROM t1 WHERE a =     ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
+a
+1
 SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
 a
 1
@@ -5343,7 +5347,8 @@ SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
 a
 1
 SELECT * FROM t1 WHERE a IN    ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
+a
+1
 SELECT * FROM t1 WHERE a =     ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
 a
 1
diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result
index a2788715194..09f664d3c28 100644
--- a/mysql-test/main/subselect_no_opts.result
+++ b/mysql-test/main/subselect_no_opts.result
@@ -3735,8 +3735,11 @@ WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
 i
 explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
 from t1;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'union (select t12.i from t1 t12))
-from t1' at line 1
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	Const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+NULL	UNION RESULT	<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
@@ -5301,7 +5304,7 @@ SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
 ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) )
 1
 SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1' at line 1
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
 SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
 ( SELECT 1 UNION SELECT 1 UNION SELECT 1 )
 1
@@ -5331,7 +5334,8 @@ SELECT * FROM t1 WHERE a IN    ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
 a
 1
 SELECT * FROM t1 WHERE a =     ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
+a
+1
 SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
 a
 1
@@ -5339,7 +5343,8 @@ SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
 a
 1
 SELECT * FROM t1 WHERE a IN    ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
+a
+1
 SELECT * FROM t1 WHERE a =     ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
 a
 1
diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result
index f5c5a1dead6..765bb15a3df 100644
--- a/mysql-test/main/subselect_no_scache.result
+++ b/mysql-test/main/subselect_no_scache.result
@@ -3742,8 +3742,11 @@ WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
 i
 explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
 from t1;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'union (select t12.i from t1 t12))
-from t1' at line 1
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	Const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+NULL	UNION RESULT	<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
@@ -5311,7 +5314,7 @@ SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
 ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) )
 1
 SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1' at line 1
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
 SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
 ( SELECT 1 UNION SELECT 1 UNION SELECT 1 )
 1
@@ -5341,7 +5344,8 @@ SELECT * FROM t1 WHERE a IN    ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
 a
 1
 SELECT * FROM t1 WHERE a =     ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
+a
+1
 SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
 a
 1
@@ -5349,7 +5353,8 @@ SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
 a
 1
 SELECT * FROM t1 WHERE a IN    ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
+a
+1
 SELECT * FROM t1 WHERE a =     ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
 a
 1
diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result
index bef256f789f..97d2f3b058f 100644
--- a/mysql-test/main/subselect_no_semijoin.result
+++ b/mysql-test/main/subselect_no_semijoin.result
@@ -3735,8 +3735,11 @@ WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
 i
 explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
 from t1;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'union (select t12.i from t1 t12))
-from t1' at line 1
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	Const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+NULL	UNION RESULT	<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
@@ -5301,7 +5304,7 @@ SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
 ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) )
 1
 SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1' at line 1
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
 SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
 ( SELECT 1 UNION SELECT 1 UNION SELECT 1 )
 1
@@ -5331,7 +5334,8 @@ SELECT * FROM t1 WHERE a IN    ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
 a
 1
 SELECT * FROM t1 WHERE a =     ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
+a
+1
 SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
 a
 1
@@ -5339,7 +5343,8 @@ SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
 a
 1
 SELECT * FROM t1 WHERE a IN    ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
+a
+1
 SELECT * FROM t1 WHERE a =     ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
 a
 1