diff options
Diffstat (limited to '')
| -rw-r--r-- | mysql-test/main/brackets.test | 2501 |
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 + |