Bug #30596 GROUP BY optimization gives wrong result order
Submitted: 23 Aug 2007 14:07 Modified: 23 Oct 2007 15:59
Reporter: Martin Hansson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version: OS:Any
Assigned to: Martin Hansson CPU Architecture:Any
Tags: GROUP BY, sr5_1

[23 Aug 2007 14:07] Martin Hansson 
Description:
The optimization that uses a unique index to remove GROUP BY suffers from the following flaw: It does not choose the index at the same time as removing GROUP BY, thus it cannot guarantee ordering the result by the columns in the GROUP BY list.

How to repeat:
CREATE TABLE t1(
  a INT, 
  b INT NOT NULL, 
  c INT NOT NULL, 
  d INT, 
  UNIQUE KEY (c,b)
);

INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);

SELECT DISTINCT c,b,d FROM t1 GROUP BY c,b,d;

Suggested fix:
When a unique index has been found, we know that GROUP BY can be skipped, but not the ORDER BY implied by GROUP BY. Hence add an ORDER BY on the same columns. This step can be skipped if ORDER BY NULL is used.
[23 Aug 2007 14:54] 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/32969

ChangeSet@1.2494, 2007-08-23 16:55:21+02:00, mhansson@linux-st28.site +4 -0
  Bug #30596  	GROUP BY optimization gives wrong result order
  
  The optimization that uses a unique index to remove GROUP BY, did not 
  ensure that the index was actually used, thus violating the ORDER BY
  that is impled by GROUP BY.
  Fixed by replacing GROUP BY with ORDER BY if the GROUP BY clause contains
  a unique index. In case GROUP BY ... ORDER BY null is used, GROUP BY is
  simply removed.
[23 Aug 2007 15:10] 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/32972

ChangeSet@1.2494, 2007-08-23 17:10:44+02:00, mhansson@linux-st28.site +5 -0
  Bug #30596  	GROUP BY optimization gives wrong result order
  
  The optimization that uses a unique index to remove GROUP BY, did not 
  ensure that the index was actually used, thus violating the ORDER BY
  that is impled by GROUP BY.
  Fixed by replacing GROUP BY with ORDER BY if the GROUP BY clause contains
  a unique index. In case GROUP BY ... ORDER BY null is used, GROUP BY is
  simply removed.
[23 Aug 2007 16:35] Timour Katchaounov 
Changed priority to P2 as this is wrong result.
[27 Aug 2007 10:45] 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/33140

ChangeSet@1.2494, 2007-08-27 12:45:30+02:00, mhansson@linux-st28.site +5 -0
  Bug #30596  	GROUP BY optimization gives wrong result order
  
  The optimization that uses a unique index to remove GROUP BY, did not 
  ensure that the index was actually used, thus violating the ORDER BY
  that is impled by GROUP BY.
  Fixed by replacing GROUP BY with ORDER BY if the GROUP BY clause contains
  a unique index. In case GROUP BY ... ORDER BY null is used, GROUP BY is
  simply removed.
[27 Aug 2007 15:33] 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/33158

ChangeSet@1.2494, 2007-08-27 17:33:41+02:00, mhansson@linux-st28.site +7 -0
  Bug #30596  	GROUP BY optimization gives wrong result order
  
  The optimization that uses a unique index to remove GROUP BY, did not 
  ensure that the index was actually used, thus violating the ORDER BY
  that is impled by GROUP BY.
  Fixed by replacing GROUP BY with ORDER BY if the GROUP BY clause contains
  a unique index. In case GROUP BY ... ORDER BY null is used, GROUP BY is
  simply removed.
[28 Aug 2007 10:09] 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/33207

ChangeSet@1.2571, 2007-08-28 12:10:55+02:00, mhansson@linux-st28.site +6 -0
  Bug #30596  	GROUP BY optimization gives wrong result order
    
  The optimization that uses a unique index to remove GROUP BY did not 
  ensure that the index was actually used, thus violating the ORDER BY
  that is impled by GROUP BY.
  Fixed by replacing GROUP BY with ORDER BY if the GROUP BY clause contains
  a unique index. In case GROUP BY ... ORDER BY null is used, GROUP BY is
  simply removed.
[28 Aug 2007 12:05] Martin Hansson 
The 'patch approved' was only for the 5.0 patch. A different patch is needed for 5.1.
[28 Aug 2007 16:03] 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/33236

ChangeSet@1.2571, 2007-08-28 18:01:29+02:00, mhansson@linux-st28.site +6 -0
  Bug #30596  	GROUP BY optimization gives wrong result order
    
  The optimization that uses a unique index to remove GROUP BY did not 
  ensure that the index was actually used, thus violating the ORDER BY
  that is implied by GROUP BY.
  Fixed by replacing GROUP BY with ORDER BY if the GROUP BY clause contains
  a unique index over non-nullable field(s). In case GROUP BY ... ORDER BY 
  null is used, GROUP BY is simply removed.
[14 Sep 2007 7:44] Bugs System 
Pushed into 5.1.23-beta
[14 Sep 2007 7:46] Bugs System 
Pushed into 5.0.50
[23 Oct 2007 15:59] Paul DuBois 
Noted in 5.0.50, 5.1.23 changelogs.
[9 Nov 2007 0:14] James Day 
It appears that this change may have introduced bug #32202.