Merge remote-tracking branch 'origin/10.4' into 10.5

24 files changed, 7171 insertions, 550 deletions

diff --git a/client/mysql.cc b/client/mysql.cc
index b881773e1f8..6402d651038 100644
--- a/client/mysql.cc
+++ b/client/mysql.cc
@@ -2668,7 +2668,7 @@ static int fake_magic_space(const char *, int)
 static void initialize_readline ()
 {
   /* Allow conditional parsing of the ~/.inputrc file. */
-  rl_readline_name= "mysql";
+  rl_readline_name= (char *) "mysql";
   rl_terminal_name= getenv("TERM");
 
   /* Tell the completer that we want a crack first. */
diff --git a/mysql-test/main/brackets.result b/mysql-test/main/brackets.result
index 8fc54e683c9..548250db758 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 393c918ebdf..342340920cf 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/except_all.result b/mysql-test/main/except_all.result
index 19ff9f33675..ef65107d62c 100644
--- a/mysql-test/main/except_all.result
+++ b/mysql-test/main/except_all.result
@@ -103,7 +103,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	EXCEPT	t2	ALL	NULL	NULL	NULL	NULL	7	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 all (/* 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 all (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`)) `a`
 ANALYZE format=json select * from ((select a,b from t1) except all (select c,d from t2)) a;
 ANALYZE
 {
@@ -337,7 +337,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	EXCEPT	t4	ALL	NULL	NULL	NULL	NULL	3	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 `t`.`a` AS `a`,`t`.`b` AS `b`,`t`.`e` AS `e`,`t`.`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 all (/* 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`)) `t`
+Note	1003	/* select#1 */ select `t`.`a` AS `a`,`t`.`b` AS `b`,`t`.`e` AS `e`,`t`.`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 all (/* 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`)) `t`
 EXPLAIN format=json select * from ((select a,b,e,f from t1,t3) except all (select c,d,g,h from t2,t4)) t;
 EXPLAIN
 {
diff --git a/mysql-test/main/intersect.result b/mysql-test/main/intersect.result
index 7ad10265f0f..7b43e478e30 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
@@ -688,7 +688,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/intersect_all.result b/mysql-test/main/intersect_all.result
index 66ee060cee5..84a97982d13 100644
--- a/mysql-test/main/intersect_all.result
+++ b/mysql-test/main/intersect_all.result
@@ -51,7 +51,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 4	INTERSECT	t3	ALL	NULL	NULL	NULL	NULL	4	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 all (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect all (/* 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 all (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect all (/* 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 all (select c,d from t2) intersect all (select e,f from t3);
 EXPLAIN
 {
@@ -312,7 +312,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	INTERSECT	t2	ALL	NULL	NULL	NULL	NULL	7	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 all (/* 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 all (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t3`.`e` AS `e` from `test`.`t2` join `test`.`t3`)) `a`
 EXPLAIN format=json (select a,b from t1) intersect all (select c,e from t2,t3);
 EXPLAIN
 {
@@ -741,7 +741,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 all 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 all (select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__6` union all (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 all 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 all (select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__6` union all (select 4 AS `4`,4 AS `4`)	latin1	latin1_swedish_ci
 drop view v1;
 drop tables t1,t2,t3;
 CREATE TABLE t (i INT);
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
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index de49a54b54a..96344c0968b 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -124,7 +124,8 @@ void Item_subselect::init(st_select_lex *select_lex,
     parsing_place= (outer_select->in_sum_expr ?
                     NO_MATTER :
                     outer_select->parsing_place);
-    if (unit->is_unit_op() && unit->first_select()->next_select())
+    if (unit->is_unit_op() &&
+        (unit->first_select()->next_select() or unit->fake_select_lex))
       engine= new subselect_union_engine(unit, result, this);
     else
       engine= new subselect_single_select_engine(select_lex, result, this);
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index c5d7e5898b6..bc530f9be60 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1448,7 +1448,7 @@ int Lex_input_stream::lex_token(YYSTYPE *yylval, THD *thd)
       return LEFT_PAREN_LIKE;
     if (token == WITH)
       return LEFT_PAREN_WITH;
-    if (token != left_paren && token != SELECT_SYM)
+    if (token != left_paren && token != SELECT_SYM && token != VALUES)
       return LEFT_PAREN_ALT;
     else
       return left_paren;
@@ -5344,10 +5344,9 @@ LEX::create_unit(SELECT_LEX *first_sel)
   SELECT_LEX_UNIT *unit;
   DBUG_ENTER("LEX::create_unit");
 
-  if (first_sel->master_unit())
-    DBUG_RETURN(first_sel->master_unit());
+  unit = first_sel->master_unit();
 
-  if (!(unit= alloc_unit()))
+  if (!unit && !(unit= alloc_unit()))
     DBUG_RETURN(NULL);
 
   unit->register_select_chain(first_sel);
@@ -9019,7 +9018,8 @@ bool LEX::insert_select_hack(SELECT_LEX *sel)
     builtin_select.link_prev= NULL; // indicator of removal
   }
 
-  set_main_unit(sel->master_unit());
+  if (set_main_unit(sel->master_unit()))
+    return true;
 
   DBUG_ASSERT(builtin_select.table_list.elements == 1);
   TABLE_LIST *insert_table= builtin_select.table_list.first;
@@ -9063,9 +9063,10 @@ bool LEX::insert_select_hack(SELECT_LEX *sel)
 }
 
 
-/*
+/**
   Create an Item_singlerow_subselect for a query expression.
 */
+
 Item *LEX::create_item_query_expression(THD *thd,
                                         st_select_lex_unit *unit)
 {
@@ -9080,118 +9081,17 @@ Item *LEX::create_item_query_expression(THD *thd,
   SELECT_LEX *curr_sel= select_stack_head();
   DBUG_ASSERT(current_select == curr_sel);
   if (!curr_sel)
+  {
     curr_sel= &builtin_select;
-  curr_sel->register_unit(unit, &curr_sel->context);
-  curr_sel->add_statistics(unit);
+    curr_sel->register_unit(unit, &curr_sel->context);
+    curr_sel->add_statistics(unit);
+  }
 
   return new (thd->mem_root)
     Item_singlerow_subselect(thd, unit->first_select());
 }
 
 
-/**
-  Process unit parsed in brackets
-*/
-
-bool LEX::parsed_unit_in_brackets(SELECT_LEX_UNIT *unit)
-{
-  SELECT_LEX *first_in_nest= unit->pre_last_parse->next_select()->first_nested;
-  if (first_in_nest->first_nested != first_in_nest)
-  {
-    /* There is a priority jump starting from first_in_nest */
-    if (create_priority_nest(first_in_nest) == NULL)
-      return true;
-    unit->fix_distinct();
-  }
-  push_select(unit->fake_select_lex);
-  return false;
-}
-
-
-
-/**
-  Process tail of unit parsed in brackets
-*/
-SELECT_LEX *LEX::parsed_unit_in_brackets_tail(SELECT_LEX_UNIT *unit,
-                                              Lex_order_limit_lock * l)
-{
-  pop_select();
-  if (l)
-  {
-    (l)->set_to(unit->fake_select_lex);
-  }
-  return unit->first_select();
-}
-
-
-/**
-  Process select parsed in brackets
-*/
-
-SELECT_LEX *LEX::parsed_select(SELECT_LEX *sel, Lex_order_limit_lock * l)
-{
-  pop_select();
-  if (l)
-  {
-    if (sel->next_select())
-    {
-      SELECT_LEX_UNIT *unit= sel->master_unit();
-      if (!unit)
-        unit= create_unit(sel);
-      if (!unit)
-        return NULL;
-      if (!unit->fake_select_lex->is_set_query_expr_tail)
-        l->set_to(unit->fake_select_lex);
-      else
-      {
-        if (!l->order_list && !unit->fake_select_lex->explicit_limit)
-        {
-          sel= unit->fake_select_lex;
-          l->order_list= &sel->order_list;
-        }
-        else
-          sel= wrap_unit_into_derived(unit);
-        if (!sel)
-          return NULL;
-        l->set_to(sel);
-      }
-    }
-    else if (!sel->is_set_query_expr_tail)
-    {
-      l->set_to(sel);
-    }
-    else
-    {
-      if (!l->order_list && !sel->explicit_limit)
-        l->order_list= &sel->order_list;
-      else
-      {
-        SELECT_LEX_UNIT *unit= create_unit(sel);
-        if (!unit)
-          return NULL;
-        sel= wrap_unit_into_derived(unit);
-      }
-      if (!sel)
-        return NULL;
-      l->set_to(sel);
-    }
-  }
-  return sel;
-}
-
-
-/**
-  Process select parsed in brackets
-*/
-
-SELECT_LEX *LEX::parsed_select_in_brackets(SELECT_LEX *sel,
-                                           Lex_order_limit_lock * l)
-{
-  sel->braces= TRUE;
-  return parsed_select(sel, l);
-}
-
-
 SELECT_LEX_UNIT *LEX::parsed_select_expr_start(SELECT_LEX *s1, SELECT_LEX *s2,
                                                enum sub_select_type unit_type,
                                                bool distinct)
@@ -9222,6 +9122,7 @@ SELECT_LEX_UNIT *LEX::parsed_select_expr_start(SELECT_LEX *s1, SELECT_LEX *s2,
   if (res == NULL)
     return NULL;
   res->pre_last_parse= sel1;
+  push_select(res->fake_select_lex);
   return res;
 }
 
@@ -9234,12 +9135,6 @@ SELECT_LEX_UNIT *LEX::parsed_select_expr_cont(SELECT_LEX_UNIT *unit,
   SELECT_LEX *sel1;
   if (!s2->next_select())
     sel1= s2;
-  else
-  {
-    sel1= wrap_unit_into_derived(s2->master_unit());
-    if (!sel1)
-      return NULL;
-  }
   SELECT_LEX *last= unit->pre_last_parse->next_select();
 
   int cmp= oracle? 0 : cmp_unit_op(unit_type, last->get_linkage());
@@ -9271,41 +9166,73 @@ SELECT_LEX_UNIT *LEX::parsed_select_expr_cont(SELECT_LEX_UNIT *unit,
   return unit;
 }
 
+
 /**
-  Process parsed select in body
+  Add primary expression as the next term in a given query expression body
+  pruducing a new query expression body
 */
 
-SELECT_LEX_UNIT *LEX::parsed_body_select(SELECT_LEX *sel,
-                                         Lex_order_limit_lock * l)
+SELECT_LEX_UNIT *
+LEX::add_primary_to_query_expression_body(SELECT_LEX_UNIT *unit,
+                                          SELECT_LEX *sel,
+                                          enum sub_select_type unit_type,
+                                          bool distinct,
+                                          bool oracle)
 {
-  if (sel->braces && l && l->lock.defined_lock)
+  SELECT_LEX *sel2= sel;
+  if (sel->master_unit() && sel->master_unit()->first_select()->next_select())
   {
-    my_error(ER_WRONG_USAGE, MYF(0), "lock options",
-        "SELECT in brackets");
-    return NULL;
+    sel2= wrap_unit_into_derived(sel->master_unit());
+    if (!sel2)
+      return NULL;
   }
-  if (!(sel= parsed_select(sel, l)))
-    return NULL;
+  SELECT_LEX *sel1= unit->first_select();
+  if (!sel1->next_select())
+    unit= parsed_select_expr_start(sel1, sel2, unit_type, distinct);
+  else
+    unit= parsed_select_expr_cont(unit, sel2, unit_type, distinct, oracle);
+  return unit;
+}
 
-  SELECT_LEX_UNIT *res= create_unit(sel);
-  if (res && sel->tvc && sel->order_list.elements)
+
+/**
+  Add query primary to a parenthesized query primary
+  pruducing a new query expression body
+*/
+
+SELECT_LEX_UNIT *
+LEX::add_primary_to_query_expression_body_ext_parens(
+                                                 SELECT_LEX_UNIT *unit,
+                                                 SELECT_LEX *sel,
+                                                 enum sub_select_type unit_type,
+                                                 bool distinct)
+{
+  SELECT_LEX *sel1= unit->first_select();
+  if (unit->first_select()->next_select())
   {
-    if (res->add_fake_select_lex(thd))
+    sel1= wrap_unit_into_derived(unit);
+    if (!sel1)
+      return NULL;
+    if (!create_unit(sel1))
       return NULL;
-    SELECT_LEX *fake= res->fake_select_lex;
-    fake->order_list= sel->order_list;
-    fake->explicit_limit= sel->explicit_limit;
-    fake->select_limit= sel->select_limit;
-    fake->offset_limit= sel->offset_limit;
   }
-  return res;
+  SELECT_LEX *sel2= sel;
+  if (sel->master_unit() && sel->master_unit()->first_select()->next_select())
+  {
+    sel2= wrap_unit_into_derived(sel->master_unit());
+    if (!sel2)
+      return NULL;
+  }
+  unit= parsed_select_expr_start(sel1, sel2, unit_type, distinct);
+  return unit;
 }
 
+
 /**
-  Process parsed unit in body
+  Process multi-operand query expression body
 */
 
-bool LEX::parsed_body_unit(SELECT_LEX_UNIT *unit)
+bool LEX::parsed_multi_operand_query_expression_body(SELECT_LEX_UNIT *unit)
 {
   SELECT_LEX *first_in_nest=
     unit->pre_last_parse->next_select()->first_nested;
@@ -9316,27 +9243,60 @@ bool LEX::parsed_body_unit(SELECT_LEX_UNIT *unit)
       return true;
     unit->fix_distinct();
   }
-  push_select(unit->fake_select_lex);
   return false;
 }
 
+
 /**
-  Process parsed tail of unit in body
+  Add non-empty tail to a query expression body
+*/
 
-  TODO: make processing for double tail case
+SELECT_LEX_UNIT *LEX::add_tail_to_query_expression_body(SELECT_LEX_UNIT *unit,
+                                                        Lex_order_limit_lock *l)
+{
+  DBUG_ASSERT(l != NULL);
+  pop_select();
+  SELECT_LEX *sel= unit->first_select()->next_select() ? unit->fake_select_lex :
+                                                         unit->first_select();
+  l->set_to(sel);
+  return unit;
+}
+
+
+/**
+  Add non-empty tail to a parenthesized query primary
 */
 
-SELECT_LEX_UNIT *LEX::parsed_body_unit_tail(SELECT_LEX_UNIT *unit,
-                                            Lex_order_limit_lock * l)
+SELECT_LEX_UNIT *
+LEX::add_tail_to_query_expression_body_ext_parens(SELECT_LEX_UNIT *unit,
+                                                  Lex_order_limit_lock *l)
 {
+  SELECT_LEX *sel= unit->first_select()->next_select() ? unit->fake_select_lex :
+                                                         unit->first_select();
+
+  DBUG_ASSERT(l != NULL);
+
   pop_select();
-  if (l)
+  if (sel->is_set_query_expr_tail)
   {
-    (l)->set_to(unit->fake_select_lex);
+    if (!l->order_list && !sel->explicit_limit)
+      l->order_list= &sel->order_list;
+    else
+    {
+      if (!unit)
+        return NULL;
+      sel= wrap_unit_into_derived(unit);
+      if (!sel)
+        return NULL;
+     if (!create_unit(sel))
+      return NULL;
+   }
   }
-  return unit;
+  l->set_to(sel);
+  return sel->master_unit();
 }
 
+
 /**
   Process subselect parsing
 */
@@ -9363,7 +9323,6 @@ SELECT_LEX *LEX::parsed_subselect(SELECT_LEX_UNIT *unit)
 }
 
 
-
 /**
   Process INSERT-like select
 */
@@ -9418,40 +9377,8 @@ SELECT_LEX *LEX::parsed_TVC_end()
 }
 
 
-TABLE_LIST *LEX::parsed_derived_select(SELECT_LEX *sel, int for_system_time,
-                                       LEX_CSTRING *alias)
-{
-  TABLE_LIST *res;
-  derived_tables|= DERIVED_SUBQUERY;
-  sel->set_linkage(DERIVED_TABLE_TYPE);
-  sel->braces= FALSE;
-  // Add the subtree of subquery to the current SELECT_LEX
-  SELECT_LEX *curr_sel= select_stack_head();
-  DBUG_ASSERT(current_select == curr_sel);
-  SELECT_LEX_UNIT *unit= sel->master_unit();
-  if (!unit)
-  {
-    unit= create_unit(sel);
-    if (!unit)
-      return NULL;
-  }
-  curr_sel->register_unit(unit, &curr_sel->context);
-  curr_sel->add_statistics(unit);
-
-  Table_ident *ti= new (thd->mem_root) Table_ident(unit);
-  if (ti == NULL)
-    return NULL;
-  if (!(res= curr_sel->add_table_to_list(thd, ti, alias, 0,
-                                         TL_READ, MDL_SHARED_READ)))
-    return NULL;
-  if (for_system_time)
-  {
-    res->vers_conditions= vers_conditions;
-  }
-  return res;
-}
 
-TABLE_LIST *LEX::parsed_derived_unit(SELECT_LEX_UNIT *unit,
+TABLE_LIST *LEX::parsed_derived_table(SELECT_LEX_UNIT *unit,
                                      int for_system_time,
                                      LEX_CSTRING *alias)
 {
@@ -9462,8 +9389,6 @@ TABLE_LIST *LEX::parsed_derived_unit(SELECT_LEX_UNIT *unit,
   // Add the subtree of subquery to the current SELECT_LEX
   SELECT_LEX *curr_sel= select_stack_head();
   DBUG_ASSERT(current_select == curr_sel);
-  curr_sel->register_unit(unit, &curr_sel->context);
-  curr_sel->add_statistics(unit);
 
   Table_ident *ti= new (thd->mem_root) Table_ident(unit);
   if (ti == NULL)
@@ -9481,7 +9406,8 @@ TABLE_LIST *LEX::parsed_derived_unit(SELECT_LEX_UNIT *unit,
 bool LEX::parsed_create_view(SELECT_LEX_UNIT *unit, int check)
 {
   SQL_I_List<TABLE_LIST> *save= &first_select_lex()->table_list;
-  set_main_unit(unit);
+  if (set_main_unit(unit))
+    return true;
   if (check_main_unit_semantics())
     return true;
   first_select_lex()->table_list.push_front(save);
@@ -9504,7 +9430,8 @@ bool LEX::select_finalize(st_select_lex_unit *expr)
   sql_command= SQLCOM_SELECT;
   selects_allow_into= TRUE;
   selects_allow_procedure= TRUE;
-  set_main_unit(expr);
+  if (set_main_unit(expr))
+    return true;
   return check_main_unit_semantics();
 }
 
@@ -9515,6 +9442,7 @@ bool LEX::select_finalize(st_select_lex_unit *expr, Lex_select_lock l)
          select_finalize(expr);
 }
 
+
 /*
   "IN" and "EXISTS" subselect can appear in two statement types:
 
@@ -9547,7 +9475,6 @@ void LEX::relink_hack(st_select_lex *select_lex)
 }
 
 
-
 bool SELECT_LEX_UNIT::set_lock_to_the_last_select(Lex_select_lock l)
 {
   if (l.defined_lock)
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index eebbbccc7c0..730d775b98c 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -4435,9 +4435,6 @@ public:
     insert_list= 0;
   }
 
-  bool make_select_in_brackets(SELECT_LEX* dummy_select,
-                               SELECT_LEX *nselect, bool automatic);
-
   SELECT_LEX_UNIT *alloc_unit();
   SELECT_LEX *alloc_select(bool is_select);
   SELECT_LEX_UNIT *create_unit(SELECT_LEX*);
@@ -4453,7 +4450,7 @@ public:
   bool insert_select_hack(SELECT_LEX *sel);
   SELECT_LEX *create_priority_nest(SELECT_LEX *first_in_nest);
 
-  void set_main_unit(st_select_lex_unit *u)
+  bool set_main_unit(st_select_lex_unit *u)
   {
     unit.options= u->options;
     unit.uncacheable= u->uncacheable;
@@ -4463,16 +4460,10 @@ public:
     unit.union_distinct= u->union_distinct;
     unit.set_with_clause(u->with_clause);
     builtin_select.exclude_from_global();
+    return false;
   }
   bool check_main_unit_semantics();
 
-  // reaction on different parsed parts (bodies are in sql_yacc.yy)
-  bool parsed_unit_in_brackets(SELECT_LEX_UNIT *unit);
-  SELECT_LEX *parsed_select(SELECT_LEX *sel, Lex_order_limit_lock * l);
-  SELECT_LEX *parsed_unit_in_brackets_tail(SELECT_LEX_UNIT *unit,
-                                           Lex_order_limit_lock * l);
-  SELECT_LEX *parsed_select_in_brackets(SELECT_LEX *sel,
-                                             Lex_order_limit_lock * l);
   SELECT_LEX_UNIT *parsed_select_expr_start(SELECT_LEX *s1, SELECT_LEX *s2,
                                             enum sub_select_type unit_type,
                                             bool distinct);
@@ -4480,20 +4471,35 @@ public:
                                            SELECT_LEX *s2,
                                            enum sub_select_type unit_type,
                                            bool distinct, bool oracle);
-  SELECT_LEX_UNIT *parsed_body_select(SELECT_LEX *sel,
-                                      Lex_order_limit_lock * l);
-  bool parsed_body_unit(SELECT_LEX_UNIT *unit);
-  SELECT_LEX_UNIT *parsed_body_unit_tail(SELECT_LEX_UNIT *unit,
-                                         Lex_order_limit_lock * l);
+  bool parsed_multi_operand_query_expression_body(SELECT_LEX_UNIT *unit);
+  SELECT_LEX_UNIT *add_tail_to_query_expression_body(SELECT_LEX_UNIT *unit,
+						     Lex_order_limit_lock *l);
+  SELECT_LEX_UNIT *
+  add_tail_to_query_expression_body_ext_parens(SELECT_LEX_UNIT *unit,
+					       Lex_order_limit_lock *l);
+  SELECT_LEX_UNIT *parsed_body_ext_parens_primary(SELECT_LEX_UNIT *unit,
+                                                  SELECT_LEX *primary,
+                                              enum sub_select_type unit_type,
+                                              bool distinct);
+  SELECT_LEX_UNIT *
+  add_primary_to_query_expression_body(SELECT_LEX_UNIT *unit,
+                                       SELECT_LEX *sel,
+                                       enum sub_select_type unit_type,
+                                       bool distinct,
+                                       bool oracle);
+  SELECT_LEX_UNIT *
+  add_primary_to_query_expression_body_ext_parens(
+                                       SELECT_LEX_UNIT *unit,
+                                       SELECT_LEX *sel,
+                                       enum sub_select_type unit_type,
+                                       bool distinct);
   SELECT_LEX *parsed_subselect(SELECT_LEX_UNIT *unit);
   bool parsed_insert_select(SELECT_LEX *firs_select);
   bool parsed_TVC_start();
   SELECT_LEX *parsed_TVC_end();
-  TABLE_LIST *parsed_derived_select(SELECT_LEX *sel, int for_system_time,
-                                    LEX_CSTRING *alias);
-  TABLE_LIST *parsed_derived_unit(SELECT_LEX_UNIT *unit,
-                                  int for_system_time,
-                                  LEX_CSTRING *alias);
+  TABLE_LIST *parsed_derived_table(SELECT_LEX_UNIT *unit,
+                                   int for_system_time,
+                                   LEX_CSTRING *alias);
   bool parsed_create_view(SELECT_LEX_UNIT *unit, int check);
   bool select_finalize(st_select_lex_unit *expr);
   bool select_finalize(st_select_lex_unit *expr, Lex_select_lock l);
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 13a89e93378..d715e5523cf 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -11310,7 +11310,7 @@ bool Sql_cmd_create_table_like::execute(THD *thd)
   }
 #endif
 
-  if (select_lex->item_list.elements)		// With select
+  if (select_lex->item_list.elements || select_lex->tvc) // With select or TVC
   {
     select_result *result;
 
diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc
index 24d414e3347..eea14d7dfc2 100644
--- a/sql/sql_tvc.cc
+++ b/sql/sql_tvc.cc
@@ -599,8 +599,8 @@ static bool create_tvc_name(THD *thd, st_select_lex *parent_select,
 
 bool table_value_constr::to_be_wrapped_as_with_tail()
 {
-  return select_lex->master_unit()->first_select()->next_select() &&
-         select_lex->order_list.elements && select_lex->explicit_limit;
+  return  select_lex->master_unit()->first_select()->next_select() &&
+          select_lex->order_list.elements && select_lex->explicit_limit;
 }
 
 
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index f3fde2e59a0..e3c5508e947 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -1304,8 +1304,8 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg,
   bool have_except= false, have_intersect= false,
     have_except_all_or_intersect_all= false;
   bool instantiate_tmp_table= false;
-  bool single_tvc= !first_sl->next_select() && first_sl->tvc &&
-                   !fake_select_lex;
+  bool single_tvc= !first_sl->next_select() && first_sl->tvc;
+  bool single_tvc_wo_order= single_tvc && !first_sl->order_list.elements;
   DBUG_ENTER("st_select_lex_unit::prepare");
   DBUG_ASSERT(thd == current_thd);
 
@@ -1409,8 +1409,9 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg,
 
   if (is_union_select || is_recursive)
   {
-    if ((is_unit_op() && !union_needs_tmp_table() &&
-        !have_except && !have_intersect) || single_tvc)
+    if ((single_tvc_wo_order && !fake_select_lex) ||
+        (is_unit_op() && !union_needs_tmp_table() &&
+	 !have_except && !have_intersect && !single_tvc))
     {
       SELECT_LEX *last= first_select();
       while (last->next_select())
@@ -1425,6 +1426,7 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg,
     else
     {
       if (!is_recursive)
+      {
         /*
           class "select_unit_ext" handles query contains EXCEPT ALL and / or
           INTERSECT ALL. Others are handled by class "select_unit"
@@ -1437,7 +1439,8 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg,
           first_sl->distinct= false;
         }
         else
-	        union_result= new (thd->mem_root) select_unit(thd);
+	  union_result= new (thd->mem_root) select_unit(thd);
+      }
       else
       {
         with_element->rec_result=
@@ -1483,17 +1486,40 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg,
   if (sl->tvc && sl->order_list.elements &&
       !sl->tvc->to_be_wrapped_as_with_tail())
   {
+    SELECT_LEX_UNIT *unit= sl->master_unit();
     if (thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_VIEW)
     {
-      sl->master_unit()->fake_select_lex= 0;
-      sl->master_unit()->saved_fake_select_lex= 0;
+      unit->fake_select_lex= 0;
+      unit->saved_fake_select_lex= 0;
     }
     else
     {
-      sl->order_list.empty();
-      sl->explicit_limit= 0;
-      sl->select_limit= 0;
-      sl->offset_limit= 0;
+      if (!unit->first_select()->next_select())
+      {
+        if (!unit->fake_select_lex)
+	{
+          Query_arena *arena, backup_arena;
+          arena= thd->activate_stmt_arena_if_needed(&backup_arena);
+          bool rc= unit->add_fake_select_lex(thd);
+          if (arena)
+            thd->restore_active_arena(arena, &backup_arena);
+          if (rc)
+            goto err;
+        }
+        SELECT_LEX *fake= unit->fake_select_lex;
+        fake->order_list= sl->order_list;
+        fake->explicit_limit= sl->explicit_limit;
+        fake->select_limit= sl->select_limit;
+        fake->offset_limit= sl->offset_limit;
+        sl->order_list.empty();
+        sl->explicit_limit= 0;
+        sl->select_limit= 0;
+        sl->offset_limit= 0;
+        if (describe)
+          fake->options|= SELECT_DESCRIBE;
+      }
+      else if (!sl->explicit_limit)
+        sl->order_list.empty();
     }
   }
 
@@ -1518,7 +1544,7 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg,
 	  goto err;
       }
       else if (sl->tvc->prepare(thd, sl, tmp_result, this))
-	goto err;
+	  goto err;
     }
     else if (prepare_join(thd, sl, tmp_result, additional_options,
                           is_union_select))
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 164dbe8bb88..5ba8e070246 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -829,10 +829,10 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
 %parse-param { THD *thd }
 %lex-param { THD *thd }
 /*
-  Currently there are 46 shift/reduce conflicts.
+  Currently there are 37 shift/reduce conflicts.
   We should not introduce new conflicts any more.
 */
-%expect 46
+%expect 37
 
 /*
    Comments for TOKENS.
@@ -1644,6 +1644,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
 %left   MYSQL_CONCAT_SYM
 %left   NEG '~' NOT2_SYM BINARY
 %left   COLLATE_SYM
+%left SUBQUERY_AS_EXPR
 
 /*
   Tokens that can change their meaning from identifier to something else
@@ -1734,7 +1735,9 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
       ALTER TABLE t1 ADD SYSTEM VERSIONING;
 */
 %left   PREC_BELOW_CONTRACTION_TOKEN2
-%left   TEXT_STRING '(' VALUE_SYM VERSIONING_SYM
+%left   TEXT_STRING '(' ')' VALUE_SYM VERSIONING_SYM
+%left EMPTY_FROM_CLAUSE
+%right INTO
 
 %type <lex_str>
         DECIMAL_NUM FLOAT_NUM NUM LONG_NUM
@@ -1997,16 +2000,18 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
         query_specification
         table_value_constructor
         simple_table
+        query_simple
         query_primary
-        query_primary_parens
+        subquery
         select_into_query_specification
 
-
 %type <select_lex_unit>
-        query_specification_start
-        query_expression_body
         query_expression
-        query_expression_unit
+        query_expression_no_with_clause
+        query_expression_body_ext
+        query_expression_body_ext_parens
+        query_expression_body
+        query_specification_start
 
 %type <boolfunc2creator> comp_op
 
@@ -2031,7 +2036,9 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
 
 %type <order_limit_lock>
         query_expression_tail
+        opt_query_expression_tail
         order_or_limit
+        order_limit_lock
         opt_order_limit_lock
 
 %type <select_order> opt_order_clause order_clause order_list
@@ -2184,7 +2191,7 @@ END_OF_INPUT
         THEN_SYM WHEN_SYM DIV_SYM MOD_SYM OR2_SYM AND_AND_SYM DELETE_SYM
         MYSQL_CONCAT_SYM ORACLE_CONCAT_SYM
 
-%type <with_clause> opt_with_clause with_clause
+%type <with_clause> with_clause
 
 %type <lex_str_ptr> query_name
 
@@ -5272,7 +5279,7 @@ create_select_query_expression:
             if (Lex->parsed_insert_select($1->first_select()))
               MYSQL_YYABORT;
           }
-        | LEFT_PAREN_WITH with_clause query_expression_body ')'
+        | LEFT_PAREN_WITH with_clause query_expression_no_with_clause ')'
           {
             SELECT_LEX *first_select= $3->first_select();
             $3->set_with_clause($2);
@@ -6785,12 +6792,7 @@ parse_vcol_expr:
         ;
 
 parenthesized_expr:
-          query_expression
-          {
-            if (!($$= Lex->create_item_query_expression(thd, $1)))
-              MYSQL_YYABORT;
-          }
-        | expr
+          expr
         | expr ',' expr_list
           {
             $3->push_front($1, thd->mem_root);
@@ -6809,6 +6811,16 @@ virtual_column_func:
               MYSQL_YYABORT;
             $$= v;
           }
+        | subquery
+          {
+            Item *item;
+            if (!(item= new (thd->mem_root) Item_singlerow_subselect(thd, $1)))
+              MYSQL_YYABORT;
+            Virtual_column_info *v= add_virtual_expression(thd, item);
+            if (unlikely(!v))
+              MYSQL_YYABORT;
+            $$= v;
+          }
         ;
 
 expr_or_literal: column_default_non_parenthesized_expr | signed_literal ;
@@ -9175,8 +9187,9 @@ opt_ignore_leaves:
   Select : retrieve data from table
 */
 
+
 select:
-          query_expression_body
+          query_expression_no_with_clause
           {
             if (Lex->push_select($1->fake_select_lex ?
                                  $1->fake_select_lex :
@@ -9186,10 +9199,11 @@ select:
           opt_procedure_or_into
           {
             Lex->pop_select();
+            $1->set_with_clause(NULL);
             if (Lex->select_finalize($1, $3))
               MYSQL_YYABORT;
           }
-        | with_clause query_expression_body
+        | with_clause query_expression_no_with_clause
           {
             if (Lex->push_select($2->fake_select_lex ?
                                  $2->fake_select_lex :
@@ -9206,7 +9220,6 @@ select:
           }
         ;
 
-
 select_into:
           select_into_query_specification
           {
@@ -9215,14 +9228,15 @@ select_into:
           }
           opt_order_limit_lock
           {
-            st_select_lex_unit *unit;
-            if (!(unit= Lex->parsed_body_select($1, $3)))
+            SELECT_LEX_UNIT *unit;
+            if (!(unit  = Lex->create_unit($1)))
               MYSQL_YYABORT;
+            if ($3)
+              unit= Lex->add_tail_to_query_expression_body(unit, $3);
             if (Lex->select_finalize(unit))
               MYSQL_YYABORT;
-           }
-         ;
-
+          }
+        ;
 
 simple_table:
           query_specification      { $$= $1; }
@@ -9288,92 +9302,191 @@ select_into_query_specification:
           }
         ;
 
-opt_from_clause:
-        /* Empty */
-        | from_clause
+/**
+
+  The following grammar for query expressions conformant to
+  the latest SQL Standard is supported:
+
+    <query expression> ::=
+     [ <with clause> ] <query expression body>
+       [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]
+
+   <with clause> ::=
+     WITH [ RECURSIVE ] <with_list
+
+   <with list> ::=
+     <with list element> [ { <comma> <with list element> }... ]
+
+   <with list element> ::=
+     <query name> [ '(' <with column list> ')' ]
+         AS <table subquery>
+
+   <with column list> ::=
+     <column name list>
+
+   <query expression body> ::
+       <query term>
+     | <query expression body> UNION [ ALL | DISTINCT ] <query term>
+     | <query expression body> EXCEPT [ DISTINCT ] <query term>
+
+   <query term> ::=
+       <query primary>
+     | <query term> INTERSECT [ DISTINCT ] <query primary>
+
+   <query primary> ::=
+       <simple table>
+     | '(' <query expression body>
+       [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]
+       ')'
+
+   <simple table>
+       <query specification>
+     | <table value constructor>
+
+  <subquery>
+       '(' <query_expression> ')'
+
+*/
+
+/*
+  query_expression produces the same expressions as
+      <query expression>
+*/
+
+query_expression:
+          query_expression_no_with_clause
+          {
+            $1->set_with_clause(NULL);
+            $$= $1;
+          }
+        | with_clause
+          query_expression_no_with_clause
+          {
+            $2->set_with_clause($1);
+            $1->attach_to($2->first_select());
+            $$= $2;
+          }
         ;
 
+/*
+   query_expression_no_with_clause produces the same expressions as
+       <query expression> without [ <with clause> ]
+*/
 
-query_primary:
-          simple_table
-          { $$= $1; }
-        | query_primary_parens
-          { $$= $1; }
+query_expression_no_with_clause:
+          query_expression_body_ext { $$= $1; }
+        | query_expression_body_ext_parens { $$= $1; }
         ;
 
-query_primary_parens:
-          '(' query_expression_unit
+/*
+  query_expression_body_ext produces the same expressions as
+      <query expression body>
+       [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]
+    | '('... <query expression body>
+       [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]
+      ')'...
+  Note: number of ')' must be equal to the number of '(' in the rule above
+*/
+
+query_expression_body_ext:
+          query_expression_body
           {
-            if (Lex->parsed_unit_in_brackets($2))
-              MYSQL_YYABORT;
+            if ($1->first_select()->next_select())
+            {
+              if (Lex->parsed_multi_operand_query_expression_body($1))
+                MYSQL_YYABORT;
+            }
           }
-          query_expression_tail ')'
+          opt_query_expression_tail
           {
-            $$= Lex->parsed_unit_in_brackets_tail($2, $4);
+            if (!$3)
+              $$= $1;
+            else
+              $$= Lex->add_tail_to_query_expression_body($1, $3);
           }
-        | '(' query_primary
+        | query_expression_body_ext_parens
           {
-            Lex->push_select($2);
+            Lex->push_select(!$1->first_select()->next_select() ?
+                               $1->first_select() : $1->fake_select_lex);
           }
-          query_expression_tail ')'
+          query_expression_tail
           {
-            if (!($$= Lex->parsed_select_in_brackets($2, $4)))
-              YYABORT;
+            if (!($$= Lex->add_tail_to_query_expression_body_ext_parens($1, $3)))
+               MYSQL_YYABORT;
           }
         ;
 
-query_expression_unit:
-          query_primary
-          unit_type_decl
-          query_primary
-          {
-            if (!($$= Lex->parsed_select_expr_start($1, $3, $2.unit_type,
-                                                    $2.distinct)))
-              YYABORT;
-          }
-        | query_expression_unit
-          unit_type_decl
-          query_primary
+query_expression_body_ext_parens:
+          '(' query_expression_body_ext_parens ')'
+          { $$= $2; }
+        | '(' query_expression_body_ext ')'
           {
-            if (!($$= Lex->parsed_select_expr_cont($1, $3, $2.unit_type,
-                                                   $2.distinct, FALSE)))
-              YYABORT;
+            SELECT_LEX *sel= $2->first_select()->next_select() ?
+                               $2->fake_select_lex : $2->first_select();
+            sel->braces= true;
+            $$= $2;
           }
         ;
 
+/*
+  query_expression_body produces the same expressions as
+      <query expression body>
+*/
+
 query_expression_body:
-          query_primary
+          query_simple
           {
             Lex->push_select($1);
+            if (!($$= Lex->create_unit($1)))
+              MYSQL_YYABORT;
           }
-          query_expression_tail
+        | query_expression_body
+          unit_type_decl
           {
-            if (!($$= Lex->parsed_body_select($1, $3)))
-              MYSQL_YYABORT;
+            if (!$1->first_select()->next_select())
+            {
+              Lex->pop_select();
+            }
           }
-        | query_expression_unit
+          query_primary
           {
-            if (Lex->parsed_body_unit($1))
+            if (!($$= Lex->add_primary_to_query_expression_body($1, $4,
+                                                                $2.unit_type,
+                                                                $2.distinct,
+                                                                FALSE)))
               MYSQL_YYABORT;
           }
-          query_expression_tail
+        | query_expression_body_ext_parens
+          unit_type_decl
+          query_primary
           {
-            if (!($$= Lex->parsed_body_unit_tail($1, $3)))
+            if (!($$= Lex->add_primary_to_query_expression_body_ext_parens(
+                                                                $1, $3,
+                                                                $2.unit_type,
+                                                                $2.distinct)))
               MYSQL_YYABORT;
           }
         ;
 
-query_expression:
-          opt_with_clause
-          query_expression_body
-          {
-            if ($1)
-            {
-              $2->set_with_clause($1);
-              $1->attach_to($2->first_select());
-            }
-            $$= $2;
-          }
+/*
+  query_primary produces the same expressions as
+      <query primary>
+*/
+
+query_primary:
+          query_simple
+          { $$= $1; }
+        | query_expression_body_ext_parens
+          { $$= $1->first_select(); }
+        ;
+
+/*
+  query_simple produces the same expressions as
+      <simple table>
+*/
+
+query_simple:
+          simple_table { $$= $1;}
         ;
 
 subselect:
@@ -9384,11 +9497,63 @@ subselect:
           }
         ;
 
-
-/**
-  <table expression>, as in the SQL standard.
+/*
+  subquery produces the same expressions as
+     <subquery>
+
+  Consider the production rule of the SQL Standard
+     subquery:
+        '(' query_expression')'
+
+  This rule is equivalent to the rule
+     subquery:
+          '(' query_expression_no_with_clause ')'
+        | '(' with_clause query_expression_no_with_clause ')'
+  that in its turn is equivalent to
+     subquery:
+          '(' query_expression_body_ext ')'
+        | query_expression_body_ext_parens
+        | '(' with_clause query_expression_no_with_clause ')'
+
+  The latter can be re-written into
+     subquery:
+          query_expression_body_ext_parens ')'
+        | '(' with_clause query_expression_no_with_clause ')'
+
+  The last rule allows us to resolve properly the shift/reduce conflict
+  when subquery is used in expressions such as in the following queries
+     select (select * from t1 limit 1) + t2.a from t2
+     select * from t1 where t1.a [not] in (select t2.a from t2)
+
+  In the rule below %prec SUBQUERY_AS_EXPR forces the parser to perform a shift
+  operation rather then a reduce operation when ')' is encountered and can be
+  considered as the last symbol a query expression.
 */
 
+subquery:
+          query_expression_body_ext_parens %prec SUBQUERY_AS_EXPR
+          {
+            if (!$1->fake_select_lex)
+              $1->first_select()->braces= false;
+            else
+              $1->fake_select_lex->braces= false;
+            if (!($$= Lex->parsed_subselect($1)))
+              YYABORT;
+          }
+        | '(' with_clause query_expression_no_with_clause ')'
+          {
+            $3->set_with_clause($2);
+            $2->attach_to($3->first_select());
+            if (!($$= Lex->parsed_subselect($3)))
+              YYABORT;
+          }
+        ;
+
+opt_from_clause:
+        /* empty */ %prec EMPTY_FROM_CLAUSE
+        | from_clause
+        ;
+
 from_clause:
           FROM table_reference_list
         ;
@@ -9552,6 +9717,7 @@ select_lock_type:
           }
         ;
 
+
 opt_select_lock_type:
         /* empty */
         {
@@ -9563,6 +9729,7 @@ opt_select_lock_type:
         }
         ;
 
+
 opt_lock_wait_timeout_new:
         /* empty */
         {
@@ -9849,15 +10016,15 @@ bool_pri:
         ;
 
 predicate:
-          bit_expr IN_SYM '(' subselect ')'
+          bit_expr IN_SYM subquery
           {
-            $$= new (thd->mem_root) Item_in_subselect(thd, $1, $4);
+            $$= new (thd->mem_root) Item_in_subselect(thd, $1, $3);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | bit_expr not IN_SYM '(' subselect ')'
+        | bit_expr not IN_SYM subquery
           {
-            Item *item= new (thd->mem_root) Item_in_subselect(thd, $1, $5);
+            Item *item= new (thd->mem_root) Item_in_subselect(thd, $1, $4);
             if (unlikely(item == NULL))
               MYSQL_YYABORT;
             $$= negate_expression(thd, item);
@@ -10378,6 +10545,11 @@ primary_expr:
           column_default_non_parenthesized_expr
         | explicit_cursor_attr
         | '(' parenthesized_expr ')' { $$= $2; }
+        | subquery
+          {
+            if (!($$= Lex->create_item_query_expression(thd, $1->master_unit())))
+              MYSQL_YYABORT;
+          }
         ;
 
 string_factor_expr:
@@ -12129,35 +12301,12 @@ table_primary_ident:
           }
         ;
 
-
-/*
-  Represents a flattening of the following rules from the SQL:2003
-  standard. This sub-rule corresponds to the sub-rule
-  <table primary> ::= ... | <derived table> [ AS ] <correlation name>
-
-  <derived table> ::= <table subquery>
-  <table subquery> ::= <subquery>
-  <subquery> ::= <left paren> <query expression> <right paren>
-  <query expression> ::= [ <with clause> ] <query expression body>
-
-  For the time being we use the non-standard rule
-  select_derived_union which is a compromise between the standard
-  and our parser. Possibly this rule could be replaced by our
-  query_expression_body.
-*/
-
 table_primary_derived:
-          query_primary_parens opt_for_system_time_clause table_alias_clause
+          subquery
+          opt_for_system_time_clause table_alias_clause
           {
-            if (!($$= Lex->parsed_derived_select($1, $2, $3)))
-              YYABORT;
-          }
-        | '('
-          query_expression
-          ')' opt_for_system_time_clause table_alias_clause
-          {
-            if (!($$= Lex->parsed_derived_unit($2, $4, $5)))
-              YYABORT;
+            if (!($$= Lex->parsed_derived_table($1->master_unit(), $2, $3)))
+              MYSQL_YYABORT;
           }
         ;
 
@@ -12293,7 +12442,6 @@ table_alias:
 
 opt_table_alias_clause:
           /* empty */ { $$=0; }
-
         | table_alias_clause { $$= $1; }
         ;
 
@@ -12427,7 +12575,7 @@ opt_window_clause:
           {}
         | WINDOW_SYM
           window_def_list
-          {}
+	  {}
         ;
 
 window_def_list:
@@ -12756,10 +12904,8 @@ delete_limit_clause:
        | LIMIT limit_option ROWS_SYM EXAMINED_SYM { thd->parse_error(); MYSQL_YYABORT; }
         ;
 
-opt_order_limit_lock:
-          /* empty */ 
-          { $$= NULL; }
-        | order_or_limit
+order_limit_lock:
+          order_or_limit
           {
             $$= $1;
             $$->lock.empty();
@@ -12779,32 +12925,45 @@ opt_order_limit_lock:
             $$->lock= $1;
           }
         ;
+
+opt_order_limit_lock:
+          /* empty */
+          {
+            Lex->pop_select();
+            $$= NULL;
+          }
+        | order_limit_lock { $$= $1; }
+        ;
+
 query_expression_tail:
+          order_limit_lock
+        ;
+
+opt_query_expression_tail:
           opt_order_limit_lock
         ;
 
 opt_procedure_or_into:
-          /* empty */ 
-        {
-          $$.empty();
-        }
+          /* empty */
+          {
+            $$.empty();
+          }
         | procedure_clause opt_select_lock_type
-        {
-          $$= $2;
-        }
+          {
+            $$= $2;
+          }
         | into opt_select_lock_type
-        {
-          push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
-                              ER_WARN_DEPRECATED_SYNTAX,
-                              ER_THD(thd, ER_WARN_DEPRECATED_SYNTAX),
-                              "<select expression> INTO <destination>;",
-                              "'SELECT <select list> INTO <destination>"
-                              " FROM...'");
-          $$= $2;
-        }
+          {
+            push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
+                                ER_WARN_DEPRECATED_SYNTAX,
+                                ER_THD(thd, ER_WARN_DEPRECATED_SYNTAX),
+                                "<select expression> INTO <destination>;",
+                                "'SELECT <select list> INTO <destination>"
+                                " FROM...'");
+            $$= $2;
+          }
         ;
 
-
 order_or_limit:
           order_clause opt_limit_clause
           {
@@ -15199,16 +15358,6 @@ temporal_literal:
           }
         ;
 
-
-opt_with_clause:
-	  /*empty */ { $$= 0; }
-	| with_clause
-          {
-            $$= $1;
-          }
-	;
-
-
 with_clause:
           WITH opt_recursive
           {
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index 2314997c9d9..f3dc8614430 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -294,10 +294,10 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
 %parse-param { THD *thd }
 %lex-param { THD *thd }
 /*
-  Currently there are 49 shift/reduce conflicts.
+  Currently there are 40 shift/reduce conflicts.
   We should not introduce new conflicts any more.
 */
-%expect 49
+%expect 40
 
 /*
    Comments for TOKENS.
@@ -1108,6 +1108,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
 %left   '^'
 %left   MYSQL_CONCAT_SYM
 %left   NEG '~' NOT2_SYM BINARY
+%left SUBQUERY_AS_EXPR
 %left   COLLATE_SYM
 
 /*
@@ -1199,7 +1200,9 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
       ALTER TABLE t1 ADD SYSTEM VERSIONING;
 */
 %left   PREC_BELOW_CONTRACTION_TOKEN2
-%left   TEXT_STRING '(' VALUE_SYM VERSIONING_SYM
+%left   TEXT_STRING '(' ')' VALUE_SYM VERSIONING_SYM
+%left EMPTY_FROM_CLAUSE
+%right INTO
 
 %type <lex_str>
         DECIMAL_NUM FLOAT_NUM NUM LONG_NUM
@@ -1472,16 +1475,18 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
         query_specification
         table_value_constructor
         simple_table
+        query_simple
         query_primary
-        query_primary_parens
+        subquery
         select_into_query_specification
 
-
 %type <select_lex_unit>
-        query_specification_start
-        query_expression_body
         query_expression
-        query_expression_unit
+        query_expression_no_with_clause
+        query_expression_body_ext
+        query_expression_body_ext_parens
+        query_expression_body
+        query_specification_start
 
 %type <boolfunc2creator> comp_op
 
@@ -1506,7 +1511,9 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
 
 %type <order_limit_lock>
         query_expression_tail
+        opt_query_expression_tail
         order_or_limit
+        order_limit_lock
         opt_order_limit_lock
 
 %type <select_order> opt_order_clause order_clause order_list
@@ -1675,7 +1682,7 @@ END_OF_INPUT
         THEN_SYM WHEN_SYM DIV_SYM MOD_SYM OR2_SYM AND_AND_SYM DELETE_SYM
         MYSQL_CONCAT_SYM ORACLE_CONCAT_SYM
 
-%type <with_clause> opt_with_clause with_clause
+%type <with_clause> with_clause
 
 %type <lex_str_ptr> query_name
 
@@ -5270,7 +5277,7 @@ create_select_query_expression:
             if (Lex->parsed_insert_select($1->first_select()))
               MYSQL_YYABORT;
           }
-        | LEFT_PAREN_WITH with_clause query_expression_body ')'
+        | LEFT_PAREN_WITH with_clause query_expression_no_with_clause ')'
           {
             SELECT_LEX *first_select= $3->first_select();
             $3->set_with_clause($2);
@@ -6784,12 +6791,7 @@ parse_vcol_expr:
         ;
 
 parenthesized_expr:
-          query_expression
-          {
-            if (!($$= Lex->create_item_query_expression(thd, $1)))
-              MYSQL_YYABORT;
-          }
-        | expr
+          expr
         | expr ',' expr_list
           {
             $3->push_front($1, thd->mem_root);
@@ -6808,6 +6810,16 @@ virtual_column_func:
               MYSQL_YYABORT;
             $$= v;
           }
+        | subquery
+          {
+            Item *item;
+            if (!(item= new (thd->mem_root) Item_singlerow_subselect(thd, $1)))
+              MYSQL_YYABORT;
+            Virtual_column_info *v= add_virtual_expression(thd, item);
+            if (unlikely(!v))
+              MYSQL_YYABORT;
+            $$= v;
+          }
         ;
 
 expr_or_literal: column_default_non_parenthesized_expr | signed_literal ;
@@ -9267,7 +9279,7 @@ opt_ignore_leaves:
 */
 
 select:
-          query_expression_body
+          query_expression_no_with_clause
           {
             if (Lex->push_select($1->fake_select_lex ?
                                  $1->fake_select_lex :
@@ -9277,10 +9289,11 @@ select:
           opt_procedure_or_into
           {
             Lex->pop_select();
+            $1->set_with_clause(NULL);
             if (Lex->select_finalize($1, $3))
               MYSQL_YYABORT;
           }
-        | with_clause query_expression_body
+        | with_clause query_expression_no_with_clause
           {
             if (Lex->push_select($2->fake_select_lex ?
                                  $2->fake_select_lex :
@@ -9306,9 +9319,11 @@ select_into:
           }
           opt_order_limit_lock
           {
-            st_select_lex_unit *unit;
-            if (!(unit= Lex->parsed_body_select($1, $3)))
+            SELECT_LEX_UNIT *unit;
+            if (!(unit  = Lex->create_unit($1)))
               MYSQL_YYABORT;
+            if ($3)
+              unit= Lex->add_tail_to_query_expression_body(unit, $3);
             if (Lex->select_finalize(unit))
               MYSQL_YYABORT;
            }
@@ -9379,92 +9394,191 @@ select_into_query_specification:
           }
         ;
 
-opt_from_clause:
-        /* Empty */
-        | from_clause
+/**
+
+  The following grammar for query expressions conformant to
+  the latest SQL Standard is supported:
+
+    <query expression> ::=
+     [ <with clause> ] <query expression body>
+       [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]
+
+   <with clause> ::=
+     WITH [ RECURSIVE ] <with_list
+
+   <with list> ::=
+     <with list element> [ { <comma> <with list element> }... ]
+
+   <with list element> ::=
+     <query name> [ '(' <with column list> ')' ]
+         AS <table subquery>
+
+   <with column list> ::=
+     <column name list>
+
+   <query expression body> ::
+       <query term>
+     | <query expression body> UNION [ ALL | DISTINCT ] <query term>
+     | <query expression body> EXCEPT [ DISTINCT ] <query term>
+
+   <query term> ::=
+       <query primary>
+     | <query term> INTERSECT [ DISTINCT ] <query primary>
+
+   <query primary> ::=
+       <simple table>
+     | '(' <query expression body>
+       [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]
+       ')'
+
+   <simple table>
+       <query specification>
+     | <table value constructor>
+
+  <subquery>
+       '(' <query_expression> ')'
+
+*/
+
+/*
+  query_expression produces the same expressions as
+      <query expression>
+*/
+
+query_expression:
+          query_expression_no_with_clause
+          {
+            $1->set_with_clause(NULL);
+            $$= $1;
+          }
+        | with_clause
+          query_expression_no_with_clause
+          {
+            $2->set_with_clause($1);
+            $1->attach_to($2->first_select());
+            $$= $2;
+          }
         ;
 
+/*
+   query_expression_no_with_clause produces the same expressions as
+       <query expression> without [ <with clause> ]
+*/
 
-query_primary:
-          simple_table
-          { $$= $1; }
-        | query_primary_parens
-          { $$= $1; }
+query_expression_no_with_clause:
+          query_expression_body_ext { $$= $1; }
+        | query_expression_body_ext_parens { $$= $1; }
         ;
 
-query_primary_parens:
-          '(' query_expression_unit
+/*
+  query_expression_body_ext produces the same expressions as
+      <query expression body>
+       [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]
+    | '('... <query expression body>
+       [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]
+      ')'...
+  Note: number of ')' must be equal to the number of '(' in the rule above
+*/
+
+query_expression_body_ext:
+          query_expression_body
           {
-            if (Lex->parsed_unit_in_brackets($2))
-              MYSQL_YYABORT;
+            if ($1->first_select()->next_select())
+            {
+              if (Lex->parsed_multi_operand_query_expression_body($1))
+                MYSQL_YYABORT;
+            }
           }
-          query_expression_tail ')'
+          opt_query_expression_tail
           {
-            $$= Lex->parsed_unit_in_brackets_tail($2, $4);
+            if (!$3)
+              $$= $1;
+            else
+              $$= Lex->add_tail_to_query_expression_body($1, $3);
           }
-        | '(' query_primary
+        | query_expression_body_ext_parens
           {
-            Lex->push_select($2);
+            Lex->push_select(!$1->first_select()->next_select() ?
+                               $1->first_select() : $1->fake_select_lex);
           }
-          query_expression_tail ')'
+          query_expression_tail
           {
-            if (!($$= Lex->parsed_select_in_brackets($2, $4)))
-              YYABORT;
+            if (!($$= Lex->add_tail_to_query_expression_body_ext_parens($1, $3)))
+               MYSQL_YYABORT;
           }
         ;
 
-query_expression_unit:
-          query_primary
-          unit_type_decl
-          query_primary
-          {
-            if (!($$= Lex->parsed_select_expr_start($1, $3, $2.unit_type,
-                                                    $2.distinct)))
-              YYABORT;
-          }
-        | query_expression_unit
-          unit_type_decl
-          query_primary
+query_expression_body_ext_parens:
+          '(' query_expression_body_ext_parens ')'
+          { $$= $2; }
+        | '(' query_expression_body_ext ')'
           {
-            if (!($$= Lex->parsed_select_expr_cont($1, $3, $2.unit_type,
-                                                   $2.distinct, TRUE)))
-              YYABORT;
+            SELECT_LEX *sel= $2->first_select()->next_select() ?
+                               $2->fake_select_lex : $2->first_select();
+            sel->braces= true;
+            $$= $2;
           }
         ;
 
+/*
+  query_expression_body produces the same expressions as
+      <query expression body>
+*/
+
 query_expression_body:
-          query_primary
+          query_simple
           {
             Lex->push_select($1);
+            if (!($$= Lex->create_unit($1)))
+              MYSQL_YYABORT;
           }
-          query_expression_tail
+        | query_expression_body
+          unit_type_decl
           {
-            if (!($$= Lex->parsed_body_select($1, $3)))
-              MYSQL_YYABORT;
+            if (!$1->first_select()->next_select())
+            {
+              Lex->pop_select();
+            }
           }
-        | query_expression_unit
+          query_primary
           {
-            if (Lex->parsed_body_unit($1))
+            if (!($$= Lex->add_primary_to_query_expression_body($1, $4,
+                                                                $2.unit_type,
+                                                                $2.distinct,
+                                                                TRUE)))
               MYSQL_YYABORT;
           }
-          query_expression_tail
+        | query_expression_body_ext_parens
+          unit_type_decl
+          query_primary
           {
-            if (!($$= Lex->parsed_body_unit_tail($1, $3)))
+            if (!($$= Lex->add_primary_to_query_expression_body_ext_parens(
+                                                                $1, $3,
+                                                                $2.unit_type,
+                                                                $2.distinct)))
               MYSQL_YYABORT;
           }
         ;
 
-query_expression:
-          opt_with_clause
-          query_expression_body
-          {
-            if ($1)
-            {
-              $2->set_with_clause($1);
-              $1->attach_to($2->first_select());
-            }
-            $$= $2;
-          }
+/*
+  query_primary produces the same expressions as
+      <query primary>
+*/
+
+query_primary:
+          query_simple
+          { $$= $1; }
+        | query_expression_body_ext_parens
+          { $$= $1->first_select(); }
+        ;
+
+/*
+  query_simple produces the same expressions as
+      <simple table>
+*/
+
+query_simple:
+          simple_table { $$= $1;}
         ;
 
 subselect:
@@ -9475,11 +9589,63 @@ subselect:
           }
         ;
 
-
-/**
-  <table expression>, as in the SQL standard.
+/*
+  subquery produces the same expressions as
+     <subquery>
+
+  Consider the production rule of the SQL Standard
+     subquery:
+        '(' query_expression ')'
+
+  This rule is equivalent to the rule
+     subquery:
+          '(' query_expression_no_with_clause ')'
+        | '(' with_clause query_expression_no_with_clause ')'
+  that in its turn is equivalent to
+     subquery:
+          '(' query_expression_body_ext ')'
+        | query_expression_body_ext_parens
+        | '(' with_clause query_expression_no_with_clause ')'
+
+  The latter can be re-written into
+     subquery:
+          query_expression_body_ext_parens
+        | '(' with_clause query_expression_no_with_clause ')'
+
+  The last rule allows us to resolve properly the shift/reduce conflict
+  when subquery is used in expressions such as in the following queries
+     select (select * from t1 limit 1) + t2.a from t2
+     select * from t1 where t1.a [not] in (select t2.a from t2)
+
+  In the rule below %prec SUBQUERY_AS_EXPR forces the parser to perform a shift
+  operation rather then a reduce operation when ')' is encountered and can be
+  considered as the last symbol a query expression.
 */
 
+subquery:
+          query_expression_body_ext_parens %prec SUBQUERY_AS_EXPR
+          {
+            if (!$1->fake_select_lex)
+              $1->first_select()->braces= false;
+            else
+              $1->fake_select_lex->braces= false;
+            if (!($$= Lex->parsed_subselect($1)))
+              YYABORT;
+          }
+        | '(' with_clause query_expression_no_with_clause ')'
+          {
+            $3->set_with_clause($2);
+            $2->attach_to($3->first_select());
+            if (!($$= Lex->parsed_subselect($3)))
+              YYABORT;
+          }
+        ;
+
+opt_from_clause:
+        /* empty */ %prec EMPTY_FROM_CLAUSE
+        | from_clause
+        ;
+
 from_clause:
           FROM table_reference_list
         ;
@@ -9949,15 +10115,15 @@ bool_pri:
         ;
 
 predicate:
-          bit_expr IN_SYM '(' subselect ')'
+          bit_expr IN_SYM subquery
           {
-            $$= new (thd->mem_root) Item_in_subselect(thd, $1, $4);
+            $$= new (thd->mem_root) Item_in_subselect(thd, $1, $3);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | bit_expr not IN_SYM '(' subselect ')'
+        | bit_expr not IN_SYM subquery
           {
-            Item *item= new (thd->mem_root) Item_in_subselect(thd, $1, $5);
+            Item *item= new (thd->mem_root) Item_in_subselect(thd, $1, $4);
             if (unlikely(item == NULL))
               MYSQL_YYABORT;
             $$= negate_expression(thd, item);
@@ -10478,6 +10644,11 @@ primary_expr:
           column_default_non_parenthesized_expr
         | explicit_cursor_attr
         | '(' parenthesized_expr ')' { $$= $2; }
+        | subquery
+          {
+            if (!($$= Lex->create_item_query_expression(thd, $1->master_unit())))
+              MYSQL_YYABORT;
+          }
         ;
 
 string_factor_expr:
@@ -12229,37 +12400,15 @@ table_primary_ident:
           }
         ;
 
-
-/*
-  Represents a flattening of the following rules from the SQL:2003
-  standard. This sub-rule corresponds to the sub-rule
-  <table primary> ::= ... | <derived table> [ AS ] <correlation name>
-
-  <derived table> ::= <table subquery>
-  <table subquery> ::= <subquery>
-  <subquery> ::= <left paren> <query expression> <right paren>
-  <query expression> ::= [ <with clause> ] <query expression body>
-
-  For the time being we use the non-standard rule
-  select_derived_union which is a compromise between the standard
-  and our parser. Possibly this rule could be replaced by our
-  query_expression_body.
-*/
-
 table_primary_derived:
-          query_primary_parens opt_for_system_time_clause table_alias_clause
+          subquery
+          opt_for_system_time_clause table_alias_clause
           {
-            if (!($$= Lex->parsed_derived_select($1, $2, $3)))
-              YYABORT;
-          }
-        | '('
-          query_expression
-          ')' opt_for_system_time_clause table_alias_clause
-          {
-            if (!($$= Lex->parsed_derived_unit($2, $4, $5)))
-              YYABORT;
+            if (!($$= Lex->parsed_derived_table($1->master_unit(), $2, $3)))
+              MYSQL_YYABORT;
           }
         ;
+        ;
 
 opt_outer:
           /* empty */ {}
@@ -12393,7 +12542,6 @@ table_alias:
 
 opt_table_alias_clause:
           /* empty */ { $$=0; }
-
         | table_alias_clause { $$= $1; }
         ;
 
@@ -12856,10 +13004,8 @@ delete_limit_clause:
        | LIMIT limit_option ROWS_SYM EXAMINED_SYM { thd->parse_error(); MYSQL_YYABORT; }
         ;
 
-opt_order_limit_lock:
-          /* empty */ 
-          { $$= NULL; }
-        | order_or_limit
+order_limit_lock:
+          order_or_limit
           {
             $$= $1;
             $$->lock.empty();
@@ -12879,29 +13025,42 @@ opt_order_limit_lock:
             $$->lock= $1;
           }
         ;
+opt_order_limit_lock:
+          /* empty */
+          {
+            Lex->pop_select();
+            $$= NULL;
+          }
+        | order_limit_lock { $$= $1; }
+        ;
+
 query_expression_tail:
+          order_limit_lock
+        ;
+
+opt_query_expression_tail:
           opt_order_limit_lock
         ;
 
 opt_procedure_or_into:
           /* empty */ 
-        {
-          $$.empty();
-        }
+          {
+            $$.empty();
+          }
         | procedure_clause opt_select_lock_type
-        {
-          $$= $2;
-        }
+          {
+            $$= $2;
+          }
         | into opt_select_lock_type
-        {
-          push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
-                              ER_WARN_DEPRECATED_SYNTAX,
-                              ER_THD(thd, ER_WARN_DEPRECATED_SYNTAX),
-                              "<select expression> INTO <destination>;",
-                              "'SELECT <select list> INTO <destination>"
-                              " FROM...'");
-          $$= $2;
-        }
+          {
+            push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
+                                ER_WARN_DEPRECATED_SYNTAX,
+                                ER_THD(thd, ER_WARN_DEPRECATED_SYNTAX),
+                                "<select expression> INTO <destination>;",
+                                "'SELECT <select list> INTO <destination>"
+                                " FROM...'");
+            $$= $2;
+          }
         ;
 
 
@@ -15321,16 +15480,6 @@ temporal_literal:
           }
         ;
 
-
-opt_with_clause:
-	  /*empty */ { $$= 0; }
-	| with_clause
-          {
-            $$= $1;
-          }
-	;
-
-
 with_clause:
           WITH opt_recursive
           {