summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/brackets.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/brackets.test')
-rw-r--r--mysql-test/main/brackets.test2501
1 files changed, 2501 insertions, 0 deletions
diff --git a/mysql-test/main/brackets.test b/mysql-test/main/brackets.test
new file mode 100644
index 00000000..9a0c204e
--- /dev/null
+++ b/mysql-test/main/brackets.test
@@ -0,0 +1,2501 @@
+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
+
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-22 19:50:47 +0000