MySQL Bugs: #37964: Wrong result using ORDER BY + LIMIT

Bug #37964	Wrong result using ORDER BY + LIMIT
Submitted:	8 Jul 2008 11:17	Modified:	15 Jul 2008 9:56
Reporter:	vsivak vsivak	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.1/5.0/6.0	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	ORDER BY DESC, regression

Description:
I´m using folowing SQL statement:
SELECT * FROM videos WHERE video_categoriesid='1' ORDER BY id DESC LIMIT 2, 1
and i get result from the array which is sorted ascending (BUT I WANT RESULT FROM DESC SORTED ARRAY)

Create new database (for example test2) use this 2 tables and try the SQL command SELECT * FROM videos WHERE video_categoriesid='1' ORDER BY id DESC LIMIT 2, 1 - the result is video with id 7, but should be 10.

CREATE TABLE `video_categories` (
  `id` int(11) NOT NULL auto_increment,
  `uid` varchar(25) NOT NULL,
  `parent_video_category` int(11) NOT NULL default '0',
  `sort_order` int(11) NOT NULL default '0',
  `category_name` varchar(50) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

INSERT INTO `video_categories` VALUES (1, '8732122339511030420583500', 0, 0, 'STAVBY PRO BYDLENÍ');
INSERT INTO `video_categories` VALUES (3, '4520520536171097446293607', 0, 0, 'VEŘEJNÉ STAVBY');

CREATE TABLE `videos` (
  `id` int(11) NOT NULL auto_increment,
  `uid` varchar(25) NOT NULL,
  `title` varchar(100) NOT NULL,
  `keywords` text,
  `description` text,
  `total_length` int(11) NOT NULL COMMENT 'delka celeho nerozestrihaneho videa',
  `videoscount` int(11) NOT NULL default '0' COMMENT 'pocet strihu videa',
  `resolution_w` int(11) NOT NULL default '320' COMMENT 'rozliseni sirka',
  `resolution_h` int(11) NOT NULL default '240' COMMENT 'rozliseni vyska',
  `time_added` datetime NOT NULL,
  `images_last_rotation` datetime NOT NULL,
  `images_rotation_timeout` int(11) NOT NULL default '3600',
  `sort_order` int(11) NOT NULL default '0',
  `selected` tinyint(1) NOT NULL default '0',
  `enabled` tinyint(1) NOT NULL default '0',
  `owner` varchar(25) NOT NULL COMMENT 'GUID vlastnika videa',
  `video_categoriesid` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `uid` (`uid`),
  KEY `FKvideos758264` (`video_categoriesid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=14 ;

INSERT INTO `videos` VALUES (4, '8462518443463578635266479', 'video 4', '', '', 15, 1, 320, 240, '2008-04-22 09:09:54', '2008-07-01 00:00:01', 3600, 0, 0, 1, '', 3);
INSERT INTO `videos` VALUES (5, '3227904390799188569201015', 'video 1', '', '', 18, 1, 320, 240, '2008-04-07 21:44:20', '2008-07-01 00:00:01', 3600, 0, 0, 1, '', 1);
INSERT INTO `videos` VALUES (6, '6908280227385544247803464', 'video 2', '', '', 15, 1, 320, 240, '2008-04-07 21:44:24', '2008-07-01 00:00:01', 3600, 0, 0, 1, '', 1);
INSERT INTO `videos` VALUES (7, '3566373516639909720539786', 'video 3', '', '', 15, 1, 320, 240, '2008-04-07 21:44:27', '2008-07-01 00:00:01', 3600, 0, 0, 1, '', 1);
INSERT INTO `videos` VALUES (9, '0731272937526921287482144', 'video 5', '', '', 36, 1, 320, 240, '2008-04-07 21:44:32', '2008-07-01 00:00:01', 3600, 0, 0, 1, '', 1);
INSERT INTO `videos` VALUES (10, '9798971171221584216471877', 'video 6', '', '', 15, 1, 320, 240, '2008-04-07 21:44:34', '2008-07-01 00:00:01', 3600, 0, 0, 1, '', 1);
INSERT INTO `videos` VALUES (11, '2227440085377856907764403', 'video 7', '', '', 15, 1, 320, 240, '2008-04-07 21:47:22', '2008-07-01 00:00:01', 3600, 0, 0, 1, '', 1);
INSERT INTO `videos` VALUES (12, '6202543484191359810966338', 'opravdu pekne / really very nice', 'Rodinný dům, Ostrava.', 'Další ze sady rodinných domků ze všech stran.', 15, 1, 320, 240, '2008-06-23 00:24:15', '2008-07-01 00:00:01', 3600, 0, 0, 1, '', 1);

ALTER TABLE `videos`
  ADD CONSTRAINT `FKvideos758264` FOREIGN KEY (`video_categoriesid`) REFERENCES `video_categories` (`id`);

How to repeat:
Create new database (for example test2) use this 2 tables and try the SQL command SELECT * FROM videos WHERE video_categoriesid='1' ORDER BY id DESC LIMIT 2, 1 - the result is video with id 7, but should be 10.

CREATE TABLE `video_categories` (
  `id` int(11) NOT NULL auto_increment,
  `uid` varchar(25) NOT NULL,
  `parent_video_category` int(11) NOT NULL default '0',
  `sort_order` int(11) NOT NULL default '0',
  `category_name` varchar(50) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

INSERT INTO `video_categories` VALUES (1, '8732122339511030420583500', 0, 0, 'STAVBY PRO BYDLENÍ');
INSERT INTO `video_categories` VALUES (3, '4520520536171097446293607', 0, 0, 'VEŘEJNÉ STAVBY');

CREATE TABLE `videos` (
  `id` int(11) NOT NULL auto_increment,
  `uid` varchar(25) NOT NULL,
  `title` varchar(100) NOT NULL,
  `keywords` text,
  `description` text,
  `total_length` int(11) NOT NULL COMMENT 'delka celeho nerozestrihaneho videa',
  `videoscount` int(11) NOT NULL default '0' COMMENT 'pocet strihu videa',
  `resolution_w` int(11) NOT NULL default '320' COMMENT 'rozliseni sirka',
  `resolution_h` int(11) NOT NULL default '240' COMMENT 'rozliseni vyska',
  `time_added` datetime NOT NULL,
  `images_last_rotation` datetime NOT NULL,
  `images_rotation_timeout` int(11) NOT NULL default '3600',
  `sort_order` int(11) NOT NULL default '0',
  `selected` tinyint(1) NOT NULL default '0',
  `enabled` tinyint(1) NOT NULL default '0',
  `owner` varchar(25) NOT NULL COMMENT 'GUID vlastnika videa',
  `video_categoriesid` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `uid` (`uid`),
  KEY `FKvideos758264` (`video_categoriesid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=14 ;

INSERT INTO `videos` VALUES (4, '8462518443463578635266479', 'video 4', '', '', 15, 1, 320, 240, '2008-04-22 09:09:54', '2008-07-01 00:00:01', 3600, 0, 0, 1, '', 3);
INSERT INTO `videos` VALUES (5, '3227904390799188569201015', 'video 1', '', '', 18, 1, 320, 240, '2008-04-07 21:44:20', '2008-07-01 00:00:01', 3600, 0, 0, 1, '', 1);
INSERT INTO `videos` VALUES (6, '6908280227385544247803464', 'video 2', '', '', 15, 1, 320, 240, '2008-04-07 21:44:24', '2008-07-01 00:00:01', 3600, 0, 0, 1, '', 1);
INSERT INTO `videos` VALUES (7, '3566373516639909720539786', 'video 3', '', '', 15, 1, 320, 240, '2008-04-07 21:44:27', '2008-07-01 00:00:01', 3600, 0, 0, 1, '', 1);
INSERT INTO `videos` VALUES (9, '0731272937526921287482144', 'video 5', '', '', 36, 1, 320, 240, '2008-04-07 21:44:32', '2008-07-01 00:00:01', 3600, 0, 0, 1, '', 1);
INSERT INTO `videos` VALUES (10, '9798971171221584216471877', 'video 6', '', '', 15, 1, 320, 240, '2008-04-07 21:44:34', '2008-07-01 00:00:01', 3600, 0, 0, 1, '', 1);
INSERT INTO `videos` VALUES (11, '2227440085377856907764403', 'video 7', '', '', 15, 1, 320, 240, '2008-04-07 21:47:22', '2008-07-01 00:00:01', 3600, 0, 0, 1, '', 1);
INSERT INTO `videos` VALUES (12, '6202543484191359810966338', 'opravdu pekne / really very nice', 'Rodinný dům, Ostrava.', 'Další ze sady rodinných domků ze všech stran.', 15, 1, 320, 240, '2008-06-23 00:24:15', '2008-07-01 00:00:01', 3600, 0, 0, 1, '', 1);

ALTER TABLE `videos`
  ADD CONSTRAINT `FKvideos758264` FOREIGN KEY (`video_categoriesid`) REFERENCES `video_categories` (`id`);

Thank you for the bug report. See Using Filesort on 5.0.41 and the lack of it on latest source.

C:\temp\mysql-5.0.41-win32>bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.41-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM videos WHERE video_categoriesid=1 ORDER BY id DESC LIMIT 2, 1\G
*************************** 1. row ***************************
                     id: 10
                    uid: 9798971171221584216471877
                  title: video 6
               keywords:
            description:
           total_length: 15
            videoscount: 1
           resolution_w: 320
           resolution_h: 240
             time_added: 2008-04-07 21:44:34
   images_last_rotation: 2008-07-01 00:00:01
images_rotation_timeout: 3600
             sort_order: 0
               selected: 0
                enabled: 1
                  owner:
     video_categoriesid: 1
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM videos WHERE video_categoriesid=1 ORDER BY id DESC LIMIT 2, 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: videos
         type: range
possible_keys: FKvideos758264
          key: FKvideos758264
      key_len: 4
          ref: NULL
         rows: 4
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

C:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.64-nt-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.0 > use test2
Database changed
mysql 5.0 > SELECT * FROM videos WHERE video_categoriesid=1 ORDER BY id DESC LIMIT 2, 1\G
*************************** 1. row ***************************
                     id: 7
                    uid: 3566373516639909720539786
                  title: video 3
               keywords:
            description:
           total_length: 15
            videoscount: 1
           resolution_w: 320
           resolution_h: 240
             time_added: 2008-04-07 21:44:27
   images_last_rotation: 2008-07-01 00:00:01
images_rotation_timeout: 3600
             sort_order: 0
               selected: 0
                enabled: 1
                  owner:
     video_categoriesid: 1
1 row in set (0.00 sec)

mysql 5.0 > EXPLAIN SELECT * FROM videos WHERE video_categoriesid=1 ORDER BY id DESC LIMIT 2, 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: videos
         type: range
possible_keys: FKvideos758264
          key: FKvideos758264
      key_len: 4
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)

mysql 5.0 >

Duplicate of bug #37830