| Bug #37830 | ORDER BY ASC/DESC - no difference | ||
|---|---|---|---|
| Submitted: | 3 Jul 2008 8:19 | Modified: | 12 Aug 2008 19:24 |
| Reporter: | Szymon Kosok | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.0.60, 5.0-bzr | OS: | Linux (Gentoo) |
| Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
| Tags: | DESC, innodb, order by | ||
[3 Jul 2008 8:19]
Szymon Kosok
[3 Jul 2008 10:47]
Sveta Smirnova
Thank you for the report. I can not repeat described behavior with test data. Please provide several rows bug is repeatable with. Results with my test data: CREATE TABLE `postac_blokada` ( `id` int(11) NOT NULL auto_increment, `postac_id` int(11) NOT NULL, `opis` varchar(255) collate utf8_polish_ci NOT NULL, `data` datetime NOT NULL, `status` tinyint(1) NOT NULL, PRIMARY KEY (`id`), KEY `postac_blokada_postac_id` (`postac_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci ; insert into postac_blokada (postac_id, opis, data, status) values(1, 't1', '2008-10-03 01:01:01', 0); insert into postac_blokada (postac_id, opis, data, status) values(2, 't2', '2008-10-03 01:01:02', 1); insert into postac_blokada (postac_id, opis, data, status) values(1, 't3', '2008-10-03 01:01:03', 1); insert into postac_blokada (postac_id, opis, data, status) values(2, 't4', '2008-10-03 01:01:04', 0); SELECT `postac_blokada`.`id`, `postac_blokada`.`postac_id`, `postac_blokada`.`opis`, `postac_blokada`.`data`, `postac_blokada`.`status` FROM `postac_blokada` WHERE `postac_blokada`.`postac_id` = 1 ORDER BY `postac_blokada`.`id` DESC LIMIT 5; id postac_id opis data status 3 1 t3 2008-10-03 01:01:03 1 1 1 t1 2008-10-03 01:01:01 0 SELECT `postac_blokada`.`id`, `postac_blokada`.`postac_id`, `postac_blokada`.`opis`, `postac_blokada`.`data`, `postac_blokada`.`status` FROM `postac_blokada` WHERE `postac_blokada`.`postac_id` = 1 ORDER BY `postac_blokada`.`id` ASC LIMIT 5; id postac_id opis data status 1 1 t1 2008-10-03 01:01:01 0 3 1 t3 2008-10-03 01:01:03 1
[3 Jul 2008 11:22]
Szymon Kosok
Ok.
Here's data:
INSERT INTO `postac_blokada` (`id`, `postac_id`, `opis`, `data`, `status`) VALUES
(1, 1, 'Lot do Mexico, Meksyk', '2008-07-02 01:57:34', 1),
(2, 1, 'Lot do Rio de Janeiro, Brazylia', '2008-07-02 11:03:07', 1),
(3, 1, 'Wakacje - Rio de Janeiro', '2008-07-02 13:07:55', 1),
(4, 1, 'Lot do Tokyo, Japonia', '2008-07-02 19:28:53', 1),
(5, 4, 'Lot do Villas, Stany Zjednoczone', '2008-07-02 20:14:35', 1),
(6, 3, 'Lot do Brno, Czechy', '2008-07-02 19:30:01', 1),
(7, 1, 'Wakacje - Tokyo', '2008-07-02 20:40:16', 1),
(8, 1, 'Lot do Bytom, Polska', '2008-07-03 11:43:39', 1);
And this is my output:
mysql> SELECT `postac_blokada`.`id`, `postac_blokada`.`postac_id`, `postac_blokada`.`opis`,
-> `postac_blokada`.`data`, `postac_blokada`.`status` FROM `postac_blokada` WHERE
-> `postac_blokada`.`postac_id` = 1 ORDER BY `postac_blokada`.`id` DESC LIMIT 5;
+----+-----------+---------------------------------+---------------------+--------+
| id | postac_id | opis | data | status |
+----+-----------+---------------------------------+---------------------+--------+
| 1 | 1 | Lot do Mexico, Meksyk | 2008-07-02 01:57:34 | 1 |
| 2 | 1 | Lot do Rio de Janeiro, Brazylia | 2008-07-02 11:03:07 | 1 |
| 3 | 1 | Wakacje - Rio de Janeiro | 2008-07-02 13:07:55 | 1 |
| 4 | 1 | Lot do Tokyo, Japonia | 2008-07-02 19:28:53 | 1 |
| 7 | 1 | Wakacje - Tokyo | 2008-07-02 20:40:16 | 1 |
+----+-----------+---------------------------------+---------------------+--------+
5 rows in set (0.00 sec)
[3 Jul 2008 11:46]
Sveta Smirnova
Thank you for the feedback. Verified as described. Bug exists since version 5.0.48. This is not same as bug #31001.
[11 Jul 2008 13:21]
Georgi Kodinov
Not repeatable with 5.0.66, 5.0 bzr and 5.1 bzr. Probably a duplicate of Bug#31001 (or at least fixed by the same fix).
[11 Jul 2008 13:35]
Sveta Smirnova
test case
Attachment: bug37830.test (application/octet-stream, text), 1.41 KiB.
[11 Jul 2008 13:47]
Georgi Kodinov
Sorry : wrong test file. Can repeat now with Sveta's test file
[15 Jul 2008 8: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/49739 2648 Georgi Kodinov 2008-07-15 Bug#37830 ORDER BY ASC/DESC - no difference Range scan in descending order for c <= <col> <= c type of ranges was ignoring the DESC flag. However some engines like InnoDB have the primary key parts as a suffix for every secondary key. When such primary key suffix is used for ordering ignoring the DESC is not valid. But we generally would like to do this because it's faster. Fixed by adding a special flag to QUICK_SELECT_DESC to respect reverse ordering and read the EQ_RANGE backwards.
[15 Jul 2008 9:56]
Georgi Kodinov
Bug #37964 marked as a duplicate of this bug.
[15 Jul 2008 16:59]
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/49778 2648 Georgi Kodinov 2008-07-15 Bug#37830 : ORDER BY ASC/DESC - no difference Range scan in descending order for c <= <col> <= c type of ranges was ignoring the DESC flag. However some engines like InnoDB have the primary key parts as a suffix for every secondary key. When such primary key suffix is used for ordering ignoring the DESC is not valid. But we generally would like to do this because it's faster. Fixed by performing only reverse scan if the primary key is used.
[16 Jul 2008 9:32]
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/49808 2648 Georgi Kodinov 2008-07-16 Bug#37830 : ORDER BY ASC/DESC - no difference Range scan in descending order for c <= <col> <= c type of ranges was ignoring the DESC flag. However some engines like InnoDB have the primary key parts as a suffix for every secondary key. When such primary key suffix is used for ordering ignoring the DESC is not valid. But we generally would like to do this because it's faster. Fixed by performing only reverse scan if the primary key is used. Removed some dead code in the process.
[17 Jul 2008 17: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/49959 2706 Georgi Kodinov 2008-07-17 [merge] merge of bug #37830 to 5.1
[17 Jul 2008 17:43]
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/49960 2706 Georgi Kodinov 2008-07-17 [merge] merge of bug #37830 to 5.1
[17 Jul 2008 17:57]
Georgi Kodinov
Pushed into the -bugteam trees
[22 Jul 2008 18:34]
Bugs System
Pushed into 5.1.28
[22 Jul 2008 18:34]
Bugs System
Pushed into 5.0.68
[23 Jul 2008 1:11]
Paul DuBois
Noted in 5.0.68, 5.1.28 changelogs. For InnoDB tables, ORDER BY ... DESC sometimes returned results in ascending order. Setting report to Patch queued pending push into 6.0.x.
[23 Jul 2008 11:25]
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/50298 2647 Georgi Kodinov 2008-07-23 Bug#37830 : ORDER BY ASC/DESC - no difference Range scan in descending order for c <= <col> <= c type of ranges was ignoring the DESC flag. However some engines like InnoDB have the primary key parts as a suffix for every secondary key. When such primary key suffix is used for ordering ignoring the DESC is not valid. But we generally would like to do this because it's faster. Fixed by performing only reverse scan if the primary key is used. Removed some dead code in the process.
[23 Jul 2008 11:27]
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/50299 2647 Georgi Kodinov 2008-07-23 Bug#37830 : ORDER BY ASC/DESC - no difference Range scan in descending order for c <= <col> <= c type of ranges was ignoring the DESC flag. However some engines like InnoDB have the primary key parts as a suffix for every secondary key. When such primary key suffix is used for ordering ignoring the DESC is not valid. But we generally would like to do this because it's faster. Fixed by performing only reverse scan if the primary key is used. Removed some dead code in the process.
[24 Jul 2008 14:21]
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/50416 2648 Davi Arnaut 2008-07-24 Cherry-pick Bug#33362 from mysql-5.1
[28 Jul 2008 14:46]
Bugs System
Pushed into 6.0.7-alpha (revid:alik@mysql.com-20080725172155-fnc73o50e4tgl23k) (version source revid:alik@mysql.com-20080725172155-fnc73o50e4tgl23k) (pib:3)
[28 Jul 2008 15:21]
Paul DuBois
Noted in 6.0.7 changelog.
[28 Jul 2008 15:29]
Georgi Kodinov
Note that the code is still not pushed in 5.0.
[28 Jul 2008 15:42]
Paul DuBois
Removing entry from 5.0.68 changelog, per Joro's last note.
[28 Jul 2008 16:45]
Bugs System
Pushed into 5.1.28 (revid:davi.arnaut@sun.com-20080722182431-0i2f1yc4uocime9q) (version source revid:davi.arnaut@sun.com-20080722182431-0i2f1yc4uocime9q) (pib:3)
[28 Jul 2008 16:54]
Paul DuBois
Resetting report to Need Doc Info.
[12 Aug 2008 14:54]
Bugs System
Pushed into 6.0.7-alpha (revid:kgeorge@mysql.com-20080723112500-7u7qh4a2oatlkxr4) (version source revid:davi.arnaut@sun.com-20080812141852-8e6knbqclpfd8irn) (pib:3)
[12 Aug 2008 15:17]
Bugs System
Pushed into 5.1.28 (revid:kgeorge@mysql.com-20080723112500-7u7qh4a2oatlkxr4) (version source revid:davi.arnaut@sun.com-20080812142843-he05ncsggstbn57z) (pib:3)
[12 Aug 2008 17:42]
Paul DuBois
Setting report to Patch Queued pending push of fix into 5.0.x.
[12 Aug 2008 18:52]
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/51449 2647 Davi Arnaut 2008-08-12 [merge] Merge mysql-5.0-bugteam into mysql-5.0
[12 Aug 2008 19:14]
Bugs System
Pushed into 5.0.68 (revid:kgeorge@mysql.com-20080723112500-7u7qh4a2oatlkxr4) (version source revid:davi.arnaut@sun.com-20080812185100-d47qb8mz2ye6pe6b) (pib:3)
[12 Aug 2008 19:24]
Paul DuBois
Noted in 5.0.68 changelog.
[28 Aug 2008 20:16]
Bugs System
Pushed into 6.0.7-alpha (revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (version source revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (pib:3)
[13 Sep 2008 21:14]
Bugs System
Pushed into 6.0.7-alpha (revid:kgeorge@mysql.com-20080723112500-7u7qh4a2oatlkxr4) (version source revid:hakan@mysql.com-20080725175322-8wgujj5xuzrjz3ke) (pib:3)
[14 Feb 2011 21:57]
Valentin Gjorgjioski
It seems it was fixed in 5.1.28 : However, I'm noticing the same problem in my version: 5.1.41-3ubuntu12.8 Additionaly, select * from table where other_column=25 order by id desc limit N; works when 13611<=N<=145, and doesn't work when 145<N<13611 Possible index corruption?! select count(*) from table returns 15419 The table is InnoDB. This seems related to this bug as well: http://stackoverflow.com/questions/2844699/mysql-order-by-and-limit-gives-wrong-result/499...
[15 Feb 2011 14:52]
Valentin Gjorgjioski
Test file. Maybe this is not exactly this bug - but it seems connected to this.
Attachment: bug-37830.txt (text/plain), 471.94 KiB.
