From: igor
Date: December 14 2007 9:42pm
Subject: bk commit into 5.0 tree (igor:1.2583) BUG#27848
List-Archive: http://lists.mysql.com/commits/40037
X-Bug: 27848
Message-Id: <20071214214252.B147153F349@igor-laptop.mysql.com>

Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-12-14 13:42:46-08:00, igor@stripped +3 -0
  Fixed bug #27848.
  In a union without braces, the order by at the end is applied to the 
  overall union. It therefore should not interfere with the individual
  select parts of the union.
  
  Fixed by changing our parser rules appropriately.

  mysql-test/r/union.result@stripped, 2007-12-14 13:42:41-08:00, igor@stripped +51 -0
    Added a test case for bug #27848.

  mysql-test/t/union.test@stripped, 2007-12-14 13:42:41-08:00, igor@stripped +44 -0
    Added a test case for bug #27848.

  sql/sql_yacc.yy@stripped, 2007-12-14 13:42:42-08:00, igor@stripped +10 -1
    Fixed bug #27848.
    In a union without braces, the order by at the end is applied to the 
    overall union. It therefore should not interfere with the individual
    select parts of the union.
    
    Fixed by changing our parser rules appropriately.

diff -Nrup a/mysql-test/r/union.result b/mysql-test/r/union.result
--- a/mysql-test/r/union.result	2007-03-22 06:58:37 -07:00
+++ b/mysql-test/r/union.result	2007-12-14 13:42:41 -08:00
@@ -1389,4 +1389,55 @@ select @var;
 1
 (select 2) union (select 1 into @var);
 ERROR 42000: Result consisted of more than one row
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (10), (20);
+CREATE TABLE t2 (b int);
+INSERT INTO t2 VALUES (10), (50), (50);
+SELECT a,1 FROM t1 
+UNION
+SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP
+ORDER BY a;
+a	1
+NULL	3
+10	1
+20	1
+50	2
+SELECT a,1 FROM t1 
+UNION
+SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP
+ORDER BY a DESC;
+a	1
+50	2
+20	1
+10	1
+NULL	3
+SELECT a,1 FROM t1 
+UNION
+SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP
+ORDER BY a ASC LIMIT 3;
+a	1
+NULL	3
+10	1
+20	1
+SELECT a,1 FROM t1 
+UNION ALL 
+SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP
+ORDER BY a DESC;
+a	1
+50	2
+20	1
+10	1
+10	1
+NULL	3
+SELECT a,1 FROM t1
+UNION 
+(SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP ORDER BY a);
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+SELECT a,1 FROM t1
+UNION ALL
+SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP ORDER BY a
+UNION
+SELECT 1,1;
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+DROP TABLE t1,t2;
 End of 5.0 tests
diff -Nrup a/mysql-test/t/union.test b/mysql-test/t/union.test
--- a/mysql-test/t/union.test	2007-03-21 11:53:51 -07:00
+++ b/mysql-test/t/union.test	2007-12-14 13:42:41 -08:00
@@ -877,4 +877,48 @@ DROP TABLE t1;
 select @var;
 --error 1172
 (select 2) union (select 1 into @var);
+
+#
+# Bug#27848: order-by of union clashes with rollup of select part
+#
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (10), (20);
+CREATE TABLE t2 (b int);
+INSERT INTO t2 VALUES (10), (50), (50);
+
+SELECT a,1 FROM t1 
+UNION
+SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP
+ORDER BY a;
+
+SELECT a,1 FROM t1 
+UNION
+SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP
+ORDER BY a DESC;
+
+SELECT a,1 FROM t1 
+UNION
+SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP
+ORDER BY a ASC LIMIT 3;
+
+SELECT a,1 FROM t1 
+UNION ALL 
+SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP
+ORDER BY a DESC;
+
+--error ER_WRONG_USAGE
+SELECT a,1 FROM t1
+UNION 
+(SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP ORDER BY a);
+
+--error ER_WRONG_USAGE
+SELECT a,1 FROM t1
+UNION ALL
+SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP ORDER BY a
+UNION
+SELECT 1,1;
+
+DROP TABLE t1,t2;
+
 --echo End of 5.0 tests
diff -Nrup a/sql/sql_yacc.yy b/sql/sql_yacc.yy
--- a/sql/sql_yacc.yy	2007-12-13 02:49:13 -08:00
+++ b/sql/sql_yacc.yy	2007-12-14 13:42:42 -08:00
@@ -4211,6 +4211,14 @@ select_paren:
               my_parse_error(ER(ER_SYNTAX_ERROR));
               MYSQL_YYABORT;
             }
+            if (sel->linkage == UNION_TYPE &&
+                sel->olap != UNSPECIFIED_OLAP_TYPE &&
+                sel->master_unit()->fake_select_lex)
+            {
+ 	       my_error(ER_WRONG_USAGE, MYF(0),
+                        "CUBE/ROLLUP", "ORDER BY");
+               MYSQL_YYABORT;
+            }
             /* select in braces, can't contain global parameters */
 	    if (sel->master_unit()->fake_select_lex)
               sel->master_unit()->global_parameters=
@@ -6165,7 +6173,8 @@ order_clause:
           SELECT_LEX *sel= lex->current_select;
           SELECT_LEX_UNIT *unit= sel-> master_unit();
 	  if (sel->linkage != GLOBAL_OPTIONS_TYPE &&
-	      sel->olap != UNSPECIFIED_OLAP_TYPE)
+              sel->olap != UNSPECIFIED_OLAP_TYPE &&
+              (sel->linkage != UNION_TYPE || sel->braces))
 	  {
 	    my_error(ER_WRONG_USAGE, MYF(0),
                      "CUBE/ROLLUP", "ORDER BY");