Bug #59308 Incorrect result for SELECT DISTINCT <col>... ORDER BY <col> DESC
Submitted: 5 Jan 2011 11:46 Modified: 28 Feb 2011 20:57
Reporter: Ole John Aske Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.6.99, 5.5, 5.1 -> OS:Any
Assigned to: Ole John Aske CPU Architecture:Any
Tags: regression
Triage: Triaged: D2 (Serious)

[5 Jan 2011 11:46] Ole John Aske
Description:
A query of the form:

  SELECT DISTINCT <column> ... ORDER BY <column> DESC;

Return an incorrect result where the values for <column> in
the result set has been entirely replaced with NULL values.

NOTE: The same testcase also results in a memory leak which
       already has been reported as bug#59110.

How to repeat:
drop table if exists `t1`;
create table `t1` (`a` int,key (`a`)) engine=myisam;
insert into `t1` values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
select distinct `a`,1 from `t1` where `a` <> 1 order by `a` desc;

+------+---+
| a    | 1 |
+------+---+
| NULL | 1 |
| NULL | 1 |
| NULL | 1 |
| NULL | 1 |
| NULL | 1 |
| NULL | 1 |
| NULL | 1 |
| NULL | 1 |
| NULL | 1 |
+------+---+
9 rows in set (0.95 sec)
[5 Jan 2011 11:56] Valeriy Kravchuk
Verified just as described:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.5.8 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists `t1`;
Query OK, 0 rows affected (0.06 sec)

mysql> create table `t1` (`a` int,key (`a`)) engine=myisam;
Query OK, 0 rows affected (0.13 sec)

mysql> insert into `t1` values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select distinct `a`,1 from `t1` where `a` <> 1 order by `a` desc;
+------+---+
| a    | 1 |
+------+---+
| NULL | 1 |
| NULL | 1 |
| NULL | 1 |
| NULL | 1 |
| NULL | 1 |
| NULL | 1 |
| NULL | 1 |
| NULL | 1 |
| NULL | 1 |
+------+---+
9 rows in set (0.02 sec)

mysql> select distinct `a`,1 from `t1` where `a` <> 1;
+------+---+
| a    | 1 |
+------+---+
|    2 | 1 |
|    3 | 1 |
|    4 | 1 |
|    5 | 1 |
|    6 | 1 |
|    7 | 1 |
|    8 | 1 |
|    9 | 1 |
|   10 | 1 |
+------+---+
9 rows in set (0.00 sec)

mysql> explain select distinct `a`,1 from `t1` where `a` <> 1 order by `a` desc;

+----+-------------+-------+------+---------------+------+---------+------+-----
-+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
 | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-----------------------------+
|  1 | SIMPLE      | t1    | ALL  | a             | NULL | NULL    | NULL |   10
 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-----------------------------+
