diff options
Diffstat (limited to 'mysql-test/t/union.test')
| -rw-r--r-- | mysql-test/t/union.test | 193 |
1 files changed, 110 insertions, 83 deletions
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index d61c02be45c..f5a5cad77e8 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -91,88 +91,6 @@ SELECT @a:=1 UNION SELECT @a:=@a+1; (SELECT 1) UNION (SELECT 2) ORDER BY (SELECT a); (SELECT 1,3) UNION (SELECT 2,1) ORDER BY (SELECT 2); - -# -# Bug#32858: Erro: "Incorrect usage of UNION and INTO" does not take subselects -# into account -# -CREATE TABLE t1 (a INT); -INSERT INTO t1 VALUES (1); - -SELECT a INTO @v FROM ( - SELECT a FROM t1 - UNION - SELECT a FROM t1 -) alias; - ---let $outfile = $MYSQLTEST_VARDIR/tmp/union.out.file ---error 0,1 ---remove_file $outfile - ---replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> -eval SELECT a INTO OUTFILE '$outfile' FROM ( - SELECT a FROM t1 - UNION - SELECT a FROM t1 WHERE 0 -) alias; ---remove_file $outfile - ---replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> -eval SELECT a INTO DUMPFILE '$outfile' FROM ( - SELECT a FROM t1 - UNION - SELECT a FROM t1 WHERE 0 -) alias; ---remove_file $outfile - -# -# INTO will not be allowed in subqueries in version 5.1 and above. -# -SELECT a FROM ( - SELECT a FROM t1 - UNION - SELECT a INTO @v FROM t1 -) alias; - ---replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> -eval SELECT a FROM ( - SELECT a FROM t1 - UNION - SELECT a INTO OUTFILE '$outfile' FROM t1 -) alias; ---remove_file $outfile - ---replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> -eval SELECT a FROM ( - SELECT a FROM t1 - UNION - SELECT a INTO DUMPFILE '$outfile' FROM t1 -) alias; ---remove_file $outfile - -SELECT a FROM t1 UNION SELECT a INTO @v FROM t1; - ---replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> -eval SELECT a FROM t1 UNION SELECT a INTO OUTFILE '$outfile' FROM t1; ---remove_file $outfile - ---replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> -eval SELECT a FROM t1 UNION SELECT a INTO DUMPFILE '$outfile' FROM t1; ---remove_file $outfile - ---error ER_WRONG_USAGE -SELECT a INTO @v FROM t1 UNION SELECT a FROM t1; - ---replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> ---error ER_WRONG_USAGE -eval SELECT a INTO OUTFILE '$outfile' FROM t1 UNION SELECT a FROM t1; - ---replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> ---error ER_WRONG_USAGE -eval SELECT a INTO DUMPFILE '$outfile' FROM t1 UNION SELECT a FROM t1; - -DROP TABLE t1; - # # Test bug reported by joc@presence-pc.com # @@ -335,7 +253,7 @@ SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a desc LIMIT 1; create temporary table t1 select a from t1 union select a from t2; drop temporary table t1; ---error 1093 +--error ER_TABLE_EXISTS_ERROR create table t1 select a from t1 union select a from t2; --error 1054 select a from t1 union select a from t2 order by t2.a; @@ -1102,7 +1020,49 @@ DROP TABLE t1; --echo End of 5.0 tests +-- echo # +-- echo # Bug#32858: Error: "Incorrect usage of UNION and INTO" does not take +-- echo # subselects into account +-- echo # +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1); + +-- echo # Tests fix in parser rule select_derived_union. +SELECT a INTO @v FROM ( + SELECT a FROM t1 + UNION + SELECT a FROM t1 +) alias; + +SELECT a INTO OUTFILE 'union.out.file' FROM ( + SELECT a FROM t1 + UNION + SELECT a FROM t1 WHERE 0 +) alias; + +SELECT a INTO DUMPFILE 'union.out.file2' FROM ( + SELECT a FROM t1 + UNION + SELECT a FROM t1 WHERE 0 +) alias; + +SELECT a FROM t1 UNION SELECT a INTO @v FROM t1; +SELECT a FROM t1 UNION SELECT a INTO OUTFILE 'union.out.file5' FROM t1; +SELECT a FROM t1 UNION SELECT a INTO OUTFILE 'union.out.file6' FROM t1; +--error ER_WRONG_USAGE +SELECT a INTO @v FROM t1 UNION SELECT a FROM t1; +--error ER_WRONG_USAGE +SELECT a INTO OUTFILE 'union.out.file7' FROM t1 UNION SELECT a FROM t1; +--error ER_WRONG_USAGE +SELECT a INTO DUMPFILE 'union.out.file8' FROM t1 UNION SELECT a FROM t1; + +-- echo # Tests fix in parser rule query_expression_body. +SELECT ( SELECT a UNION SELECT a ) INTO @v FROM t1; +SELECT ( SELECT a UNION SELECT a ) INTO OUTFILE 'union.out.file3' FROM t1; +SELECT ( SELECT a UNION SELECT a ) INTO DUMPFILE 'union.out.file4' FROM t1; +DROP TABLE t1; +remove_files_wildcard $MYSQLTEST_VARDIR/mysqld.1/data/test union.out.fil*; --echo # --echo # Bug #49734: Crash on EXPLAIN EXTENDED UNION ... ORDER BY @@ -1198,3 +1158,70 @@ execute stmt1; deallocate prepare stmt1; --echo End of 5.1 tests + +--echo # +--echo # Bug#57986 ORDER BY clause is not used after a UNION, +--echo # if embedded in a SELECT +--echo # + +CREATE TABLE t1 (c1 VARCHAR(10) NOT NULL, c2 INT NOT NULL); +CREATE TABLE t2 (c1 VARCHAR(10) NOT NULL, c2 INT NOT NULL); + + +INSERT INTO t1 (c1, c2) VALUES ('t1a', 1), ('t1a', 2), ('t1a', 3), ('t1b', 2), ('t1b', 1); +INSERT INTO t2 (c1, c2) VALUES ('t2a', 1), ('t2a', 2), ('t2a', 3), ('t2b', 2), ('t2b', 1); + +SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY c2, c1; +SELECT * FROM t1 UNION (SELECT * FROM t2) ORDER BY c2, c1; +SELECT * FROM t1 UNION (SELECT * FROM t2 ORDER BY c2, c1); + +SELECT c1, c2 FROM ( + SELECT c1, c2 FROM t1 + UNION + (SELECT c1, c2 FROM t2) + ORDER BY c2, c1 +) AS res; + +SELECT c1, c2 FROM ( + SELECT c1, c2 FROM t1 + UNION + (SELECT c1, c2 FROM t2) + ORDER BY c2 DESC, c1 LIMIT 1 +) AS res; + +SELECT c1, c2 FROM ( + SELECT c1, c2 FROM t1 + UNION + (SELECT c1, c2 FROM t2 ORDER BY c2 DESC, c1 LIMIT 1) +) AS res; + +SELECT c1, c2 FROM ( + SELECT c1, c2 FROM t1 + UNION + SELECT c1, c2 FROM t2 + ORDER BY c2 DESC, c1 DESC LIMIT 1 +) AS res; + +SELECT c1, c2 FROM ( + ( + (SELECT c1, c2 FROM t1) + UNION + (SELECT c1, c2 FROM t2) + ) + ORDER BY c2 DESC, c1 ASC LIMIT 1 +) AS res; + +DROP TABLE t1, t2; + +--echo # +--echo # Bug #58970 Problem Subquery (without referencing a table) +--echo # and Order By +--echo # + +SELECT(SELECT 0 AS a UNION SELECT 1 AS a ORDER BY a ASC LIMIT 1) AS dev; +SELECT(SELECT 0 AS a UNION SELECT 1 AS a ORDER BY a DESC LIMIT 1) AS dev; +SELECT(SELECT 0 AS a FROM dual UNION SELECT 1 AS a FROM dual ORDER BY a ASC LIMIT 1) AS dev; +SELECT(SELECT 0 AS a FROM dual UNION SELECT 1 AS a FROM dual ORDER BY a DESC LIMIT 1) AS dev; +SELECT(SELECT 1 AS a ORDER BY a) AS dev; +SELECT(SELECT 1 AS a LIMIT 1) AS dev; +SELECT(SELECT 1 AS a FROM dual ORDER BY a DESC LIMIT 1) AS dev; |