MySQL Bugs: #75929: Critical bug with orders in select statements

Bug #75929	Critical bug with orders in select statements
Submitted:	17 Feb 2015 9:47	Modified:	17 Feb 2015 13:40
Reporter:	Juraj Kovacik	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.6.23	OS:	Windows (2k8, win7)
Assigned to:	Tor Didriksen	CPU Architecture:	Any
Tags:	incorrect results sort, order by

Description:
Hello community,

it seems there is an critical bug with orders in select statements. 

Results from following queries are different in 5.5.27 and 5.6.23/5.7.14. 

Result from 5.5.27 server - OK:
query 1.) 205
query 2.) 205
query 3.) 205
query 4.) 205

Result from 5.6 / 5.7 server:
query 1.) 207
query 2.) 207
query 3.) 205
query 4.) 205

Return from SQL query executed in 5.6 server is correct only when there is primary key defined in order by.

How to repeat:
CREATE TABLE `tbl` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `command_path` varchar(100) NOT NULL,
  `id_list` int(10) unsigned NOT NULL,
  `id_machine` int(10) unsigned DEFAULT NULL,
  `id_case` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tbl` (`id`, `status`, `command_path`, `id_list`, `id_machine`, `id_case`) VALUES
(205,	0,	'',	123,	25,	238),
(206,	0,	'',	122,	25,	238),
(207,	0,	'',	121,	25,	238);

SELECT id  FROM tbl WHERE status=0 AND (id_machine=25 or id_machine is NULL) ORDER BY id_case, id_machine DESC limit 1;
SELECT id  FROM tbl WHERE status=0 AND (id_machine=25 or id_machine is NULL) order by id_Case  limit 0,1;
SELECT id  FROM tbl WHERE status=0 AND (id_machine=25 or id_machine is NULL) limit 1;
SELECT id  FROM tbl WHERE status=0 AND id_machine=25 order by id_case, id limit 1;

Same difference found when comparing 5.5.41 and 5.6.22

-- Peter
-- not a MySQL/Oracle eprson

mysql 5.5 > SELECT id  FROM tbl WHERE status=0 AND (id_machine=25 or id_machine is NULL) ORDER BY id_case, id_machine DESC limit
+-----+
| id  |
+-----+
| 205 |
+-----+
1 row in set (0.00 sec)
mysql 5.5 > SELECT id  FROM tbl WHERE status=0 AND (id_machine=25 or id_machine is NULL) order by id_Case  limit 0,1;
+-----+
| id  |
+-----+
| 205 |
+-----+
1 row in set (0.00 sec)
mysql 5.5 > SELECT id  FROM tbl WHERE status=0 AND (id_machine=25 or id_machine is NULL) limit 1;
+-----+
| id  |
+-----+
| 205 |
+-----+
1 row in set (0.00 sec)
mysql 5.5 > SELECT id  FROM tbl WHERE status=0 AND id_machine=25 order by id_case, id limit 1;
+-----+
| id  |
+-----+
| 205 |
+-----+
1 row in set (0.00 sec)
mysql 5.5 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+--------------------------------+
| Variable_name           | Value                          |
+-------------------------+--------------------------------+
| innodb_version          | 5.5.43                         |
| protocol_version        | 10                             |
| slave_type_conversions  |                                |
| version                 | 5.5.43-log                     |
| version_comment         | Source distribution 2015/02/09 |
| version_compile_machine | AMD64                          |
| version_compile_os      | Win64                          |
+-------------------------+--------------------------------+
7 rows in set (0.00 sec)
mysql 5.6 > SELECT id  FROM tbl WHERE status=0 AND (id_machine=25 or id_machine is NULL) ORDER BY id_case, id_machine DESC limit 1;
+-----+
| id  |
+-----+
| 207 |
+-----+
1 row in set (0.00 sec)
mysql 5.6 > SELECT id  FROM tbl WHERE status=0 AND (id_machine=25 or id_machine is NULL) order by id_Case  limit 0,1;
+-----+
| id  |
+-----+
| 207 |
+-----+
1 row in set (0.00 sec)
mysql 5.6 > SELECT id  FROM tbl WHERE status=0 AND (id_machine=25 or id_machine is NULL) limit 1;
+-----+
| id  |
+-----+
| 205 |
+-----+
1 row in set (0.00 sec)
mysql 5.6 > SELECT id  FROM tbl WHERE status=0 AND id_machine=25 order by id_case, id limit 1;
+-----+
| id  |
+-----+
| 205 |
+-----+
1 row in set (0.00 sec)

mysql 5.6 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+--------------------------------+
| Variable_name           | Value                          |
+-------------------------+--------------------------------+
| innodb_version          | 5.6.24                         |
| protocol_version        | 10                             |
| slave_type_conversions  |                                |
| version                 | 5.6.24                         |
| version_comment         | Source distribution 2015/02/09 |
| version_compile_machine | x86_64                         |
| version_compile_os      | Win64                          |
+-------------------------+--------------------------------+
7 rows in set (0.00 sec)

Thank you for the bug report. Notice 5.7 isn't affected:

mysql 5.7 > SELECT id  FROM tbl WHERE status=0 AND (id_machine=25 or id_machine is NULL) ORDER BY id_case, id_machine DESC limit 1;
+-----+
| id  |
+-----+
| 205 |
+-----+
1 row in set (0.00 sec)

mysql 5.7 > SELECT id  FROM tbl WHERE status=0 AND (id_machine=25 or id_machine is NULL) order by id_Case  limit 0,1;
+-----+
| id  |
+-----+
| 205 |
+-----+
1 row in set (0.00 sec)

mysql 5.7 > SELECT id  FROM tbl WHERE status=0 AND (id_machine=25 or id_machine is NULL) limit 1;
+-----+
| id  |
+-----+
| 205 |
+-----+
1 row in set (0.00 sec)

mysql 5.7 > SELECT id  FROM tbl WHERE status=0 AND id_machine=25 order by id_case, id limit 1;
+-----+
| id  |
+-----+
| 205 |
+-----+
1 row in set (0.00 sec)

mysql 5.7 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+--------------------------------+
| Variable_name           | Value                          |
+-------------------------+--------------------------------+
| innodb_version          | 5.7.6                          |
| protocol_version        | 10                             |
| slave_type_conversions  |                                |
| version                 | 5.7.6-m16-debug                |
| version_comment         | Source distribution 2015/02/09 |
| version_compile_machine | x86_64                         |
| version_compile_os      | Win64                          |
+-------------------------+--------------------------------+
7 rows in set (0.00 sec)

BTW: MariaDB 10.0.15 returns same as MySQL 5.6.22. So they have probably merged some critical code optinmizer code from MySQL 5.6.

My 'optimizer_switch' setting:

MySQL 5.6.22:
index_merge=ON,index_merge_union=ON,index_merge_sort_union=ON,index_merge_intersection=ON,engine_condition_pushdown=ON,index_condition_pushdown=ON,mrr=ON,mrr_cost_based=ON,block_nested_loop=ON,batched_key_access=off,materialization=ON,semijoin=ON,loosescan=ON,firstmatch=ON,subquery_materialization_cost_based=ON,use_index_extensions=ON  

MariaDB 10.0.15
index_merge=ON,index_merge_union=ON,index_merge_sort_union=ON,index_merge_intersection=ON,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=ON,derived_merge=ON,derived_with_keys=ON,firstmatch=ON,loosescan=ON,materialization=ON,in_to_exists=ON,semijoin=ON,partial_match_rowid_merge=ON,partial_match_table_scan=ON,subquery_cache=ON,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=ON,semijoin_with_cache=ON,join_cache_incremental=ON,join_cache_hashed=ON,join_cache_bka=ON,optimize_join_buffer_size=off,table_elimination=ON,extended_keys=ON,exists_to_in=ON 

(I believe they are both defaults)

I failed to see what exactly is the bug here?  How can rows be predicted when the order by does not include a unique/PK row?

This is not a bug.
See discussion here:
http://bugs.mysql.com/bug.php?id=72076

Hello Tor,

thank you for your reply. I understand following statement:

"If multiple rows have identical values in the ORDER BY columns, the
server is free to return those rows in any order, and may do so
differently depending on the overall execution plan. In other words,
the sort order of those rows is nondeterministic with respect to
the nonordered columns." This is logical.

But im not satisfied with query results. Why are results from 5.5 equal to newest 5.7 and versions from 5.6 to 5.7.6.-m15 are different?

Posted by developer:
 
The actual order for a non-deterministic sort may depend on many things
- how the storage engine stores and returns rows
- the PriorityQueue implementation (I switched implementation some time ago)
- the final sorting algorithm used (I switched from homegrown quicksort, to C++ std::sort recently)