List:Commits« Previous MessageNext Message »
From:Magne Mahre Date:January 10 2011 12:43pm
Subject:bzr commit into mysql-5.5 branch (magne.mahre:3227) Bug#58970
View as plain text  
#At file:///export/home/tmp/x/mysql-5.5-58970/ based on revid:magne.mahre@stripped

 3227 Magne Mahre	2011-01-10
      Bug#58970 Problem Subquery (without referencing a table) 
                and Order By
            
      When having a UNION statement in a subquery, with no
      referenced tables (or only a reference to the virtual
      table 'dual'), the UNION did not allow an ORDER BY clause.
            
            i.e:
                SELECT(SELECT 1 AS a UNION 
                       SELECT 0 AS a 
                       ORDER BY a) AS b  or
                SELECT(SELECT 1 AS a FROM dual UNION 
                       SELECT 0 as a 
                       ORDER BY a) AS b
            
            
      In addition, an ORDER BY / LIMIT clause was not accepted
      in subqueries even for single SELECT statements with no 
      referenced tables (or with 'dual' as table reference)
            
         i.e: 
            SELECT(SELECT 1 AS a ORDER BY a) AS b  or
            SELECT(SELECT 1 AS a FROM dual ORDER BY a) AS b
            
      The fix was to allow an optional ORDER BY/LIMIT clause to
      the grammar for these cases.
            
      See also: Bug#57986

    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	2011-01-10 12:16:50 +0000
+++ b/mysql-test/r/union.result	2011-01-10 12:43:12 +0000
@@ -1744,3 +1744,28 @@ ORDER BY c2 DESC, c1 ASC LIMIT 1
 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

=== modified file 'mysql-test/t/union.test'
--- a/mysql-test/t/union.test	2011-01-10 12:16:50 +0000
+++ b/mysql-test/t/union.test	2011-01-10 12:43:12 +0000
@@ -1171,3 +1171,16 @@ SELECT c1, c2 FROM (
 ) 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;

=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy	2011-01-10 12:16:50 +0000
+++ b/sql/sql_yacc.yy	2011-01-10 12:43:12 +0000
@@ -13914,7 +13914,7 @@ query_specification:
         ;
 
 query_expression_body:
-          query_specification
+          query_specification opt_union_order_or_limit
         | query_expression_body
           UNION_SYM union_option 
           {
@@ -13922,6 +13922,7 @@ query_expression_body:
               MYSQL_YYABORT;
           }
           query_specification
+          opt_union_order_or_limit
           {
             Lex->pop_context();
             $$= $1;


Attachment: [text/bzr-bundle] bzr/magne.mahre@oracle.com-20110110124312-awlhreokzgvyt6ow.bundle
Thread
bzr commit into mysql-5.5 branch (magne.mahre:3227) Bug#58970Magne Mahre10 Jan