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
Triage: D3 (Medium)

[16 Apr 2007 11:59] Martin Friebe
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
[16 Apr 2007 12:00] Martin Friebe
proposal for a patch and tests

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

[16 Apr 2007 22:59] Sveta Smirnova
Thank you for the report.

Verified as described.
[14 Dec 2007 21:40] Bugs System
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.
[11 Jan 2008 12:18] Bugs System
Pushed into 6.0.5-alpha
[11 Jan 2008 12:21] Bugs System
Pushed into 5.1.23-rc
[11 Jan 2008 12:22] Bugs System
Pushed into 5.0.56
[18 Jul 2008 15:51] Paul Dubois
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.