Bug #69750 mysql 5.6.7-rc gives incorrect result when using distinct with order by

• Submitted: 15 Jul 2013 16:24
• Modified: 15 Jul 2013 17:37
• Reporter: Manu Agarwal
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.6.7-rc
• OS: Linux
• CPU Architecture: Any
• Tags: distinct, order by

[15 Jul 2013 16:24] Manu Agarwal

Description:
I am querying a table with multiple columns(with date as one of the columns). If When I use select distinct date from table order by date desc, it gives me the incorrect result.

mysql> select distinct date from _auto order by date desc;
+------------+
| date       |
+------------+
| 2530-11-20 |
| 2530-11-20 |
| 2530-11-20 |
| 2530-11-20 |
| 2530-11-20 |
| 2530-11-20 |
+------------+
6 rows in set (0.00 sec)
 
mysql> select date from _auto group by date order by date desc;
+------------+
| date       |
+------------+
| 2013-07-12 |
| 2013-07-11 |
| 2013-07-10 |
| 2013-07-09 |
| 2013-07-08 |
| 2013-07-05 |
+------------+
6 rows in set (0.00 sec)
 

Also, mysql> select distinct date from _auto order by date desc;
+------------+
| date       |
+------------+
| 2530-11-20 |
| 2530-11-20 |
| 2530-11-20 |
| 2530-11-20 |
| 2530-11-20 |
| 2530-11-20 |
+------------+
6 rows in set (0.00 sec)
 
mysql> select distinct `date` from _auto order by date desc;
+------------+
| date       |
+------------+
| 2530-11-20 |
| 2530-11-20 |
| 2530-11-20 |
| 2530-11-20 |
| 2530-11-20 |
| 2530-11-20 |
+------------+
6 rows in set (0.00 sec)
 
mysql> select distinct `date` from _auto order by date ;
+------------+
| date       |
+------------+
| 2013-07-05 |
| 2013-07-08 |
| 2013-07-09 |
| 2013-07-10 |
| 2013-07-11 |
| 2013-07-12 |
+------------+
6 rows in set (0.00 sec)
 
mysql> select distinct date from _auto order by date ;
+------------+
| date       |
+------------+
| 2013-07-05 |
| 2013-07-08 |
| 2013-07-09 |
| 2013-07-10 |
| 2013-07-11 |
| 2013-07-12 |
+------------+
6 rows in set (0.00 sec)
 

How to repeat:
Create a table with date as a column and query as above on that table. distinct with order by desc, fives the incorrect result.

[15 Jul 2013 16:47] Hartmut Holzgraefe

Didn't test specifically against 5.6.7 as i don't have it installed right now, but i can't reproduce this in current 5.6.12 or in older 5.6.5 using following simple test:

  create table dat(id int primary key auto_increment, d date);

  insert intop dat set d='2013-07-08';
  insert intop dat set d='2013-07-09';
  insert intop dat set d='2013-07-10';
  insert intop dat set d='2013-07-11';
  insert intop dat set d='2013-07-12';

  select d from dat order by d desc;
  select distinct d from dat order by d desc;

Both selects return the same result set with 
the correct year 2013 ...

[15 Jul 2013 17:37] MySQL Verification Team

Hello Manu,

Thank you for the report.

I can not repeat described behavior on latest MySQL version 5.6.12 and with dummy data. Additionally version 5.6.7-rc(2012-09-29, Release Candidate) is old and many bugs were fixed since. Please could you try with the latest version and inform us if you can repeat this with the reproducible data?

We do not fix bugs that are not repeatable with current versions.

Thanks,
Umesh