MDEV-19956 Queries with subqueries containing UNION are not parsed

Shift-Reduce conflicts prevented parsing some queries with subqueries that
used set operations when the subqueries occurred in expressions or in IN
predicands.
The grammar rules for query expression were transformed in order to avoid
these conflicts. New grammar rules employ an idea taken from MySQL 8.0.

1 files changed, 2318 insertions, 0 deletions

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