#At file:///export/home/tmp/x/mysql-trunk-bugfixing-57986/ based on revid:alfranio.correia@stripped
3413 Magne Mahre 2010-12-03
Bug#57986 ORDER BY clause is not used after a UNION,
if embedded in a SELECT
An ORDER BY clause was bound to the incorrect
(sub-)statement when used in a UNION context.
In a query like:
SELECT * FROM a UNION SELECT * FROM b ORDER BY c
the result of SELECT * FROM b is sorted, and then
combined with a. The correct behaviour is that
the ORDER BY clause should be applied on the
final set.
In a UNION statement, there will be a select_lex
object for each of the two selects, and a
select_lex_unit object that describes the UNION
itself.
The bug was caused by using a grammar rule for
ORDER BY (and LIMIT) that bound these elements
to thd->lex->current_select, which points to the
last of the two selects, instead of to the
fake_select_lex member of the master select_lex_unit
object.
@ sql/sql_yacc.yy
Need to use (opt_)union_order_or_limit to
bind to the correct select_lex object.
modified:
mysql-test/r/union.result
mysql-test/t/union.test
sql/sql_yacc.yy
=== modified file 'mysql-test/r/union.result'
--- a/mysql-test/r/union.result 2010-11-29 13:30:18 +0000
+++ b/mysql-test/r/union.result 2010-12-03 08:05:25 +0000
@@ -1644,3 +1644,103 @@ 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;
=== modified file 'mysql-test/t/union.test'
--- a/mysql-test/t/union.test 2010-11-29 13:04:34 +0000
+++ b/mysql-test/t/union.test 2010-12-03 08:05:25 +0000
@@ -1116,5 +1116,58 @@ SELECT * FROM t2 UNION SELECT * FROM t2
DROP TABLE t1,t2;
-
--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;
=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy 2010-11-29 11:28:55 +0000
+++ b/sql/sql_yacc.yy 2010-12-03 08:05:25 +0000
@@ -9534,7 +9534,7 @@ table_factor:
;
select_derived_union:
- select_derived opt_order_clause opt_limit_clause
+ select_derived opt_union_order_or_limit
| select_derived_union
UNION_SYM
union_option
@@ -9550,7 +9550,7 @@ select_derived_union:
*/
Lex->pop_context();
}
- opt_order_clause opt_limit_clause
+ opt_union_order_or_limit
;
/* The equivalent of select_init2 for nested queries. */
@@ -14018,6 +14018,11 @@ union_opt:
| union_order_or_limit { $$= 1; }
;
+opt_union_order_or_limit:
+ /* empty */
+ | union_order_or_limit
+ ;
+
union_order_or_limit:
{
THD *thd= YYTHD;
Attachment: [text/bzr-bundle] bzr/magne.mahre@oracle.com-20101203080525-p6jzjje9cvhb5d8t.bundle
