diff options
Diffstat (limited to 'mysql-test/main/brackets.test')
| -rw-r--r-- | mysql-test/main/brackets.test | 180 |
1 files changed, 180 insertions, 0 deletions
diff --git a/mysql-test/main/brackets.test b/mysql-test/main/brackets.test new file mode 100644 index 00000000000..9ca86b87032 --- /dev/null +++ b/mysql-test/main/brackets.test @@ -0,0 +1,180 @@ +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 # End of 10.4 tests + |