Details
-
Type:
Bug
-
Status: Closed
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
The following query:
SELECT *
FROM t2
LEFT JOIN t1 ON t2.a != 0
AND ( 'j' , 'r' ) IN (
SELECT b , a
FROM t3
);
crashes as follows when executed twice as a prepared statement with semijoin:
#4 <signal handler called>
#5 0x081d838c in Item_ref::fix_fields (this=0xae6138a8, thd=0x9c3c6b8, reference=0xae613a38) at item.cc:6417
#6 0x081f3adb in Item_func::fix_fields (this=0xae6139d8, thd=0x9c3c6b8, ref=0xae613dac) at item_func.cc:180
#7 0x08215327 in Item_cond::fix_fields (this=0xae613cf8, thd=0x9c3c6b8, ref=0x0) at item_cmpfunc.cc:4129
#8 0x08249bee in Item_in_subselect::create_row_in_to_exists_cond (this=0xae62aa80, join=0xae635280,
where_item=0xae63a4a8, having_item=0xae63a4ac) at item_subselect.cc:2141
#9 0x08249e13 in Item_in_subselect::create_in_to_exists_cond (this=0xae62aa80, join_arg=0xae635280)
at item_subselect.cc:2197
#10 0x083e3ea6 in JOIN::choose_subquery_plan (this=0xae635280, join_tables=0) at opt_subselect.cc:4462
#11 0x0832364a in make_join_statistics (join=0xae635280, tables_list=..., conds=0x0, keyuse_array=0xae63a3c0)
at sql_select.cc:3544
#12 0x0831b23c in JOIN::optimize (this=0xae635280) at sql_select.cc:1112
#13 0x081b63c2 in st_select_lex::optimize_unflattened_subqueries (this=0xae62911c) at sql_lex.cc:3126
#14 0x083e3bcf in JOIN::optimize_unflattened_subqueries (this=0xae62cdf0) at opt_subselect.cc:4337
#15 0x0831ce0e in JOIN::optimize (this=0xae62cdf0) at sql_select.cc:1621
#16 0x08321015 in mysql_select (thd=0x9c3c6b8, rref_pointer_array=0xae629258, tables=0xae629950, wild_num=0,
fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416200192,
result=0xae62ac88, unit=0xae628e3c, select_lex=0xae62911c) at sql_select.cc:2886
#17 0x08318e6b in handle_select (thd=0x9c3c6b8, lex=0xae628de0, result=0xae62ac88, setup_tables_done_option=0)
at sql_select.cc:283
#18 0x082b3cb9 in execute_sqlcom_select (thd=0x9c3c6b8, all_tables=0xae629950) at sql_parse.cc:5102
#19 0x082aaaca in mysql_execute_command (thd=0x9c3c6b8) at sql_parse.cc:2247
#20 0x08363658 in Prepared_statement::execute (this=0xae717b10, expanded_query=0xae9e070c, open_cursor=false)
at sql_prepare.cc:3735
#21 0x08362b73 in Prepared_statement::execute_loop (this=0xae717b10, expanded_query=0xae9e070c, open_cursor=false,
packet=0x0, packet_end=0x0) at sql_prepare.cc:3416
#22 0x08361442 in mysql_sql_stmt_execute (thd=0x9c3c6b8) at sql_prepare.cc:2641
#23 0x082aaaf3 in mysql_execute_command (thd=0x9c3c6b8) at sql_parse.cc:2256
#24 0x082b62e8 in mysql_parse (thd=0x9c3c6b8, rawbuf=0xae612a10 "EXECUTE st1", length=11, found_semicolon=0xae9e1228)
at sql_parse.cc:6102
#25 0x082a8710 in dispatch_command (command=COM_QUERY, thd=0x9c3c6b8, packet=0x9c95549 "EXECUTE st1", packet_length=11)
at sql_parse.cc:1221
#26 0x082a7b6b in do_command (thd=0x9c3c6b8) at sql_parse.cc:916
#27 0x082a4af5 in handle_one_connection (arg=0x9c3c6b8) at sql_connect.cc:1187
#28 0x00821919 in start_thread () from /lib/libpthread.so.0
#29 0x0076acce in clone () from /lib/libc.so.6
minimal optimizer switch: semijoin=on;
full optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_ sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,m aterialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache= off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimi ze_join_buffer_size=off,table_elimination=on
explain:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 system NULL NULL NULL NULL 0 const row not found
1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
revision-id: <email address hidden>
date: 2011-09-10 18:01:27 +0300
build-date: 2011-09-14 09:12:12 +0300
revno: 3183
branch-nick: maria-5.3
test case:
SET SESSION optimizer_switch = 'semijoin=on';
CREATE TABLE t1 (a int);
CREATE TABLE t2 (a int);
CREATE TABLE t3 (a int, b int) ;
PREPARE st1 FROM "
SELECT *
FROM t2
LEFT JOIN t1 ON t2.a != 0
AND ( 'j' , 'r' ) IN (
SELECT b , a
FROM t3
);
";
EXECUTE st1;
EXECUTE st1;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Crash in Item_func::fix_fields on second execution of a prepared statement with semijoin
It is not clear why the problem happens only with semijoin=on . The subquery is in the ON clause, so it is not converted to semi-join.
Re: Crash in Item_func::fix_fields on second execution of a prepared statement with semijoin
The relationship to semi-joins is as follows: currently, semi-join runtime processes IN subqueries in the ON clauses as follows:
- it considers them to be semi-join conversion candidates (and so, records them in sj_subselects)
- convert_join_subqueries_to_semijoins() will not convert subqueries from ON expressions, it will fall-back to IN->EXISTS for them.
apparently, there is something wrong that happens on this execution path.
Re: Crash in Item_func::fix_fields on second execution of a prepared statement with semijoin
This can also be an assertion:
mysqld: item_subselect.cc:1394: virtual longlong Item_in_subselect::val_int(): Assertion `0' failed.
- 2011-09-15T00:02:01 #6 0x00000033b58296e6 in __assert_fail () from /lib64/libc.so.6
- 2011-09-15T00:02:01 #7 0x000000000062c3ba in Item_in_subselect::val_int (this=0x2aaacc033f68) at item_subselect.cc:1394
- 2011-09-15T00:02:01 #8 0x000000000063979c in Item_func_trig_cond::val_int (this=0x2aaacc15ef70) at item_cmpfunc.h:477
- 2011-09-15T00:02:01 #9 0x000000000073694a in sub_select (join=0x2aaacc024fe0, join_tab=0x2aaacc15e850, end_of_records=false) at sql_select.cc:15083
- 2011-09-15T00:02:01 #10 0x000000000072d4a3 in evaluate_join_record (join=0x2aaacc024fe0, join_tab=0x2aaacc15e530, error=0) at sql_select.cc:15297
- 2011-09-15T00:02:01 #11 0x0000000000736a58 in sub_select (join=0x2aaacc024fe0, join_tab=0x2aaacc15e530, end_of_records=false) at sql_select.cc:15100
- 2011-09-15T00:02:01 #12 0x0000000000737c6f in do_select (join=0x2aaacc024fe0, fields=0x2aaacc02b048, table=0x0, procedure=0x0) at sql_select.cc:14763
- 2011-09-15T00:02:01 #13 0x0000000000757d0a in JOIN::exec (this=0x2aaacc024fe0) at sql_select.cc:2679
- 2011-09-15T00:02:01 #14 0x0000000000633cb9 in subselect_single_select_engine::exec (this=0x2aaacc034748) at item_subselect.cc:2967
- 2011-09-15T00:02:01 #15 0x0000000000638bd4 in Item_subselect::exec (this=0x2aaacc1f0b30) at item_subselect.cc:572
- 2011-09-15T00:02:01 #16 0x000000000062a696 in Item_singlerow_subselect::val_str (this=0x2aaacc1f0b30, str=0x2aaacc1f0b48) at item_subselect.cc:1090
- 2011-09-15T00:02:01 #17 0x0000000000755aef in JOIN::exec (this=0x2aaacc0870e0) at sql_select.cc:2147
- 2011-09-15T00:02:01 #18 0x0000000000751c12 in mysql_select (thd=0x2aaabd11fa08, rref_pointer_array=0x2aaacc0e14c8, tables=0x2aaacc1f1508, wild_num=0, fields=...,
- 2011-09-15T00:02:01 conds=0x2aaacc28c620, og_num=12, order=0x2aaacc14a418, group=0x2aaacc149ec0, having=0x0, proc_param=0x0, select_options=2416232961,
- 2011-09-15T00:02:01 result=0x2aaacc14ad98, unit=0x2aaacc0e0d90, select_lex=0x2aaacc0e1278) at sql_select.cc:2900
- 2011-09-15T00:02:01 #19 0x000000000075803c in handle_select (thd=0x2aaabd11fa08, lex=0x2aaacc0e0cf0, result=0x2aaacc14ad98, setup_tables_done_option=0) at sql_select.cc:283
- 2011-09-15T00:02:01 #20 0x00000000006a40ea in execute_sqlcom_select (thd=0x2aaabd11fa08, all_tables=0x2aaacc1f1508) at sql_parse.cc:5102
- 2011-09-15T00:02:01 #21 0x00000000006a61ca in mysql_execute_command (thd=0x2aaabd11fa08) at sql_parse.cc:2247
- 2011-09-15T00:02:01 #22 0x0000000000770e93 in Prepared_statement::execute (this=0x2aaacc220de8, expanded_query=0x4f01dc60, open_cursor=false) at sql_prepare.cc:3735
- 2011-09-15T00:02:01 #23 0x000000000077119e in Prepared_statement::execute_loop (this=0x2aaacc220de8, expanded_query=0x4f01dc60, open_cursor=false, packet=0x0, packet_end=0x0)
- 2011-09-15T00:02:01 at sql_prepare.cc:3416
- 2011-09-15T00:02:01 #24 0x0000000000771457 in mysql_sql_stmt_execute (thd=0x2aaabd11fa08) at sql_prepare.cc:2641
- 2011-09-15T00:02:01 #25 0x00000000006a61f5 in mysql_execute_command (thd=0x2aaabd11fa08) at sql_parse.cc:2256
- 2011-09-15T00:02:01 #26 0x00000000006aef63 in mysql_parse (thd=0x2aaabd11fa08, rawbuf=0x1340a5d0 "EXECUTE prep_stmt_23239 /* TRANSFORM_OUTCOME_UNORDERED_MATCH */", length=63,
- 2011-09-15T00:02:01 found_semicolon=0x4f01ef08) at sql_parse.cc:6102
- 2011-09-15T00:02:01 #27 0x00000000006afaf1 in dispatch_command (command=COM_QUERY, thd=0x2aaabd11fa08,
- 2011-09-15T00:02:01 packet=0x2aaabd0bd8f9 "EXECUTE prep_stmt_23239 /* TRANSFORM_OUTCOME_UNORDERED_MATCH */", packet_length=63) at sql_parse.cc:1221
- 2011-09-15T00:02:01 #28 0x00000000006b112f in do_command (thd=0x2aaabd11fa08) at sql_parse.cc:916
- 2011-09-15T00:02:01 #29 0x000000000069b93f in handle_one_connection (arg=0x2aaabd11fa08) at sql_connect.cc:1187
- 2011-09-15T00:02:01 #30 0x00000033b600673d in start_thread () from /lib64/libpthread.so.0
- 2011-09-15T00:02:01 #31 0x00000033b58d40cd in clone () from /lib64/libc.so.6
test case:
CREATE TABLE t1 ( a int(11) , b int(11) , c int(11) , d varchar(1) ) ;
INSERT INTO t1 VALUES (2,6,3,'w');
CREATE TABLE t2 ( a int(11) , b int(11) , c int(11) , d varchar(1) ) ;
INSERT INTO t2 VALUES (2,9,8,'e');
SET SESSION optimizer_switch='semijoin=ON';
PREPARE st1 FROM "SELECT SQ1_alias1.d
FROM t2 AS SQ1_alias1
RIGHT JOIN t1 AS SQ1_alias2 ON SQ1_alias2.a = SQ1_alias1.b AND SQ1_alias2.d = ANY
(
SELECT d
FROM t1 )
;
";
EXECUTE st1;
EXECUTE st1;
Launchpad bug id: 849717
Re: Crash in Item_func::fix_fields on second execution of a prepared statement with semijoin
The crash happens here:
#0 Item_in_subselect::create_row_in_to_exists_cond (...
#1 0x08268a05 in Item_in_subselect::create_in_to_exists_cond (...
#2 0x0842218d in JOIN::choose_subquery_plan (...
#3 0x0836fec0 in make_join_statistics (...
#4 0x08371701 in JOIN::optimize (...
#5 0x081b9b11 in st_select_lex::optimize_unflattened_subqueries (...
#6 0x08423139 in JOIN::optimize_unflattened_subqueries (...
#7 0x08373355 in JOIN::optimize (...
We execute this code:
new Item_func_eq(new
,
Item_direct_ref(&select_lex->context,
(*optimizer->get_cache())->
addr
(char *)"<no matter>",
(char *)in_left_expr_name),
new
Item_direct_ref(&select_lex->context,
select_lex->
ref_pointer_array+i,
(char *)"<no matter>",
(char *)"<list ref>"));
and during fist execution
)
(gdb) p *((*optimizer->get_cache())->addr
$89 = (Item_cache_str *) 0xb48c0b8
while during the second:
(gdb) p *((*optimizer->get_cache())->addr
)
$93 = (Cannot access memory at address 0x0