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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.059 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.