Bug #21456 SELECT DISTINCT(x) produces incorrect results when using order by
Submitted: 4 Aug 2006 22:14 Modified: 19 Sep 2006 16:30
Reporter: Morgan Tocker Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.21, 5.0bk OS:
Assigned to: Georgi Kodinov CPU Architecture:Any

[4 Aug 2006 22:14] Morgan Tocker
Description:
This appears to be a bug introduced in 4.1.21

How to repeat:
mysql> create table sort_test (pkey tinyint unsigned auto_increment primary key, sort tinyint unsigned);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into sort_test (sort) values (1), (3), (2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select distinct pkey, sort from sort_test order by sort;
+------+------+
| pkey | sort |
+------+------+
|    1 |    1 | 
|    2 |    3 | 
|    3 |    2 | 
+------+------+
3 rows in set (0.00 sec)
[5 Aug 2006 14:35] Hartmut Holzgraefe
mysqltest test case

Attachment: bug21456.tar.gz (application/x-gzip, text), 724 bytes.

[5 Aug 2006 14:38] Hartmut Holzgraefe
Verified, 4.1.20 and 5.0.24 are ok, 4.1.21 and 5.0bk show the wrong behavior
(see attached test case)
[17 Aug 2006 9: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/10578

ChangeSet@1.2533, 2006-08-17 12:10:08+03:00, gkodinov@macbook.gmz +3 -0
  Bug #21456: SELECT DISTINCT(x) produces incorrect results when using order by
   GROUP BY/DISTINCT pruning optimization must be done before ORDER BY 
   optimization because ORDER BY may be removed when GROUP BY/DISTINCT sorts
   as a side effect, e.g. in 
   SELECT DISTINCT <non-key-col>,<pk> FROM t1 ORDER BY <non-key-col> 
   DISTINCT must be removed before ORDER BY as if done the other way around 
   it will remove both.
[23 Aug 2006 12:35] 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/10754

ChangeSet@1.2533, 2006-08-23 15:37:12+03:00, timour@lamia.home +2 -0
  BUG#21456: SELECT DISTINCT(x) produces incorrect results when using order by
  
  GROUP BY/DISTINCT pruning optimization must be done before ORDER BY 
  optimization because ORDER BY may be removed when GROUP BY/DISTINCT
  sorts as a side effect, e.g. in 
    SELECT DISTINCT <non-key-col>,<pk> FROM t1
    ORDER BY <non-key-col> DISTINCT
  must be removed before ORDER BY as if done the other way around
  it will remove both.
[23 Aug 2006 13:44] 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/10763

ChangeSet@1.2533, 2006-08-23 16:46:57+03:00, timour@lamia.home +3 -0
  Bug #21456: SELECT DISTINCT(x) produces incorrect results when using order by
  
  GROUP BY/DISTINCT pruning optimization must be done before ORDER BY 
  optimization because ORDER BY may be removed when GROUP BY/DISTINCT
  sorts as a side effect, e.g. in 
    SELECT DISTINCT <non-key-col>,<pk> FROM t1
    ORDER BY <non-key-col> DISTINCT
  must be removed before ORDER BY as if done the other way around
  it will remove both.
[29 Aug 2006 13:24] Evgeny Potemkin
Fixed in 5.0.25
[30 Aug 2006 20:40] Paul Dubois
Noted in 4.1.22, 5.0.25 changelogs.

Setting report back to NDI pending merge into 5.1.x.
[19 Sep 2006 9:34] Georgi Kodinov
Merged into 5.1.12-beta
[19 Sep 2006 16:30] Paul Dubois
Noted in 5.1.12 changelog.
[26 Sep 2006 4:28] Jerry Zheng
Do you have plan to release fix for 4.1.21? Is it downloadable?