Bug #53712 ORDER BY causes incorrect query result
Submitted: 17 May 2010 16:13 Modified: 18 Jun 2010 18:36
Reporter: River Tarnell Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.1.46 OS:Solaris
Assigned to: CPU Architecture:Any

[17 May 2010 16:13] River Tarnell
Description:
Reproduced this issue on two servers, both running 64-bit 5.1.46 with standard InnoDB on Solaris 10 Update 8 x86.

river@sql-s2-fast:dewiki_p> SELECT dl.*, page_title, COUNT(NULLIF(p.page_namespace=0,FALSE)) as c from u_river.dewiki_dab_links dl INNER JOIN dewiki_p.page p ON dl.page_id = p.page_id  WHERE dl.date='2010-05-17' AND p.page_namespace IN (0,10,14)  GROUP BY dl.dab_main_title order by c LIMIT 20;
+------------+---------+--------+----------------+-----------------------+--------+
| date       | page_id | dab_id | dab_main_title | page_title            | c      |
+------------+---------+--------+----------------+-----------------------+--------+
| 2010-05-17 |    5850 | 128283 | NULL           | 1920er                |  24202 |
| 2010-05-17 | 4258476 | 232415 |                | Badanhausen_(Kinding) | 169987 |
+------------+---------+--------+----------------+-----------------------+--------+
2 rows in set (8.30 sec)

river@sql-s2-fast:dewiki_p> SELECT dl.*, page_title, COUNT(NULLIF(p.page_namespace=0,FALSE)) as c from u_river.dewiki_dab_links dl INNER JOIN dewiki_p.page p ON dl.page_id = p.page_id  WHERE dl.date='2010-05-17' AND p.page_namespace IN (0,10,14)  GROUP BY dl.dab_main_title LIMIT 5;        
+------------+---------+---------+----------------+----------------------+---+
| date       | page_id | dab_id  | dab_main_title | page_title           | c |
+------------+---------+---------+----------------+----------------------+---+
| 2010-05-17 | 5015402 | 5014872 | !!             | ‼                  | 2 |
| 2010-05-17 |   14692 |   22811 | 0              | Null                 | 4 |
| 2010-05-17 |   27611 |   27608 | 08/15          | Null-acht-fünf-zehn | 6 |
| 2010-05-17 |  246114 | 5202696 | 1-Cent-Münze  | Cent_(Währung)      | 1 |
| 2010-05-17 | 1235090 | 1915462 | 1._Armee       | 1._Deutsche_Armee    | 1 |
+------------+---------+---------+----------------+----------------------+---+
5 rows in set (0.00 sec)

river@sql-s2-fast:dewiki_p> explain SELECT dl.*, page_title, COUNT(NULLIF(p.page_namespace=0,FALSE)) as c from u_river.dewiki_dab_links dl INNER JOIN dewiki_p.page p ON dl.page_id = p.page_id  WHERE dl.date='2010-05-17' AND p.page_namespace IN (0,10,14)  GROUP BY dl.dab_main_title order by c LIMIT 5;
+----+-------------+-------+--------+----------------------+----------------+---------+--------------------+------+-----------------------------------------------------------+
| id | select_type | table | type   | possible_keys        | key            | key_len | ref                | rows | Extra                                                     |
+----+-------------+-------+--------+----------------------+----------------+---------+--------------------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | dl    | index  | PRIMARY,date,page_id | dab_main_title | 258     | NULL               |   80 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | page  | eq_ref | PRIMARY,name_title   | PRIMARY        | 4       | u_river.dl.page_id |    1 | Using where                                               |
+----+-------------+-------+--------+----------------------+----------------+---------+--------------------+------+-----------------------------------------------------------+
2 rows in set (0.01 sec)

river@sql-s2-fast:dewiki_p> explain SELECT dl.*, page_title, COUNT(NULLIF(p.page_namespace=0,FALSE)) as c from u_river.dewiki_dab_links dl INNER JOIN dewiki_p.page p ON dl.page_id = p.page_id  WHERE dl.date='2010-05-17' AND p.page_namespace IN (0,10,14)  GROUP BY dl.dab_main_title LIMIT 20;                   
+----+-------------+-------+--------+----------------------+----------------+---------+--------------------+------+--------------------------+
| id | select_type | table | type   | possible_keys        | key            | key_len | ref                | rows | Extra                    |
+----+-------------+-------+--------+----------------------+----------------+---------+--------------------+------+--------------------------+
|  1 | SIMPLE      | dl    | index  | PRIMARY,date,page_id | dab_main_title | 258     | NULL               |   80 | Using where; Using index |
|  1 | SIMPLE      | page  | eq_ref | PRIMARY,name_title   | PRIMARY        | 4       | u_river.dl.page_id |    1 | Using where              |
+----+-------------+-------+--------+----------------------+----------------+---------+--------------------+------+--------------------------+
2 rows in set (0.00 sec)

The only difference in the query is the ORDER BY, but the result is significantly different.

root@sql-s2-fast:(none)> show create table dewiki.page\G
*************************** 1. row ***************************
       Table: page
Create Table: CREATE TABLE `page` (
  `page_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `page_namespace` int(11) NOT NULL DEFAULT '0',
  `page_title` varbinary(255) NOT NULL DEFAULT '',
  `page_restrictions` tinyblob NOT NULL,
  `page_counter` bigint(20) unsigned NOT NULL DEFAULT '0',
  `page_is_redirect` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `page_is_new` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `page_random` double unsigned NOT NULL DEFAULT '0',
  `page_touched` varbinary(14) NOT NULL DEFAULT '',
  `page_latest` int(8) unsigned NOT NULL DEFAULT '0',
  `page_len` int(8) unsigned NOT NULL DEFAULT '0',
  `page_no_title_convert` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`page_id`),
  UNIQUE KEY `name_title` (`page_namespace`,`page_title`),
  KEY `page_random` (`page_random`),
  KEY `page_len` (`page_len`)
) ENGINE=InnoDB AUTO_INCREMENT=5392991 DEFAULT CHARSET=binary
1 row in set (0.00 sec)

root@sql-s2-fast:(none)> show create table u_river.dewiki_dab_links\G
*************************** 1. row ***************************
       Table: dewiki_dab_links
Create Table: CREATE TABLE `dewiki_dab_links` (
  `date` date NOT NULL DEFAULT '0000-00-00',
  `page_id` int(11) NOT NULL DEFAULT '0',
  `dab_id` int(11) NOT NULL DEFAULT '0',
  `dab_main_title` varbinary(255) DEFAULT NULL,
  PRIMARY KEY (`date`,`page_id`,`dab_id`),
  KEY `date` (`date`),
  KEY `page_id` (`page_id`),
  KEY `dab_id` (`dab_id`),
  KEY `dab_main_title` (`dab_main_title`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

I have dumped both tables (102MB total) and can upload the dump to ftp.mysql.com if necessary.

How to repeat:
Load dump, run both queries, observe output.
[18 May 2010 18:36] Valeriy Kravchuk
Thank you for the problem report. Please, upload your dump and send file name here.
[18 Jun 2010 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".