Bug #30596 GROUP BY optimization gives wrong result order
Submitted: 23 Aug 2007 16:07 Modified: 23 Oct 2007 17:59
Reporter: Martin Hansson
Status: Closed
Category:Server: Optimizer Severity:S2 (Serious)
Version: OS:Any
Assigned to: Martin Hansson Target Version:5.1.23
Tags: sr5_1, GROUP BY

[23 Aug 2007 16: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 16: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 17: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 18:35] Timour Katchaounov 
Changed priority to P2 as this is wrong result.
[27 Aug 2007 12: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 17: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 12: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 14:05] Martin Hansson 
The 'patch approved' was only for the 5.0 patch. A different patch is needed for 5.1.
[28 Aug 2007 18: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 9:44] Bugs System 
Pushed into 5.1.23-beta
[14 Sep 2007 9:46] Bugs System 
Pushed into 5.0.50
[23 Oct 2007 17:59] Paul DuBois 
Noted in 5.0.50, 5.1.23 changelogs.
[9 Nov 2007 1:14] James Day 
It appears that this change may have introduced bug #32202.