| 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: | |
| 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 | ||
[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
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)