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:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0.60, 5.0-bzr OS:Linux (Gentoo)
Assigned to: Georgi Kodinov
Tags: DESC, innodb, order by
Triage: D2 (Serious)

[3 Jul 2008 8:19] Szymon Kosok
Description:
There is probably again same bug as here http://bugs.mysql.com/bug.php?id=31001. I'm using 5.0.60 version and such query:

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

order results ascending. Schema of table is:

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 ;

How to repeat:
Create table:

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 ;

Add some records (with at least two different "postac_id"), run query

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
[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.