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.   Similar behaviour was seen on LIMIT
clauses as well.
            
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.  Similarly, the same behaviour was also
seen on derived tables.
            
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.

2 files changed, 154 insertions, 0 deletions

diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index 33fc4333d1c..7f0ec2275d7 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -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;
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index 596fc5f41ef..694f1bab15c 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -1117,3 +1117,57 @@ 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;