| Bug #9904 | order desc on simple select returns zero rows! | ||
|---|---|---|---|
| Submitted: | 14 Apr 2005 13:56 | Modified: | 21 Apr 2005 7:02 |
| Reporter: | Sergey Frolovichev | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S1 (Critical) |
| Version: | 4.1.11 binary | OS: | Linux (SuSe 9.1, 9.2, Kernel 2.4, 2.6) |
| Assigned to: | Bugs System | CPU Architecture: | Any |
[14 Apr 2005 13:56]
Sergey Frolovichev
[14 Apr 2005 16:39]
Heikki Tuuri
Hi! Please print EXPLAIN SELECT ... for both queries. Please show a mysql client session run that demonstrates the error. Regards, Heikki
[14 Apr 2005 16:53]
Sergey Frolovichev
mysql> use Monamour2;
Database changed
mysql> select * from Photo as p where p.rating_id=11574 and
-> p.moderated='Approved' and p.status='Active' and p.system_status='Active' and
-> p.rating_moderated='Approved' and p.top_status!='Blocked' order by p.created
-> desc limit 1;
Empty set (0.00 sec)
mysql> select * from Photo as p where p.rating_id=11574 and
-> p.moderated='Approved' and p.status='Active' and p.system_status='Active' and
-> p.rating_moderated='Approved' and p.top_status!='Blocked' order by p.created
-> limit 1;
+-------+-----------+-------------+------------+---------+-----------+-------+--------+--------+---------+-------+-----------+-------+----------+----------+----------+----------+------------+---------------+---------------+---------------+---------------+--------+-----------+-----------+-----------+-----------+--------------+-----------------+-----------------+-----------------+-----------------+------+-------------+-------------------+----------------------+----------------------+----------------------+----------------------+------------+---------------+---------------+---------------+---------------+---------------------+---------------------+-----------+---------------------+--------------+-----------------+-----------+------------------+---------------------+---------------------+------------------------+----------+---------------+------------+--------+---------------+
| oid | folder_id | language_id | partner_id | user_id | anketa_id | width | height | rwidth | rheight | bytes | extension | small | small_x1 | small_y1 | small_x2 | small_y2 | small_face | small_face_x1 | small_face_y1 | small_face_x2 | small_face_y2 | medium | medium_x1 | medium_y1 | medium_x2 | medium_y2 | medium_adult | medium_adult_x1 | medium_adult_y1 | medium_adult_x2 | medium_adult_y2 | huge | huge_rating | huge_rating_adult | huge_rating_adult_x1 | huge_rating_adult_y1 | huge_rating_adult_x2 | huge_rating_adult_y2 | huge_adult | huge_adult_x1 | huge_adult_y1 | huge_adult_x2 | huge_adult_y2 | updated | created | moderated | moderated_ts | moderator_id | moderated_descr | rating_id | rating_moderated | rating_moderated_ts | rating_moderator_id | rating_moderated_descr | on_first | photo_checked | top_status | status | system_status |
+-------+-----------+-------------+------------+---------+-----------+-------+--------+--------+---------+-------+-----------+-------+----------+----------+----------+----------+------------+---------------+---------------+---------------+---------------+--------+-----------+-----------+-----------+-----------+--------------+-----------------+-----------------+-----------------+-----------------+------+-------------+-------------------+----------------------+----------------------+----------------------+----------------------+------------+---------------+---------------+---------------+---------------+---------------------+---------------------+-----------+---------------------+--------------+-----------------+-----------+------------------+---------------------+---------------------+------------------------+----------+---------------+------------+--------+---------------+
| 11795 | 0 | 2 | 3 | 11793 | 11794 | 500 | 327 | 415 | 271 | 23655 | jpg | Yes | 191 | 0 | 431 | 327 | Yes | 283 | 123 | 344 | 205 | Yes | 191 | 0 | 431 | 327 | No | 130 | 0 | 370 | 327 | Yes | No | No | 0 | 0 | 0 | 0 | No | 0 | 0 | 167 | 109 | 2004-05-07 19:56:14 | 2004-01-13 17:28:00 | Approved | 2004-01-13 17:28:00 | 0 | | 11574 | Approved | 2004-01-13 17:28:00 | 0 | XML:import | No | Yes | Active | Active | Active |
+-------+-----------+-------------+------------+---------+-----------+-------+--------+--------+---------+-------+-----------+-------+----------+----------+----------+----------+------------+---------------+---------------+---------------+---------------+--------+-----------+-----------+-----------+-----------+--------------+-----------------+-----------------+-----------------+-----------------+------+-------------+-------------------+----------------------+----------------------+----------------------+----------------------+------------+---------------+---------------+---------------+---------------+---------------------+---------------------+-----------+---------------------+--------------+-----------------+-----------+------------------+---------------------+---------------------+------------------------+----------+---------------+------------+--------+---------------+
1 row in set (0.02 sec)
-------------------------------------------------------------------
mysql> explain select * from Photo as p where p.rating_id=11574 and
-> p.moderated='Approved' and p.status='Active' and p.system_status='Active' and
-> p.rating_moderated='Approved' and p.top_status!='Blocked' order by p.created
-> desc limit 1;
+----+-------------+-------+------+---------------+-----------+---------+-------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-----------+---------+-------------+---------+-------------+
| 1 | SIMPLE | p | ref | rating_id | rating_id | 5 | const,const | 1873504 | Using where |
+----+-------------+-------+------+---------------+-----------+---------+-------------+---------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from Photo as p where p.rating_id=11574 and
-> p.moderated='Approved' and p.status='Active' and p.system_status='Active' and
-> p.rating_moderated='Approved' and p.top_status!='Blocked' order by p.created
-> limit 1;
+----+-------------+-------+------+---------------+-----------+---------+-------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-----------+---------+-------------+---------+-------------+
| 1 | SIMPLE | p | ref | rating_id | rating_id | 5 | const,const | 1874426 | Using where |
+----+-------------+-------+------+---------------+-----------+---------+-------------+---------+-------------+
1 row in set (0.00 sec)
[14 Apr 2005 19:25]
Heikki Tuuri
Sergey, Jan, thank you. Looks like a bug in either MySQL's query optimizer or in InnoDB's search mechanism. I am assigning this bug report to Jan Lindström. Please download the data, and find out what goes wrong in row_search_for_mysql(). Regards, Heikki
[15 Apr 2005 2:22]
Jorge del Conde
Hi! I was unable to uncompress the file you uploaded. I got the following error msg: gunzip: Photo.sql.gz: unexpected end of file can you please reupload it ? Thanks
[15 Apr 2005 10:12]
Sergey Frolovichev
Here comes tared and gzipped version, previos was working fine because dump was to this file directly :-( http://194.67.27.123/Photo.tgz
[19 Apr 2005 5:24]
Jan Lindström
Thank you for your bug report. I was able to repeat this problem with a given database and SQL-query.
[19 Apr 2005 7:14]
Jan Lindström
I created similar table Photo2 with table handler MyISAM i.e.
| Photo2 |CREATE TABLE `Photo2` (
`oid` int(10) unsigned NOT NULL default '0',
`folder_id` int(10) unsigned NOT NULL default '0',
`language_id` int(10) unsigned NOT NULL default '0',
`partner_id` int(10) unsigned NOT NULL default '0',
`user_id` int(10) unsigned NOT NULL default '0',
`anketa_id` int(10) unsigned NOT NULL default '0',
`width` int(10) unsigned NOT NULL default '0',
`height` int(10) unsigned NOT NULL default '0',
`rwidth` int(11) NOT NULL default '0',
`rheight` int(11) NOT NULL default '0',
`bytes` int(10) unsigned NOT NULL default '0',
`extension` varchar(10) collate utf8_unicode_ci NOT NULL default '',
`small` enum('Yes','No') collate utf8_unicode_ci NOT NULL default 'No',
`small_x1` int(10) unsigned NOT NULL default '0',
`small_y1` int(10) unsigned NOT NULL default '0',
`small_x2` int(10) unsigned NOT NULL default '0',
`small_y2` int(10) unsigned NOT NULL default '0',
`small_face` enum('Yes','No') collate utf8_unicode_ci NOT NULL default 'No',
`small_face_x1` int(11) NOT NULL default '0',
`small_face_y1` int(11) NOT NULL default '0',
`small_face_x2` int(11) NOT NULL default '0',
`small_face_y2` int(11) NOT NULL default '0',
`medium` enum('Yes','No') collate utf8_unicode_ci NOT NULL default 'No',
`medium_x1` int(10) unsigned NOT NULL default '0',
`medium_y1` int(10) unsigned NOT NULL default '0',
`medium_x2` int(10) unsigned NOT NULL default '0',
`medium_y2` int(10) unsigned NOT NULL default '0',
`medium_adult` enum('Yes','No') collate utf8_unicode_ci NOT NULL default 'No',
`medium_adult_x1` int(11) NOT NULL default '0',
`medium_adult_y1` int(11) NOT NULL default '0',
`medium_adult_x2` int(11) NOT NULL default '0',
`medium_adult_y2` int(11) NOT NULL default '0',
`huge` enum('Yes','No') collate utf8_unicode_ci NOT NULL default 'No',
`huge_rating` enum('Yes','No') collate utf8_unicode_ci NOT NULL default 'No',
`huge_rating_adult` enum('Yes','No') collate utf8_unicode_ci NOT NULL default 'No',
`huge_rating_adult_x1` int(10) unsigned NOT NULL default '0',
`huge_rating_adult_y1` int(10) unsigned NOT NULL default '0',
`huge_rating_adult_x2` int(10) unsigned NOT NULL default '0',
`huge_rating_adult_y2` int(10) unsigned NOT NULL default '0',
`huge_adult` enum('Yes','No') collate utf8_unicode_ci NOT NULL default 'No',
`huge_adult_x1` int(11) NOT NULL default '0',
`huge_adult_y1` int(11) NOT NULL default '0',
`huge_adult_x2` int(11) NOT NULL default '0',
`huge_adult_y2` int(11) NOT NULL default '0',
`updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`created` timestamp NOT NULL default '0000-00-00 00:00:00',
`moderated` enum('New','Updated','Rejected','Approved','AfterRejected') collate utf8_unicode_ci NOT NULL default 'New',
`moderated_ts` timestamp NOT NULL default '0000-00-00 00:00:00',
`moderator_id` int(10) unsigned NOT NULL default '0',
`moderated_descr` varchar(255) collate utf8_unicode_ci NOT NULL default '',
`rating_id` int(11) NOT NULL default '0',
`rating_moderated` enum('None','New','Updated','Rejected','Approved','AfterRejected') collate utf8_unicode_ci NOT NULL default 'None',
`rating_moderated_ts` timestamp NOT NULL default '0000-00-00 00:00:00',
`rating_moderator_id` int(10) unsigned NOT NULL default '0',
`rating_moderated_descr` varchar(255) collate utf8_unicode_ci NOT NULL default '',
`on_first` enum('Yes','No') collate utf8_unicode_ci NOT NULL default 'No',
`photo_checked` enum('New','Yes','No') collate utf8_unicode_ci NOT NULL default 'New',
`top_status` enum('Active','Blocked') collate utf8_unicode_ci NOT NULL default 'Active',
`status` enum('Active','Blocked','Deleted') collate utf8_unicode_ci NOT NULLdefault 'Active',
`system_status` enum('Active','Blocked','Deleted') collate utf8_unicode_ci NOT NULL default 'Active',
PRIMARY KEY (`oid`),
KEY `anketa_id` (`anketa_id`),
KEY `user_id` (`user_id`),
KEY `rating_id` (`rating_id`,`rating_moderated`,`created`),
KEY `photo_checked` (`photo_checked`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
Then I copied all rows from Photo to Photo2 (insert into Photo2 select * from Photo;). And tested given query.
mysql> select * from Photo2 as p where p.rating_id=11574 and p.moderated='Approved' and p.status='Active' and p.system_status='Active' and p.rating_moderated='Approved' and p.top_status!='Blocked' order by p.created desc limit 1;
+----------+-----------+-------------+------------+----------+-----------+-------+--------+--------+---------+-------+-----------+-------+----------+----------+----------+----------+------------+---------------+---------------+---------------+---------------+--------+-----------+-----------+-----------+-----------+--------------+-----------------+-----------------+-----------------+-----------------+------+-------------+-------------------+----------------------+----------------------+----------------------+----------------------+------------+---------------+---------------+---------------+---------------+---------------------+---------------------+-----------+---------------------+--------------+-----------------+-----------+------------------+---------------------+---------------------+------------------------+----------+---------------+------------+--------+---------------+
| oid | folder_id | language_id | partner_id | user_id | anketa_id | width | height | rwidth | rheight | bytes | extension | small | small_x1 | small_y1 | small_x2 | small_y2 | small_face | small_face_x1 | small_face_y1 | small_face_x2 | small_face_y2 | medium | medium_x1 | medium_y1 | medium_x2 | medium_y2 | medium_adult | medium_adult_x1 | medium_adult_y1 | medium_adult_x2 | medium_adult_y2 | huge | huge_rating | huge_rating_adult | huge_rating_adult_x1 | huge_rating_adult_y1 | huge_rating_adult_x2 | huge_rating_adult_y2 | huge_adult | huge_adult_x1 | huge_adult_y1 | huge_adult_x2 | huge_adult_y2 | updated | created | moderated | moderated_ts | moderator_id | moderated_descr | rating_id | rating_moderated | rating_moderated_ts | rating_moderator_id | rating_moderated_descr | on_first | photo_checked | top_status | status | system_status |
+----------+-----------+-------------+------------+----------+-----------+-------+--------+--------+---------+-------+-----------+-------+----------+----------+----------+----------+------------+---------------+---------------+---------------+---------------+--------+-----------+-----------+-----------+-----------+--------------+-----------------+-----------------+-----------------+-----------------+------+-------------+-------------------+----------------------+----------------------+----------------------+----------------------+------------+---------------+---------------+---------------+---------------+---------------------+---------------------+-----------+---------------------+--------------+-----------------+-----------+------------------+---------------------+---------------------+------------------------+----------+---------------+------------+--------+---------------+
| 19107455 | 0 | 2 | 475306 | 19106889 | 19106890 | 600 | 415 | 415 | 287 | 40097 | jpg | Yes | 68 | 0 | 358 | 396 | Yes | 160 | 0 | 331 | 230 | Yes | 68 | 0 | 358 | 396 | No | 0 | 0 | 600 | 415 | Yes | No | No | 0 | 0 | 0 | 0 | No | 0 | 0 | 600 | 415 | 2005-04-13 23:51:00 | 2005-04-13 22:11:22 | Approved | 2005-04-13 23:51:00 | 551 | | 11574 | Approved | 2005-04-13 23:51:00 | 551 | | No | New | Active | Active | Active |
+----------+-----------+-------------+------------+----------+-----------+-------+--------+--------+---------+-------+-----------+-------+----------+----------+----------+----------+------------+---------------+---------------+---------------+---------------+--------+-----------+-----------+-----------+-----------+--------------+-----------------+-----------------+-----------------+-----------------+------+-------------+-------------------+----------------------+----------------------+----------------------+----------------------+------------+---------------+---------------+---------------+---------------+---------------------+---------------------+-----------+---------------------+--------------+-----------------+-----------+------------------+---------------------+---------------------+------------------------+----------+---------------+------------+--------+---------------+
1 row in set (0.12 sec)
mysql> explain select * from Photo2 as p where p.rating_id=11574 and p.moderated='Approved' and p.status='Active' and p.system_status='Active' and p.rating_moderated='Approved' and p.top_status!='Blocked' order by p.created desc limit 1;
+----+-------------+-------+------+---------------+-----------+---------+-------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-----------+---------+-------------+--------+-------------+
| 1 | SIMPLE | p | ref | rating_id | rating_id | 5 | const,const | 311285 | Using where |
+----+-------------+-------+------+---------------+-----------+---------+-------------+--------+-------------+
1 row in set (0.47 sec)
mysql> explain select * from Photo as p where p.rating_id=11574 and p.moderated='Approved' and p.status='Active' and p.system_status='Active' and p.rating_moderated='Approved' and p.top_status!='Blocked' order by p.created desc limit1;
+----+-------------+-------+------+---------------+-----------+---------+-------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-----------+---------+-------------+--------+-------------+
| 1 | SIMPLE | p | ref | rating_id | rating_id | 5 | const,const | 411722 | Using where |
+----+-------------+-------+------+---------------+-----------+---------+-------------+--------+-------------+
1 row in set (0.08 sec)
mysql> select * from Photo as p where p.rating_id=11574 and p.moderated='Approved' and p.status='Active' and p.system_status='Active' and p.rating_moderated='Approved' and p.top_status!='Blocked' order by p.created desc limit 1;
Empty set (0.01 sec)
mysql> select * from Photo as p where p.rating_id=11574 and p.moderated='Approved' and p.status='Active' and p.system_status='Active' and p.rating_moderated='Approved' and p.top_status!='Blocked' order by p.created desc;
Empty set (0.00 sec)
mysql> select * from Photo as p where p.rating_id=11574 and p.moderated='Approved' and p.status='Active' and p.system_status='Active' and p.rating_moderated='Approved' and p.top_status!='Blocked' order by p.created asc limit 1;
+-------+-----------+-------------+------------+---------+-----------+-------+--------+--------+---------+-------+-----------+-------+----------+----------+----------+----------+------------+---------------+---------------+---------------+---------------+--------+-----------+-----------+-----------+-----------+--------------+-----------------+-----------------+-----------------+-----------------+------+-------------+-------------------+----------------------+----------------------+----------------------+----------------------+------------+---------------+---------------+---------------+---------------+---------------------+---------------------+-----------+---------------------+--------------+-----------------+-----------+------------------+---------------------+---------------------+------------------------+----------+---------------+------------+--------+---------------+
| oid | folder_id | language_id | partner_id | user_id | anketa_id | width |height | rwidth | rheight | bytes | extension | small | small_x1 | small_y1 | small_x2 | small_y2 | small_face | small_face_x1 | small_face_y1 | small_face_x2 | small_face_y2 | medium | medium_x1 | medium_y1 | medium_x2 | medium_y2 | medium_adult | medium_adult_x1 | medium_adult_y1 | medium_adult_x2 | medium_adult_y2 | huge | huge_rating | huge_rating_adult | huge_rating_adult_x1 | huge_rating_adult_y1 | huge_rating_adult_x2 | huge_rating_adult_y2 | huge_adult | huge_adult_x1 | huge_adult_y1 | huge_adult_x2 | huge_adult_y2 | updated |created | moderated | moderated_ts | moderator_id | moderated_descr | rating_id | rating_moderated | rating_moderated_ts | rating_moderator_id | rating_moderated_descr | on_first | photo_checked | top_status | status | system_status |
+-------+-----------+-------------+------------+---------+-----------+-------+--------+--------+---------+-------+-----------+-------+----------+----------+----------+----------+------------+---------------+---------------+---------------+---------------+--------+-----------+-----------+-----------+-----------+--------------+-----------------+-----------------+-----------------+-----------------+------+-------------+-------------------+----------------------+----------------------+----------------------+----------------------+------------+---------------+---------------+---------------+---------------+---------------------+---------------------+-----------+---------------------+--------------+-----------------+-----------+------------------+---------------------+---------------------+------------------------+----------+---------------+------------+--------+---------------+
| 11795 | 0 | 2 | 3 | 11793 | 11794 | 500 | 327 | 415 | 271 | 23655 | jpg | Yes | 191 | 0 | 431 | 327 | Yes | 283 | 123 | 344 | 205 | Yes | 191 | 0 | 431 | 327 | No | 130 | 0 | 370 | 327 | Yes | No | No | 0 | 0 | 0 | 0 | No | 0 | 0 | 167 | 109 | 2004-05-07 19:56:14 |2004-01-13 17:28:00 | Approved | 2004-01-13 17:28:00 | 0 | | 11574 | Approved | 2004-01-13 17:28:00 | 0 | XML:import | No | Yes | Active | Active | Active |
+-------+-----------+-------------+------------+---------+-----------+-------+--------+--------+---------+-------+-----------+-------+----------+----------+----------+----------+------------+---------------+---------------+---------------+---------------+--------+-----------+-----------+-----------+-----------+--------------+-----------------+-----------------+-----------------+-----------------+------+-------------+-------------------+----------------------+----------------------+----------------------+----------------------+------------+---------------+---------------+---------------+---------------+---------------------+---------------------+-----------+---------------------+--------------+-----------------+-----------+------------------+---------------------+---------------------+------------------------+----------+---------------+------------+--------+---------------+
1 row in set (0.49 sec)
Thus, this bug does not seem to be associated with the limit 1, instead it seems to be associated with the order by ... desc.
[19 Apr 2005 9:01]
Jan Lindström
Following query works nicely: mysql> select p.rating_id from Photo as p where p.rating_id=11574 and p.moderated='Approved' and p.status='Active' and p.system_status='Active' and p.top_status!='Blocked' order by p.created desc limit 1; +-----------+ | rating_id | +-----------+ | 11574 | +-----------+ 1 row in set (11 min 53.54 sec) Thus problem is in a attribute rating_moderated which is a enum type attribute.
[20 Apr 2005 12:17]
Heikki Tuuri
This is probably a duplicate of the ENUM bug. The table is sorted in a wrong order. Please import the table to the latest 4.1.12 snapshot, and test again. Regards, Heikki
[20 Apr 2005 12:18]
Heikki Tuuri
The ENUM bug is: http://bugs.mysql.com/bug.php?id=9526
[21 Apr 2005 7:02]
Jan Lindström
Confirmed to work with 4.1.12bk: jplindst@t41:~/mysql-4.1/client$ ./mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.12-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use test; Database changed mysql> select p.rating_id from Photo as p where p.rating_id=11574 and p.moderated='Approved' and p.status='Active' and p.system_status='Active' and p.rating_moderated='Approved' and p.top_status!='Blocked' order by p.created desc limit 1; +-----------+ | rating_id | +-----------+ | 11574 | +-----------+ 1 row in set (3.26 sec)
