| Bug #21992 | ORDER BY Does not Sort rows | ||
|---|---|---|---|
| Submitted: | 4 Sep 2006 14:12 | Modified: | 5 Sep 2006 9:46 |
| Reporter: | Peter Zaitsev (Basic Quality Contributor) | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.21 | OS: | Any (all) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Optimizer, order by | ||
[4 Sep 2006 14:12]
Peter Zaitsev
[4 Sep 2006 14:40]
Valeriy Kravchuk
Thank you for a problem report. Sorry, but I wans not able to repeat the behaviour described with dummy data in the table and 4.1.22-BK on Linux:
openxs@suse:~/dbs/4.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.22
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `mantis_bug_table` (
-> `id` int(10) unsigned NOT NULL auto_increment,
-> `project_id` int(10) unsigned NOT NULL default '0',
-> `reporter_id` int(10) unsigned NOT NULL default '0',
-> `handler_id` int(10) unsigned NOT NULL default '0',
-> `duplicate_id` int(10) unsigned NOT NULL default '0',
-> `priority` smallint(6) NOT NULL default '30',
-> `severity` smallint(6) NOT NULL default '50',
-> `reproducibility` smallint(6) NOT NULL default '10',
-> `status` smallint(6) NOT NULL default '10',
-> `resolution` smallint(6) NOT NULL default '10',
-> `projection` smallint(6) NOT NULL default '10',
-> `category` varchar(64) NOT NULL default '',
-> `date_submitted` datetime NOT NULL default '1970-01-01 00:00:01',
-> `last_updated` datetime NOT NULL default '1970-01-01 00:00:01',
-> `eta` smallint(6) NOT NULL default '10',
-> `bug_text_id` int(10) unsigned NOT NULL default '0',
-> `os` varchar(32) NOT NULL default '',
-> `os_build` varchar(32) NOT NULL default '',
-> `platform` varchar(32) NOT NULL default '',
-> `version` varchar(64) NOT NULL default '',
-> `fixed_in_version` varchar(64) NOT NULL default '',
-> `build` varchar(32) NOT NULL default '',
-> `profile_id` int(10) unsigned NOT NULL default '0',
-> `view_state` smallint(6) NOT NULL default '10',
-> `summary` varchar(128) NOT NULL default '',
-> `sponsorship_total` int(11) NOT NULL default '0',
-> `sticky` tinyint(4) NOT NULL default '0',
-> PRIMARY KEY (`id`),
-> KEY `idx_bug_sponsorship_total` (`sponsorship_total`),
-> KEY `idx_bug_fixed_in_version` (`fixed_in_version`),
-> KEY `idx_bug_status` (`status`),
-> KEY `idx_project` (`project_id`)
-> ) ENGINE=MyISAM AUTO_INCREMENT=247 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into `mantis_bug_table` (last_updated) values(now()+rand());
Query OK, 1 row affected (0.01 sec)
mysql> insert into `mantis_bug_table` (last_updated) values(now()+rand());
Query OK, 1 row affected (0.00 sec)
mysql> insert into `mantis_bug_table` (last_updated) values(now()+rand());
Query OK, 1 row affected (0.00 sec)
mysql> insert into `mantis_bug_table` (last_updated) values(now()+rand());
Query OK, 1 row affected (0.00 sec)
mysql> insert into `mantis_bug_table` (last_updated) values(now()+rand());
Query OK, 1 row affected (0.01 sec)
mysql> insert into `mantis_bug_table` (last_updated) select last_updated +rand(
) from `mantis_bug_table`;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> insert into `mantis_bug_table` (last_updated) select last_updated +rand(
) from `mantis_bug_table`;
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> insert into `mantis_bug_table` (last_updated) select last_updated +rand(
) from `mantis_bug_table`;
Query OK, 20 rows affected (0.01 sec)
Records: 20 Duplicates: 0 Warnings: 0
mysql> insert into `mantis_bug_table` (last_updated) select last_updated +rand(
) from `mantis_bug_table`;
Query OK, 40 rows affected (0.00 sec)
Records: 40 Duplicates: 0 Warnings: 0
mysql> insert into `mantis_bug_table` (last_updated) select last_updated +rand(
) from `mantis_bug_table`;
Query OK, 80 rows affected (0.01 sec)
Records: 80 Duplicates: 0 Warnings: 0
mysql> insert into `mantis_bug_table` (last_updated) select last_updated +rand(
) from `mantis_bug_table`;
Query OK, 160 rows affected (0.01 sec)
Records: 160 Duplicates: 0 Warnings: 0
mysql> insert into `mantis_bug_table` (last_updated) select last_updated +rand(
) from `mantis_bug_table`;
Query OK, 320 rows affected, 1 warning (0.01 sec)
Records: 320 Duplicates: 0 Warnings: 1
mysql> explain SELECT DISTINCT
-> id,mantis_bug_table.sticky,last_updated,date_submitted FROM mantis_bug_table
-> WHERE mantis_bug_table.id in (2, 3, 5, 6, 7, 10, 11, 12, 13, 14, 15, 18,
19, 20,
-> 21, 22, 27, 30, 33, 34, 38, 39, 40, 45, 49, 50, 53, 54, 56, 57, 60, 68,
69, 74,
-> 75, 77, 82, 83, 84, 85, 86, 87, 88, 89, 92, 93, 94, 95, 97, 99, 103, 104
, 105,
-> 106, 109, 112, 113, 114, 115, 116, 118, 119, 121, 122, 123, 124, 125, 129, 131,
-> 133, 139, 141, 143, 146, 147, 155, 156, 157, 160, 161, 172, 173, 175, 176, 181,
-> 185, 186, 189, 190, 191, 192, 193, 194, 195, 197, 198, 201, 205, 206, 207, 208,
-> 209, 210, 211, 212, 216, 217, 218, 219, 220, 222, 223, 224, 225, 226, 227, 228,
-> 231, 233, 234, 235, 236, 237, 238, 239, 240, 241, 242, 243, 244, 245) ORDER BY
-> sticky DESC, last_updated DESC, date_submitted DESC LIMIT 0,5\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mantis_bug_table
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 131
Extra: Using where; Using filesort
1 row in set (0.00 sec)
Please, upload dump of your real data or explain what's wrong with my test.
[4 Sep 2006 14:52]
Peter Zaitsev
Here is the data: DROP TABLE IF EXISTS `mbt`; CREATE TABLE `mbt` ( `id` int(10) unsigned NOT NULL auto_increment, `project_id` int(10) unsigned NOT NULL default '0', `reporter_id` int(10) unsigned NOT NULL default '0', `handler_id` int(10) unsigned NOT NULL default '0', `duplicate_id` int(10) unsigned NOT NULL default '0', `priority` smallint(6) NOT NULL default '30', `severity` smallint(6) NOT NULL default '50', `reproducibility` smallint(6) NOT NULL default '10', `status` smallint(6) NOT NULL default '10', `resolution` smallint(6) NOT NULL default '10', `projection` smallint(6) NOT NULL default '10', `category` varchar(64) NOT NULL default '', `date_submitted` datetime NOT NULL default '1970-01-01 00:00:01', `last_updated` datetime NOT NULL default '1970-01-01 00:00:01', `eta` smallint(6) NOT NULL default '10', `bug_text_id` int(10) unsigned NOT NULL default '0', `os` varchar(32) NOT NULL default '', `os_build` varchar(32) NOT NULL default '', `platform` varchar(32) NOT NULL default '', `version` varchar(64) NOT NULL default '', `fixed_in_version` varchar(64) NOT NULL default '', `build` varchar(32) NOT NULL default '', `profile_id` int(10) unsigned NOT NULL default '0', `view_state` smallint(6) NOT NULL default '10', `summary` varchar(128) NOT NULL default '', `sponsorship_total` int(11) NOT NULL default '0', `sticky` tinyint(4) NOT NULL default '0', PRIMARY KEY (`id`), KEY `idx_bug_sponsorship_total` (`sponsorship_total`), KEY `idx_bug_fixed_in_version` (`fixed_in_version`), KEY `idx_bug_status` (`status`), KEY `idx_project` (`project_id`) ) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8; -- -- Dumping data for table `mbt` -- INSERT INTO `mbt` VALUES (2,1,1,1,0,30,50,10,50,10,10,'2-server scripts','2006-05-11 06:13:07','2006-05-11 06:22:04',10,2,'','','','','','',0,10,'',0,0),(3,1,2,1,0,30,50,10,50,10,10,'1-web scripts','2006-05-11 06:14:51','2006-05-11 06:21:43',10,3,'','','','','','',0,10,'',0,0),(5,1,1,3,0,30,50,10,50,10,10,'1-web scripts','2006-05-11 06:20:26','2006-05-15 02:25:43',10,5,'','','','','','',0,10,'',0,0),(6,1,1,1,0,30,50,10,50,10,10,'1-web scripts','2006-05-11 06:23:18','2006-05-11 06:23:18',10,6,'','','','','','',0,10,'',0,0),(7,1,1,1,0,30,50,10,50,10,10,'1-web scripts','2006-05-11 06:25:59','2006-05-11 06:25:59',10,7,'','','','','','',0,10,'',0,0),(10,1,1,1,0,30,50,10,50,10,10,'1-web scripts','2006-05-11 06:29:58','2006-05-11 06:29:58',10,10,'','','','','','',0,10,'',0,0),(11,1,1,1,0,30,50,10,50,10,10,'1-web scripts','2006-05-11 06:34:16','2006-05-11 06:34:16',10,11,'','','','','','',0,10,'',0,0); New query: mysql> SELECT DISTINCT id,sticky,last_updated,date_submitted FROM mbt WHERE id in (2, 3, 5, 6) ORDER BY sticky DESC, last_updated DESC, date_submitted DESC LIMIT 0,5; +----+--------+---------------------+---------------------+ | id | sticky | last_updated | date_submitted | +----+--------+---------------------+---------------------+ | 2 | 0 | 2006-05-11 06:22:04 | 2006-05-11 06:13:07 | | 3 | 0 | 2006-05-11 06:21:43 | 2006-05-11 06:14:51 | | 5 | 0 | 2006-05-15 02:25:43 | 2006-05-11 06:20:26 | | 6 | 0 | 2006-05-11 06:23:18 | 2006-05-11 06:23:18 | +----+--------+---------------------+---------------------+ 4 rows in set (0.00 sec)
[4 Sep 2006 15:10]
Martin Friebe
possible this is bug #21456 ?
[4 Sep 2006 15:20]
Peter Zaitsev
Quite possibly Martin. Interesting enough I tried to find if there is any matching bugs before submiting this one. Sadly enough search in bugs system is very poor (Or it should be used some special way) ? For some reason it only is able to search ANY of keywords, not ALL of theywords, so if I search SELECT DISTINCT ORDER BY I get 351 matching bugs which are not even sorted by relavance (which match most words could be matched first)
[5 Sep 2006 9:46]
Valeriy Kravchuk
Duplicate of bug #21456, it seems. Already fixed in current sources, as shown by my test.
