MySQL Bugs: #33483: After partition a INNODB table, order desc by primary key is broken

Bug #33483	After partition a INNODB table, order desc by primary key is broken
Submitted:	22 Dec 2007 18:41	Modified:	3 Jan 2008 18:02
Reporter:	Nicolae Namolovan	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S1 (Critical)
Version:	5.1.22	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I have a innodb (innodb_file_per_table) table such as

CREATE TABLE `table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `owner` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (88134) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (165517) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

After upgrading to 5.1.22, sorting by id is wrong

mysql> SELECT id FROM table WHERE owner = 43497 ORDER BY id DESC;   
+--------+
| id     |
+--------+
| 164946 |
| 163707 |
| 161182 |
| 157286 |
| 157277 |
| 156425 |
| 156023 |
| 156019 |
| 156011 |
| 155262 |
| 154640 |
| 154583 |
| 154243 |
| 154219 |
| 154024 |
| 153830 |
| 153811 |
| 153809 |
| 153400 |
| 153393 |
| 153389 |
| 153367 |
| 153352 |
| 153128 |
| 153126 |
| 153110 |
| 152794 |
| 152752 |
| 152727 |
| 151998 |
| 151697 |
| 150929 |
| 149187 |
| 149152 |
| 147871 |
| 147697 |
| 147676 |
| 147396 |
| 147089 |
| 145102 |
| 145012 |
| 144824 |
| 144675 |
| 144357 |
| 144113 |
| 144037 |
| 143725 |
| 143466 |
| 143223 |
| 143102 |
| 142881 |
| 140896 |
| 140522 |
| 135055 |
| 134347 |
| 179852 | <- p2 ?
| 179847 |
| 179844 |
| 179051 |
| 179046 |
| 170697 |
| 170696 |
| 170695 |
| 169575 |
| 168945 |
| 168126 |
| 167737 |
| 165687 |
+--------+

Look like it first take values and sort from p0,p1, and after take values from p2 and sort.. Wired
Temporarily I sort by date (it's not primary and not partitioned by it)

How to repeat:
Hope this way..

CREATE TABLE `table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `owner` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (88134) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (165517) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Insert into the table, some for p0 (id LESS THAN 88134), some for p1 (id LESS THAN 165517), some for p2 (id bigger than 165517).

And the try to sort DESC by id (ASC seem to work fine).

Nobody seem to be interested in this ? ;o)

There seem to be problems between innodb and mysql..

Many thanks for writing a bug report.

Unfortunately, I can't repeat this behaviour.

mysql>CREATE TABLE bla (   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,   `owner` int(11) NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (88134) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (165517) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

mysql>insert into bla(id,owner) values(164946,12), (163707,12), (161182,12), (157286,12), (157277,12), (156425,12), (156023,12), (156019,12), (156011,12), (155262,12), (154640,12), (154583,12), (154243,12), (154219,12), (154024,12), (153830,12), (153811,12), (153809,12), (153400,12), (153393,12), (153389,12), (153367,12), (153352,12), (153128,12), (153126,12), (153110,12), (152794,12), (152752,12), (152727,12), (151998,12), (151697,12), (150929,12), (149187,12),
(149152,12), (147871,12), (147697,12), (147676,12), (147396,12), (147089,12), (145102,12), (145012,12), (144824,12), (144675,12), (144357,12), (144113,12), (144037,12), (143725,12), (143466,12), (143223,12), (143102,12), (142881,12), (140896,12), (140522,12), (135055,12), (134347,12), (179852,12), (179847,12),
(179844,12), (179051,12), (179046,12), (170697,12), (170696,12), (170695,12),
(169575,12), (168945,12), (168126,12), (167737,12), (165687,12);

mysql> select id from bla where owner=12 order by id desc;
+--------+
| id     |
+--------+
| 179852 | 
| 179847 | 
| 179844 | 
| 179051 | 
| 179046 | 
| 170697 | 
| 170696 | 
| 170695 | 
| 169575 | 
| 168945 | 
| 168126 | 
| 167737 | 
| 165687 | 
| 164946 | 
| 163707 | 
| 161182 | 
| 157286 | 
| 157277 | 
| 156425 | 
| 156023 | 
| 156019 | 
| 156011 | 
| 155262 | 
| 154640 | 
| 154583 | 
| 154243 | 
| 154219 | 
| 154024 | 
| 153830 | 
| 153811 | 
| 153809 | 
| 153400 | 
| 153393 | 
| 153389 | 
| 153367 | 
| 153352 | 
| 153128 | 
| 153126 | 
| 153110 | 
| 152794 | 
| 152752 | 
| 152727 | 
| 151998 | 
| 151697 | 
| 150929 | 
| 149187 | 
| 149152 | 
| 147871 | 
| 147697 | 
| 147676 | 
| 147396 | 
| 147089 | 
| 145102 | 
| 145012 | 
| 144824 | 
| 144675 | 
| 144357 | 
| 144113 | 
| 144037 | 
| 143725 | 
| 143466 | 
| 143223 | 
| 143102 | 
| 142881 | 
| 140896 | 
| 140522 | 
| 135055 | 
| 134347 | 
+--------+

Please, let us know the platform, that you use.