SELECT DISTINCT x...ORDER BY y LIMIT N,N crashes server

[markus_albe]

Running simple query leads to server crash:

CREATE TABLE `audits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user` varchar(255) DEFAULT NULL,
  `item_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `action` varchar(255) DEFAULT NULL,
  `item_lock_version` int(11) DEFAULT NULL,
  `reviewed_by` varchar(255) DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `hide_in_history` tinyint(1) DEFAULT '0',
  `accepted` tinyint(1) DEFAULT NULL,
  `type_id` int(11) DEFAULT NULL,
  `locale` varchar(255) DEFAULT 'nl',
  `sub_item_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_audits_on_item_id` (`item_id`),
  KEY `on_reviewed_by` (`reviewed_by`),
  KEY `index_audits_on_user` (`user`),
  KEY `index_audits_on_reviewed_by_and_type_id_and_created_at` (`reviewed_by`,`type_id`,`created_at`),
  KEY `idx_on_reviewed_by_and_type_id_and_item_id` (`reviewed_by`,`type_id`,`item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15459436 DEFAULT CHARSET=latin1;

SOURCE /tmp/audits.dump; -- available on request from devs

SET sql_mode='';

SELECT distinct audits.item_id FROM `audits` WHERE (audits.reviewed_by is null and audits.type_id = 233 and audits.user = 'UserName') ORDER BY audits.created_at desc LIMIT 0, 10;

ERROR 2013 (HY000): Lost connection to MySQL server during query

○ → resolve_stack_dump -s /tmp/mysqld.symbols -n /tmp/mysql.stack | c++filt
0xfe8b2e my_print_stacktrace + 46
0x9420b1 handle_fatal_signal + 1185
0x7f2e36fe3330 _end + 889345552
0xde0623 test_if_skip_sort_order(JOIN_TAB*, st_order*, unsigned long long, bool, Bitmap<64u> const*, char const*) + 2035
0xde5e99 JOIN::optimize_distinct_group_order() + 2121
0xdeced5 JOIN::optimize() + 6309
0xe2945a st_select_lex::optimize(THD*) + 74
0xe29625 handle_query(THD*, LEX*, Query_result*, unsigned long long, unsigned long long) + 357
0x909984 execute_sqlcom_select(THD*, TABLE_LIST*) + 563
0xdf4210 mysql_execute_command(THD*, bool) + 12368
0xdf6a35 mysql_parse(THD*, Parser_state*) + 1493
0xdf7cf1 dispatch_command(THD*, COM_DATA const*, enum_server_command) + 4689
0xdf84bd do_command(THD*) + 461
0xeacf4c handle_connection + 684
0x1017aa4 pfs_spawn_thread + 436
0x7f2e36fdb184 _end + 889312356
0x7f2e3524237d _end + 858277469

If we remove the LIMIT or use GROUP BY audits.item_id instead of DISTINCT, then the crash does not happen. This is a Percona Server 5.7 only, upstream 5.7 is not affected and Percona Server 5.6 is not affected.

I did found other bugs related to test_if_skip_sort_order, but again, I could not reproduce the behavior on upstream 5.7; These are somewhat similar, but they all include JOINs whereas the above is on single table

https://bugs.launchpad.net/maria/+bug/902356
https://bugs.mysql.com/bug.php?id=74505

[Sveta Smirnova (svetasmirnova)]

Reduced test case uploaded to internal ticket

[Laurynas Biveinis (laurynas-biveinis)]

    https://github.com/percona/percona-server/pull/1090

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1001