diff options
Diffstat (limited to 'mysql-test/main/brackets.result')
| -rw-r--r-- | mysql-test/main/brackets.result | 210 |
1 files changed, 210 insertions, 0 deletions
diff --git a/mysql-test/main/brackets.result b/mysql-test/main/brackets.result new file mode 100644 index 00000000000..194807290a0 --- /dev/null +++ b/mysql-test/main/brackets.result @@ -0,0 +1,210 @@ +select 1 union ( select 2 union select 3); +1 +1 +2 +3 +explain extended +select 1 union ( select 2 union select 3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +4 UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +NULL UNION RESULT <union1,4> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 1 AS `1` union /* select#4 */ select `__4`.`2` AS `2` from (/* select#2 */ select 2 AS `2` union /* select#3 */ select 3 AS `3`) `__4` +select 1 union ( select 1 union select 1); +1 +1 +explain extended +select 1 union ( select 1 union select 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +4 UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +NULL UNION RESULT <union1,4> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 1 AS `1` union /* select#4 */ select `__4`.`1` AS `1` from (/* select#2 */ select 1 AS `1` union /* select#3 */ select 1 AS `1`) `__4` +select 1 union all ( select 1 union select 1); +1 +1 +1 +explain extended +select 1 union all ( select 1 union select 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +4 UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 1 AS `1` union all /* select#4 */ select `__4`.`1` AS `1` from (/* select#2 */ select 1 AS `1` union /* select#3 */ select 1 AS `1`) `__4` +select 1 union ( select 1 union all select 1); +1 +1 +explain extended +select 1 union ( select 1 union all select 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +4 UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,4> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 1 AS `1` union /* select#4 */ select `__4`.`1` AS `1` from (/* select#2 */ select 1 AS `1` union all /* select#3 */ select 1 AS `1`) `__4` +select 1 union select 1 union all select 1; +1 +1 +1 +explain extended +select 1 union select 1 union all select 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 1 AS `1` union /* select#2 */ select 1 AS `1` union all /* select#3 */ select 1 AS `1` +(select 1 as a) union (select 2) order by a; +a +1 +2 +explain extended +(select 1 as a) union (select 2) order by a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 (/* select#1 */ select 1 AS `a`) union (/* select#2 */ select 2 AS `2`) order by `a` +/* select#1 */ select 1 AS `a` union /* select#2 */ select 2 AS `2` order by `a`; +a +1 +2 +explain extended +/* select#1 */ select 1 AS `a` union /* select#2 */ select 2 AS `2` order by `a`; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 /* 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))); +1 +1 +explain extended all +select 1 union ( select 1 union (select 1 union (select 1 union select 1))); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +8 UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +7 UNION <derived3> ALL NULL NULL NULL NULL 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +6 UNION <derived4> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +5 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL +NULL UNION RESULT <union3,6> ALL NULL NULL NULL NULL NULL NULL +NULL UNION RESULT <union2,7> ALL NULL NULL NULL NULL NULL NULL +NULL UNION RESULT <union1,8> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1/0 Filter Select: select `1` AS `1` */ select 1 AS `1` union /* select#8/0 */ select `__8`.`1` AS `1` from (/* select#2/1 Filter Select: select `1` AS `1` */ select 1 AS `1` union /* select#7/1 */ select `__7`.`1` AS `1` from (/* select#3/2 Filter Select: select `1` AS `1` */ select 1 AS `1` union /* select#6/2 */ select `__6`.`1` AS `1` from (/* select#4/3 Filter Select: select `1` AS `1` */ select 1 AS `1` union /* select#5/3 */ select 1 AS `1`) `__6`) `__7`) `__8` +# +# MDEV-6341: INSERT ... SELECT UNION with parenthesis +# +create table t1 (a int, b int); +insert into t1 (select 1,1 union select 2,2); +select * from t1 order by 1; +a b +1 1 +2 2 +delete from t1; +insert into t1 select 1,1 union select 2,2; +select * from t1 order by 1; +a b +1 1 +2 2 +drop table t1; +CREATE OR REPLACE TABLE t1 AS SELECT 1 AS a UNION SELECT 2; +select * from t1 order by 1; +a +1 +2 +drop table t1; +CREATE OR REPLACE TABLE t1 AS (SELECT 1 AS a UNION SELECT 2); +select * from t1 order by 1; +a +1 +2 +drop table t1; +CREATE OR REPLACE VIEW v1 AS (SELECT 1 AS a); +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select 1 AS `a`) latin1 latin1_swedish_ci +drop view v1; +CREATE OR REPLACE VIEW v1 AS SELECT 1 AS a UNION SELECT 2; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `a` union select 2 AS `2` latin1 latin1_swedish_ci +drop view v1; +CREATE OR REPLACE VIEW v1 AS (SELECT 1 AS a UNION SELECT 2); +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `a` union select 2 AS `2` latin1 latin1_swedish_ci +drop view v1; +# +# MDEV-10028: Syntax error on ((SELECT ...) UNION (SELECT ...)) +# +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)); +a +10 +20 +30 +(SELECT * FROM t1 UNION SELECT * FROM t1); +a +10 +20 +30 +((SELECT a FROM t1) LIMIT 1); +a +10 +SELECT * FROM (SELECT 1 UNION (SELECT 2 UNION SELECT 3)) t1; +1 +1 +2 +3 +DROP TABLE t1; +# +# test of several levels of ORDER BY / LIMIT +# +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; +a b +1 100 +2 200 +3 30 +(select a,b from t1 order by 1 limit 3) order by 2 limit 2; +a b +3 30 +1 100 +(select 10,1000 union select a,b from t1 order by 1 limit 3) order by 2 limit 2; +10 1000 +3 30 +1 100 +((select a,b from t1 order by 1 limit 3) order by 2 limit 2) order by 1 limit 1; +a b +1 100 +((select a,b from t1 order by 1 limit 3) order by 2 limit 2) order by 1; +a b +1 100 +3 30 +drop table t1; +# End of 10.4 tests |