Description:
This query:
SELECT `varchar_key`
FROM (
SELECT *
FROM B ) table2
HAVING ( `varchar_key` , table1 ) IN (
SELECT `varchar_nokey`
FROM B ) ;
Causes the following crash (full crash output attached as separate file)
This *only* occurs when materialization is turned on
Assertion failed: (fixed == 0), function fix_fields, file item.cc, line 6004
# 11:13:27 Thread 11 (core thread 10):
# 11:13:27 #0 0x90efe136 in clock_get_attributes ()
# 11:13:27 #1 0x90ef7013 in usleep$NOCANCEL$UNIX2003 ()
# 11:13:27 #2 0x90f0e685 in abort ()
# 11:13:27 #3 0x90f033db in __assert_rtn ()
# 11:13:27 #4 0x00029879 in Item_ref::fix_fields (this=0x19fabf0, thd=0x1248e18, reference=0x19fadc8) at item.cc:6004
# 11:13:27 #5 0x000b0edf in Item_row::fix_fields (this=0x19fad60, thd=0x1248e18, ref=0x2b5b5b8) at item_row.cc:68
# 11:13:27 #6 0x000ab17d in Item_in_subselect::select_in_like_transformer (this=0x2b58248, join=0x2b72028, func=0x8f46b4) at item_subselect.cc:1720
# 11:13:27 #7 0x000ab34c in Item_in_subselect::select_transformer (this=0x2b58248, join=0x2b72028) at item_subselect.cc:1647
# 11:13:27 #8 0x001b01c8 in JOIN::prepare (this=0x2b72028, rref_pointer_array=0x19faed4, tables_init=0x19fbae8, wild_num=0, conds_init=0x2b58158, og_num=0, order_init=0x0, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x19fadd0, unit_arg=0x19faf68) at sql_select.cc:707
# 11:13:27 #9 0x000abe59 in subselect_single_select_engine::prepare (this=0x2b58300) at item_subselect.cc:2109
# 11:13:27 #10 0x000a6589 in Item_subselect::fix_fields (this=0x2b58248, thd_param=0x1248e18, ref=0x2b65c98) at item_subselect.cc:174
# 11:13:27 #11 0x000ab4ed in Item_in_subselect::fix_fields (this=0x2b58248, thd_arg=0x1248e18, ref=0x2b65c98) at item_subselect.cc:1788
# 11:13:27 #12 0x001afa07 in JOIN::prepare (this=0x2b61028, rref_pointer_array=0x124a320, tables_init=0x12569f8, wild_num=0, conds_init=0x19fa958, og_num=10, order_init=0x2b583f0, group_init=0x19faab0, having_init=0x2b58248, proc_param_init=0x0, select_lex_arg=0x124a21c, unit_arg=0x1249d6c) at sql_select.cc:555
# 11:13:27 #13 0x001bbebe in mysql_select (thd=0x1248e18, rref_pointer_array=0x124a320, tables=0x12569f8, wild_num=0, fields=@0x124a2b0, conds=0x19fa958, og_num=10, order=0x2b583f0, group=0x19faab0, having=0x2b58248, proc_param=0x0, select_options=2147764736, result=0x2b5b2e8, unit=0x1249d6c, select_lex=0x124a21c) at sql_select.cc:3070
# 11:13:27 #14 0x001bc2bd in handle_select (thd=0x1248e18, lex=0x1249d10, result=0x2b5b2e8, setup_tables_done_option=0) at sql_select.cc:306
# 11:13:27 #15 0x0010f2db in execute_sqlcom_select (thd=0x1248e18, all_tables=0x12569f8) at sql_parse.cc:4930
# 11:13:27 #16 0x0011576a in mysql_execute_command (thd=0x1248e18) at sql_parse.cc:2112
# 11:13:27 #17 0x0011f277 in mysql_parse (thd=0x1248e18, inBuf=0x1255228 "SELECT table1 . `date_key` AS field1 , table2 . `varchar_key` AS field2 , MAX( table1 . `int_nokey` ) AS field3 , MAX( table1 . `varchar_key` ) AS field4 FROM ( C AS table1 RIGHT OUTER JOIN ( ( ( ("..., length=1652, found_semicolon=0xb0512e14) at sql_parse.cc:5945
# 11:13:27 #18 0x0011fdf4 in dispatch_command (command=COM_QUERY, thd=0x1248e18, packet=0x19f0019 " SELECT table1 . `date_key` AS field1 , table2 . `varchar_key` AS field2 , MAX( table1 . `int_nokey` ) AS field3 , MAX( table1 . `varchar_key` ) AS field4 FROM ( C AS table1 RIGHT OUTER JOIN ( ( ( "..., packet_length=1653) at sql_parse.cc:1062
# 11:13:27 #19 0x001211ca in do_command (thd=0x1248e18) at sql_parse.cc:744
# 11:13:27 #20 0x0010cc0b in handle_one_connection (arg=0x1248e18) at sql_connect.cc:1163
# 11:13:27 #21 0x90e53155 in _pthread_start ()
# 11:13:27 #22 0x90e53012 in thread_start ()
How to repeat:
MTR test case - note the full test case with the original crashing query is attached as a separate file
Toggle the materialization switch on and off and observe that the crash will vanish
/*!50400 SET SESSION optimizer_switch = 'firstmatch=on,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=on,materialization=on,semijoin=on' */;
/*!50400 SET SESSION optimizer_use_mrr = 'force' */;
/*!50400 SET SESSION engine_condition_pushdown = 'ON' */;
/*!50400 SET SESSION join_cache_level = 1 */;
#/* Begin test case for query 0 */
--disable_warnings
DROP TABLE /*! IF EXISTS */ B;
--enable_warnings
CREATE TABLE `B` (
`varchar_key` varchar(1) DEFAULT NULL,
`varchar_nokey` varchar(1) DEFAULT NULL,
KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES ('f','f');
SELECT `varchar_key`
FROM (
SELECT *
FROM B ) table2
HAVING ( `varchar_key` , table1 ) IN (
SELECT `varchar_nokey`
FROM B ) ;
DROP TABLE B;
#/* End of test case for query 0 */
Suggested fix:
Ensure correct, crash free query processing regardless of optimizer switch settings.