MySQL Bugs: #27848: rollup in union part causes error with order of union

Bug #27848	rollup in union part causes error with order of union
Submitted:	16 Apr 2007 11:59	Modified:	18 Jul 2008 15:51
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.40 4.1.23	OS:	Any
Assigned to:	Igor Babaev	CPU Architecture:	Any
Tags:	Contribution, order by, rollup, UNION

Description:
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.

In the example the order by even uses a column, not availabel to the 2nd select part. Yet the 2nd select part fails due to a mix of rollup/order

select a,1 from t1 UNION select b, count(*) from t2 group by b with rollup order by a;

How to repeat:
create table t1 (a int);
insert into t1 values (10), (20);
create table t2 (b int);
insert into t2 values (5), (50), (50);

select a,1 from t1 UNION select b, count(*) from t2 group by b with rollup order by a;

drop table t1,t2;

Suggested fix:
see patch

proposal for a patch and tests

Attachment: union_rollup.patch (text/x-patch), 2.56 KiB.

Thank you for the report.

Verified as described.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40037

ChangeSet@1.2583, 2007-12-14 13:42:46-08:00, igor@olga.mysql.com +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.

Pushed into 6.0.5-alpha

Pushed into 5.1.23-rc

Pushed into 5.0.56

Noted in 5.0.56, 5.1.23, 6.0.5 changelogs.

An ORDER BY at the end of a UNION affected individual SELECT 
statements rather than the overall query result.