1 row in set (0.30 sec)
[5 Jan 2011 11:58] Valeriy Kravchuk
5.0.91 is not affected:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.91-community-nt MySQL Community Edition (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists `t1`;
Query OK, 0 rows affected (0.59 sec)

mysql> create table `t1` (`a` int,key (`a`)) engine=myisam;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into `t1` values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select distinct `a`,1 from `t1` where `a` <> 1 order by `a` desc;
+------+---+
| a    | 1 |
+------+---+
|   10 | 1 |
|    9 | 1 |
|    8 | 1 |
|    7 | 1 |
|    6 | 1 |
|    5 | 1 |
|    4 | 1 |
|    3 | 1 |
|    2 | 1 |
+------+---+
9 rows in set (0.03 sec)

So, this is a regression bug.
[5 Jan 2011 13:49] 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/127976

3534 Ole John Aske	2011-01-05
      Fix for bug#59308: Incorrect result for SELECT DISTINCT <col>... ORDER BY <col> DESC.
      
      Also fix bug#59110: Memory leak of QUICK_SELECT_I allocated memory.
      
      Root cause of these bugs are that test_if_skip_sort_order() decided to
      revert the 'skip_sort_order' descision (and use filesort) after the
      query plan has been updated to reflect a 'skip' of the sort order.
      
      This might happen in 'check_reverse_order:' if we have a 
      select->quick which could not be made descending by appending 
      a QUICK_SELECT_DESC. ().
      
      The original 'save_quick' was then restored after the QEP has been modified, which caused:
      
       - An incorrect 'precomputed_group_by= TRUE' may have been set, and not reverted, as
         part of the already modifified QEP (Bug#59398)
       - A 'select->quick' might have been created which we fail to delete (bug#59110).
      
      This fix is a refactorication of test_if_skip_sort_order() where all logic
      related to modification of QEP (controlled by argument 'bool no_changes'), is
      moved to the end of test_if_skip_sort_order(), and done after *all* 'test_if_skip'
      checks has been performed - including the 'check_reverse_order:' checks.
      
      The refactorication above contains now intentional changes to the logic which has been
      moved to the end of the function.
      
      Furthermore, a smaller part of the fix address the handling of the select->quick objects 
      which may already exists when we call 'test_if_skip_sort_order()' (save_quick) - and
      new select->quick's created during test_if_skip_sort_order():
      
        - Before new select->quick may be created by calling ::test_quick_select(), we
          set 'select->quick= 0' to avoid that ::test_quick_select() prematurely
          delete the save_quick's. (After this call we may have both a 'save_quick' 
          and 'select->quick')
      
        - All returns from ::test_if_skip_sort_order() where we may have both a
          'save_quick' and a 'select->quick' has been changed to goto's to the
          exit points 'skiped_sort_order:' or 'need_filesort:' where we
          decide which of the QUICK_SELECT's to keep, and delete the other.
[6 Jan 2011 8:02] 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/128047

3534 Ole John Aske	2011-01-06
      Updated Fix for bug#59308: Incorrect result for SELECT DISTINCT <col>... ORDER BY <col> DESC.
      
      Also fix bug#59110: Memory leak of QUICK_SELECT_I allocated memory.
      
      Root cause of these bugs are that test_if_skip_sort_order() decided to
      revert the 'skip_sort_order' descision (and use filesort) after the
      query plan has been updated to reflect a 'skip' of the sort order.
      
      This might happen in 'check_reverse_order:' if we have a 
      select->quick which could not be made descending by appending 
      a QUICK_SELECT_DESC. ().
      
      The original 'save_quick' was then restored after the QEP has been modified, which caused:
      
       - An incorrect 'precomputed_group_by= TRUE' may have been set, and not reverted, as
         part of the already modifified QEP (Bug#59398)
       - A 'select->quick' might have been created which we fail to delete (bug#59110).
      
      This fix is a refactorication of test_if_skip_sort_order() where all logic
      related to modification of QEP (controlled by argument 'bool no_changes'), is
      moved to the end of test_if_skip_sort_order(), and done after *all* 'test_if_skip'
      checks has been performed - including the 'check_reverse_order:' checks.
      
      The refactorication above contains now intentional changes to the logic which has been
      moved to the end of the function.
      
      Furthermore, a smaller part of the fix address the handling of the select->quick objects 
      which may already exists when we call 'test_if_skip_sort_order()' (save_quick) - and
      new select->quick's created during test_if_skip_sort_order():
      
        - Before new select->quick may be created by calling ::test_quick_select(), we
          set 'select->quick= 0' to avoid that ::test_quick_select() prematurely
          delete the save_quick's. (After this call we may have both a 'save_quick' 
          and 'select->quick')
      
        - All returns from ::test_if_skip_sort_order() where we may have both a
          'save_quick' and a 'select->quick' has been changed to goto's to the
          exit points 'skiped_sort_order:' or 'need_filesort:' where we
          decide which of the QUICK_SELECT's to keep, and delete the other.
[2 Feb 2011 14:47] 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/130245
[2 Feb 2011 14:50] 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/130246

3295 Ole John Aske	2011-02-02
      Updated Fix for bug#59308: Incorrect result for SELECT DISTINCT 
                                  <col>... ORDER BY <col> DESC.
      
      Rebased to mysql-5.5 which has changed considerably in the areas
      affected by the original bugfix. (based on mysql-5.1)
      
      See http://lists.mysql.com/commits/130245 for original commit comments.
[2 Feb 2011 14:51] 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/130247

3592 Ole John Aske	2011-02-02
      Updated Fix for bug#59308: Incorrect result for SELECT DISTINCT 
                                  <col>... ORDER BY <col> DESC.
      
      Rebased to mysql-trunk which has changed considerably in the areas
      affected by the original bugfix. (based on mysql-5.1)
      
      See http://lists.mysql.com/commits/130245 for original commit comments.
[7 Feb 2011 9:36] 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/130529
[7 Feb 2011 9:41] 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/130531

3306 Ole John Aske	2011-02-07 [merge]
      Merge of fix for bug#59308 from mysql-5.1 -> mysql-5.5
[7 Feb 2011 9:47] 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/130532

3601 Ole John Aske	2011-02-07 [merge]
      Merge of fix for bug#59308 from mysql-5.5 -> mysql-trunk
[7 Feb 2011 9:48] Bugs System
Pushed into mysql-5.1 5.1.56 (revid:ole.john.aske@oracle.com-20110207093621-hyaa46qkdbrwbiom) (version source revid:ole.john.aske@oracle.com-20110207093621-hyaa46qkdbrwbiom) (merge vers: 5.1.56) (pib:24)
[7 Feb 2011 9:48] Bugs System
Pushed into mysql-trunk 5.6.2 (revid:ole.john.aske@oracle.com-20110207094653-lcejq75drwade4dd) (version source revid:ole.john.aske@oracle.com-20110207094653-lcejq75drwade4dd) (merge vers: 5.6.2) (pib:24)
[7 Feb 2011 9:49] Bugs System
Pushed into mysql-5.5 5.5.10 (revid:ole.john.aske@oracle.com-20110207094042-ix1ch1gq52ti51i9) (version source revid:ole.john.aske@oracle.com-20110207094042-ix1ch1gq52ti51i9) (merge vers: 5.5.10) (pib:24)
[28 Feb 2011 20:57] Paul Dubois
Noted in 5.1.56, 5.5.10, 5.6.2 changelogs.

A query of the following form returned an incorrect result, where the
values for col_name in the result set were entirely replaced with
NULL values: 

SELECT DISTINCT col_name ... ORDER BY col_name DESC;

CHANGESET - http://lists.mysql.com/commits/130532