List:Commits« Previous MessageNext Message »
From:Magne Mahre Date:December 3 2010 8:05am
Subject:bzr commit into mysql-trunk-bugfixing branch (magne.mahre:3413) Bug#57986
View as plain text  
#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
Thread
bzr commit into mysql-trunk-bugfixing branch (magne.mahre:3413) Bug#57986Magne Mahre3 Dec
  • Re: bzr commit into mysql-trunk-bugfixing branch (magne.mahre:3413)Bug#57986Davi Arnaut3 Dec