Bug #46767 Crash using unknown column name in HAVING clause subquery with materialization
Submitted: 17 Aug 2009 20:32 Modified: 24 Aug 2009 7:10
Reporter: Patrick Crews Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.4/6.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: assertion failed, crashing bug, materialization, subquery

[17 Aug 2009 20:32] Patrick Crews
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.
[17 Aug 2009 20:34] Patrick Crews
full crash output with backtraces, etc

Attachment: bug46767_crash_output.txt (text/plain), 16.28 KiB.

[17 Aug 2009 20:35] Patrick Crews
Full MTR test case with original and simplified queries

Attachment: bug46767_test.txt (text/plain), 8.08 KiB.

[24 Aug 2009 7:10] Philip Stoev
Duplicate of bug #45863