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
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.21, 5.0bk
• 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, [e-mail] +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, [e-mail] +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, [e-mail] +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?