diff options
Diffstat (limited to 'mysql-test/r/union.result')
| -rw-r--r-- | mysql-test/r/union.result | 125 |
1 files changed, 125 insertions, 0 deletions
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index d1342098329..bcf4df8e12c 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1644,3 +1644,128 @@ b 2 DROP TABLE t1,t2; End of 5.1 tests +# +# Bug#57986 ORDER BY clause is not used after a UNION, +# if embedded in a SELECT +# +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; +c1 c2 +t1a 1 +t1b 1 +t2a 1 +t2b 1 +t1a 2 +t1b 2 +t2a 2 +t2b 2 +t1a 3 +t2a 3 +SELECT * FROM t1 UNION (SELECT * FROM t2) ORDER BY c2, c1; +c1 c2 +t1a 1 +t1b 1 +t2a 1 +t2b 1 +t1a 2 +t1b 2 +t2a 2 +t2b 2 +t1a 3 +t2a 3 +SELECT * FROM t1 UNION (SELECT * FROM t2 ORDER BY c2, c1); +c1 c2 +t1a 1 +t1a 2 +t1a 3 +t1b 2 +t1b 1 +t2a 1 +t2a 2 +t2a 3 +t2b 2 +t2b 1 +SELECT c1, c2 FROM ( +SELECT c1, c2 FROM t1 +UNION +(SELECT c1, c2 FROM t2) +ORDER BY c2, c1 +) AS res; +c1 c2 +t1a 1 +t1b 1 +t2a 1 +t2b 1 +t1a 2 +t1b 2 +t2a 2 +t2b 2 +t1a 3 +t2a 3 +SELECT c1, c2 FROM ( +SELECT c1, c2 FROM t1 +UNION +(SELECT c1, c2 FROM t2) +ORDER BY c2 DESC, c1 LIMIT 1 +) AS res; +c1 c2 +t1a 3 +SELECT c1, c2 FROM ( +SELECT c1, c2 FROM t1 +UNION +(SELECT c1, c2 FROM t2 ORDER BY c2 DESC, c1 LIMIT 1) +) AS res; +c1 c2 +t1a 1 +t1a 2 +t1a 3 +t1b 2 +t1b 1 +t2a 3 +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; +c1 c2 +t2a 3 +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; +c1 c2 +t1a 3 +DROP TABLE t1, t2; +# +# Bug #58970 Problem Subquery (without referencing a table) +# and Order By +# +SELECT(SELECT 0 AS a UNION SELECT 1 AS a ORDER BY a ASC LIMIT 1) AS dev; +dev +0 +SELECT(SELECT 0 AS a UNION SELECT 1 AS a ORDER BY a DESC LIMIT 1) AS dev; +dev +1 +SELECT(SELECT 0 AS a FROM dual UNION SELECT 1 AS a FROM dual ORDER BY a ASC LIMIT 1) AS dev; +dev +0 +SELECT(SELECT 0 AS a FROM dual UNION SELECT 1 AS a FROM dual ORDER BY a DESC LIMIT 1) AS dev; +dev +1 +SELECT(SELECT 1 AS a ORDER BY a) AS dev; +dev +1 +SELECT(SELECT 1 AS a LIMIT 1) AS dev; +dev +1 +SELECT(SELECT 1 AS a FROM dual ORDER BY a DESC LIMIT 1) AS dev; +dev +1 |