select 1 union ( select 2 union select 3);
explain extended
select 1 union ( select 2 union select 3);
select 1 union ( select 1 union select 1);
explain extended
select 1 union ( select 1 union select 1);
select 1 union all ( select 1 union select 1);
explain extended
select 1 union all ( select 1 union select 1);
select 1 union ( select 1 union all select 1);
explain extended
select 1 union ( select 1 union all select 1);
select 1 union select 1 union all select 1;
explain extended
select 1 union select 1 union all select 1;

(select 1 as a) union (select 2) order by a;
explain extended
(select 1 as a) union (select 2) order by a;
/* select#1 */ select 1 AS `a` union /* select#2 */ select 2 AS `2` order by `a`;
explain extended
/* select#1 */ select 1 AS `a` union /* select#2 */ select 2 AS `2` order by `a`;

select 1 union ( select 1 union (select 1 union (select 1 union select 1)));
explain extended all
select 1 union ( select 1 union (select 1 union (select 1 union select 1)));

--echo #
--echo # MDEV-6341: INSERT ... SELECT UNION with parenthesis
--echo #
create table t1 (a int, b int);
insert into t1 (select 1,1 union select 2,2);
select * from t1 order by 1;
delete from t1;
insert into t1 select 1,1 union select 2,2;
select * from t1 order by 1;
drop table t1;
CREATE OR REPLACE TABLE t1 AS SELECT 1 AS a UNION SELECT 2;
select * from t1 order by 1;
drop table t1;
CREATE OR REPLACE TABLE t1 AS (SELECT 1 AS a UNION SELECT 2);
select * from t1 order by 1;
drop table t1;
CREATE OR REPLACE VIEW v1 AS (SELECT 1 AS a);
show create view v1;
drop view v1;
CREATE OR REPLACE VIEW v1 AS SELECT 1 AS a UNION SELECT 2;
show create view v1;
drop view v1;
CREATE OR REPLACE VIEW v1 AS (SELECT 1 AS a UNION SELECT 2);
show create view v1;
drop view v1;


--echo #
--echo # MDEV-10028: Syntax error on ((SELECT ...) UNION (SELECT ...))
--echo #
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
INSERT INTO t1 VALUES (30);

((SELECT a FROM t1) UNION (SELECT a FROM t1));
(SELECT * FROM t1 UNION SELECT * FROM t1);
((SELECT a FROM t1) LIMIT 1);
SELECT * FROM (SELECT 1 UNION (SELECT 2 UNION SELECT 3)) t1;
DROP TABLE t1;

--echo #
--echo # test of several levels of ORDER BY / LIMIT
--echo #
create table t1 (a int, b int);
insert into t1 (a,b) values (1, 100), (2, 200), (3,30), (4,4);

select a,b from t1 order by 1 limit 3;
(select a,b from t1 order by 1 limit 3) order by 2 limit 2;
(select 10,1000 union select a,b from t1 order by 1 limit 3) order by 2 limit 2;
((select a,b from t1 order by 1 limit 3) order by 2 limit 2) order by 1 limit 1;
((select a,b from t1 order by 1 limit 3) order by 2 limit 2) order by 1;

drop table t1;

--echo #
--echo # MDEV-16359: union with 3 selects in brackets
--echo #

select 1 union select 1 union select 1;
(select 1 union select 1 union select 1);
((select 1) union (select 1) union (select 1));

--echo #
--echo # MDEV-16357: union in brackets with tail
--echo #             union with tail in brackets
--echo #

CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES(1),(2),(3),(4);

CREATE TABLE t2 (a int);
INSERT INTO t2 VALUES (4),(5),(6),(7);

(SELECT a FROM t1  UNION SELECT a FROM t2) LIMIT 1;
(SELECT a FROM t1  UNION SELECT a FROM t2) ORDER BY a DESC;

(SELECT a FROM t1  UNION SELECT a FROM t2 LIMIT 1);

DROP TABLE t1,t2;

--echo #
--echo # MDEV-19324: ((SELECT ...) ORDER BY col ) LIMIT n
--echo #

create table t1 (a int);
insert into t1 values (10),(20),(30);

let $q1=
select a from t1 order by a desc limit 1;
eval $q1;
eval explain extended $q1;
eval explain format=json $q1;

let $q2=
(select a from t1 order by a desc) limit 1;
eval $q2;
eval explain extended $q2;
eval explain format=json $q2;

let $q1=
(select a from t1 where a=20 union select a from t1) order by a desc limit 1;
eval $q1;
eval explain extended $q1;
eval explain format=json $q1;

let $q2=
((select a from t1 where a=20 union select a from t1) order by a desc) limit 1;
eval $q2;
eval explain extended $q2;
eval explain format=json $q2;

drop table t1;

--echo #
--echo # MDEV-19363: ((SELECT ...) ORDER BY col ) LIMIT n UNION ...
--echo #

create table t1 (pk int);
insert into t1 values (5),(4),(1),(2),(3);

let $q=
((select * from t1 order by pk) limit 2) union (select * from t1 where pk > 4);
eval $q;
eval explain extended $q;
eval explain format=json $q;

drop table t1;

--echo #
--echo # MDEV-18689: parenthesis around table names and derived tables
--echo #

select * from ( mysql.db );

create table t1 (a int);
insert into t1 values (7), (2), (7);

select * from (t1);
select * from ((t1));
select * from (t1 t) where t.a > 5;
select * from ((t1 t)) where t.a > 5;

select * from ((select a, sum(a) from t1 group by a) t);
select * from (((select a, sum(a) from t1 group by a) t));

update (t1 t) set t.a=t.a+1;
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

#enable after fix MDEV-29554
--disable_view_protocol
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;
--enable_view_protocol

--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