Bug #76576 ASSERTION FAILED in test_if_skip_sort_order
Submitted: 2 Apr 2015 9:06 Modified: 21 May 2015 13:46
Reporter: Benny Wang Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[2 Apr 2015 9:06] Benny Wang
Description:
#0  0x00007f08efaf78a5 in raise () from /lib64/libc.so.6
#1  0x00007f08efaf9085 in abort () from /lib64/libc.so.6
#2  0x00007f08efaf0a1e in __assert_fail_base () from /lib64/libc.so.6
#3  0x00007f08efaf0ae0 in __assert_fail () from /lib64/libc.so.6
#4  0x00000000015c0aa0 in test_if_skip_sort_order (tab=0x7f08601214e0,
    order=0x7f0860006ab8, select_limit=20, no_changes=false,
    map=0x7f086000ffd0, clause_type=0x1ff6efb "GROUP BY")
    at /export/home/tmp/xwang-test/mysql-trunk/sql/sql_optimizer.cc:2085
#5  0x00000000015beb94 in JOIN::test_skip_sort (this=0x7f08601210e0)
    at /export/home/tmp/xwang-test/mysql-trunk/sql/sql_optimizer.cc:1142
#6  0x00000000015bce50 in JOIN::optimize (this=0x7f08601210e0)
    at /export/home/tmp/xwang-test/mysql-trunk/sql/sql_optimizer.cc:631
#7  0x0000000001633a87 in st_select_lex::optimize (this=0x7f08600055f0, thd=
    0x7f0860000bb0)
    at /export/home/tmp/xwang-test/mysql-trunk/sql/sql_select.cc:1003
#8  0x00000000016321bb in handle_query (thd=0x7f0860000bb0,
    lex=0x7f0860002ce8, result=0x7f08601208c0, added_options=0,
    removed_options=0)
    at /export/home/tmp/xwang-test/mysql-trunk/sql/sql_select.cc:163
#9  0x00000000015e7e6a in execute_sqlcom_select (thd=0x7f0860000bb0,
    all_tables=0x7f0860120170)
    at /export/home/tmp/xwang-test/mysql-trunk/sql/sql_parse.cc:4782
#10 0x00000000015e0cba in mysql_execute_command (thd=0x7f0860000bb0)

How to repeat:
CREATE TABLE `CCC` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) NOT NULL,
  `col_int_key` int(11),
  `col_date_nokey` date NOT NULL,
  `col_date_key` date,
  `col_datetime_nokey` datetime NOT NULL,
  `col_time_nokey` time NOT NULL,
  `col_datetime_key` datetime ,
  `col_time_key` time ,
  `col_varchar_nokey` varchar(1) NOT NULL,
  `col_varchar_key` varchar(1),
  PRIMARY KEY (`pk`),
  KEY `gc_idx1` (`col_int_key`),
  KEY `gc_idx2` (`col_varchar_key`),
  KEY `gc_idx3` (`col_date_key`),
  KEY `gc_idx4` (`col_time_key`),
  KEY `gc_idx5` (`col_datetime_key`),
  KEY `gc_idx6` (`col_varchar_key`,`col_int_key`),
  KEY `gc_idx7` (`col_date_key`,`col_datetime_key`,`col_time_key`),
  KEY `gc_idx8`
(`col_int_key`,`col_varchar_key`,`col_date_key`,`col_time_key`,`col_datetime_key`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;

INSERT /*! IGNORE */ INTO CCC (
                                col_int_key,
                                col_date_nokey,
                                col_time_nokey,
                                col_datetime_nokey,
                                col_varchar_key
                        ) VALUES (1, '2009-12-01', '00:21:38.058143',
'2007-05-28 00:00:00', 'c'),(8, '2004-12-17', '04:08:02.046897', '2009-07-25
09:21:20.064099', 'm'),(9, '2000-03-14', '16:25:11.040240', '2002-01-16
00:00:00', 'd'),(24, '2000-10-08', '10:14:58.018534', '2006-10-12
04:32:53.031976', 'd'),(6, '2006-05-25', '19:47:59.011283', '2001-02-15
03:08:38.035426', 'y'),(1, '2008-01-23', '11:14:24.032949', '2004-10-02
20:31:15.022553', 't'),(6, '2007-06-18', NULL, '2002-08-20 22:48:00.035785',
'd'),(2, '2002-10-13', '00:00:00', '1900-01-01 00:00:00', 's'),(4,
'1900-01-01', '15:57:25.019666', '2005-08-15 00:00:00', 'r'),(8, NULL,
'07:05:51.006712', '1900-01-01 00:00:00', 'm'),(4, '2006-03-09',
'19:22:21.057406', '2008-05-16 08:09:06.002924', 'b'),(4, '2001-06-05',
'03:53:16.001370', '2001-01-20 12:47:23.022022', 'x'),(7, '2006-05-28',
'09:16:38.034570', '2008-07-02 00:00:00', 'g'),(4, '2001-04-19',
'15:37:26.028315', '1900-01-01 00:00:00', 'p'),(1, '1900-01-01', '00:00:00',
'2002-12-08 11:34:58.001571', 'q'),(9, '2004-08-20', '05:03:03.047452',
'1900-01-01 00:00:00', 'w'),(4, '2004-10-10', '02:59:24.063764', '1900-01-01
00:00:00', 'd'),(8, '2000-04-02', '00:01:58.064243', '2002-08-25
20:35:06.064634', 'e'),(4, '2006-11-02', '00:00:00', '2001-10-22
11:13:24.048128', 'b'),(8, '2009-01-28', '02:20:16.024931', '2003-03-12
02:00:34.029335', 'y');
SELECT DISTINCT MAX( `col_varchar_key`) AS field1 , `col_int_key` AS field2 ,
`col_int_key` AS field3 FROM CCC AS alias1 WHERE alias1 . `col_int_key` >=
'USA'  GROUP BY field2, field3;
[21 May 2015 13:46] Paul DuBois
Noted in 5.7.8, 5.8.0 changelogs.

An assertion could be raised for queries with a GROUP BY clause and a
table for which the optimizer identified multiple candidate indexes.