Crash in do_copy_not_null with prepared statement, outer join , join_cache_level > 3

[Philip Stoev]

If this query

        SELECT *
        FROM ( t2 LEFT JOIN t1 ON t1.b = t2.b )
        JOIN t3 ON t1.b = t3.b

is executed twice as a prepared statement with join_cache_level>3 , the server crashes as follows:

#4 <signal handler called>
#5 0x08424c0a in do_copy_not_null (copy=0xae514304) at field_conv.cc:253
#6 0x0834ccc8 in store_key_field::copy_inner (this=0xae5142f0) at sql_select.h:1328
#7 0x082528f8 in store_key::copy (this=0xae5142f0) at sql_select.h:1269
#8 0x083439f9 in cp_buffer_from_ref (thd=0x9f126b8, table=0xae631e48, ref=0xae5140fc) at sql_select.cc:18550
#9 0x082f1ff6 in JOIN_CACHE_HASHED::put_record (this=0xae532b40) at sql_join_cache.cc:2848
#10 0x0833b10c in sub_select_cache (join=0xae52b668, join_tab=0xae513f98, end_of_records=false) at sql_select.cc:14862
#11 0x082f13d0 in JOIN_CACHE::generate_full_extensions (this=0xae532988, rec_ptr=0xae535b45 "") at sql_join_cache.cc:2339
#12 0x082f118a in JOIN_CACHE::join_matching_records (this=0xae532988, skip_last=false) at sql_join_cache.cc:2232
#13 0x082f0cec in JOIN_CACHE::join_records (this=0xae532988, skip_last=false) at sql_join_cache.cc:2031
#14 0x0833b042 in sub_select_cache (join=0xae52b668, join_tab=0xae513d94, end_of_records=true) at sql_select.cc:14849
#15 0x0833b25a in sub_select (join=0xae52b668, join_tab=0xae513b90, end_of_records=true) at sql_select.cc:15011
#16 0x0833ad49 in do_select (join=0xae52b668, fields=0xae529a10, table=0x0, procedure=0x0) at sql_select.cc:14735
#17 0x0831f7cb in JOIN::exec (this=0xae52b668) at sql_select.cc:2680
#18 0x0831fff8 in mysql_select (thd=0x9f126b8, rref_pointer_array=0xae529ab8, tables=0xae52a418, wild_num=0, fields=..., conds=0xae512fb0, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416200192, result=0xae52b5c0, unit=0xae52969c, select_lex=0xae52997c)
    at sql_select.cc:2901
#19 0x08317deb in handle_select (thd=0x9f126b8, lex=0xae529640, result=0xae52b5c0, setup_tables_done_option=0) at sql_select.cc:283
#20 0x082b31b9 in execute_sqlcom_select (thd=0x9f126b8, all_tables=0xae52a418) at sql_parse.cc:5090
#21 0x082a9fbd in mysql_execute_command (thd=0x9f126b8) at sql_parse.cc:2234
#22 0x08362468 in Prepared_statement::execute (this=0xae525558, expanded_query=0xae8e070c, open_cursor=false) at sql_prepare.cc:3736
#23 0x08361983 in Prepared_statement::execute_loop (this=0xae525558, expanded_query=0xae8e070c, open_cursor=false, packet=0x0, packet_end=0x0)
    at sql_prepare.cc:3417
#24 0x08360253 in mysql_sql_stmt_execute (thd=0x9f126b8) at sql_prepare.cc:2642
#25 0x082a9fe6 in mysql_execute_command (thd=0x9f126b8) at sql_parse.cc:2243
#26 0x082b57f5 in mysql_parse (thd=0x9f126b8, rawbuf=0xae512ec0 "EXECUTE st1", length=11, found_semicolon=0xae8e1228) at sql_parse.cc:6091
#27 0x082a7c3a in dispatch_command (command=COM_QUERY, thd=0x9f126b8, packet=0x9f6b549 "", packet_length=11) at sql_parse.cc:1211
#28 0x082a7095 in do_command (thd=0x9f126b8) at sql_parse.cc:906
#29 0x082a40fd in handle_one_connection (arg=0x9f126b8) at sql_connect.cc:1186
#30 0x00821919 in start_thread () from /lib/libpthread.so.0
#31 0x0076acce in clone () from /lib/libc.so.6

or:

#4 <signal handler called>
#5 0x08424c0a in do_copy_not_null (copy=0xae713a64) at field_conv.cc:253
#6 0x0834ccc8 in store_key_field::copy_inner (this=0xae713a50) at sql_select.h:1328
#7 0x082528f8 in store_key::copy (this=0xae713a50) at sql_select.h:1269
#8 0x083439f9 in cp_buffer_from_ref (thd=0xa09b6b8, table=0xae721be0, ref=0xae71385c) at sql_select.cc:18550
#9 0x0834394c in cmp_buffer_with_ref (thd=0xa09b6b8, table=0xae721be0, tab_ref=0xae71385c) at sql_select.cc:18532
#10 0x0833c898 in join_read_key2 (thd=0xa09b6b8, tab=0xae7136f8, table=0xae721be0, table_ref=0xae71385c) at sql_select.cc:15645
#11 0x0833c82e in join_read_key (tab=0xae7136f8) at sql_select.cc:15628
#12 0x0833b477 in sub_select (join=0xae72b6f0, join_tab=0xae7136f8, end_of_records=false) at sql_select.cc:15065
#13 0x0833bb67 in evaluate_join_record (join=0xae72b6f0, join_tab=0xae7134f4, error=0) at sql_select.cc:15265
#14 0x0833b4ca in sub_select (join=0xae72b6f0, join_tab=0xae7134f4, end_of_records=false) at sql_select.cc:15068
#15 0x0833bb67 in evaluate_join_record (join=0xae72b6f0, join_tab=0xae7132f0, error=0) at sql_select.cc:15265
#16 0x0833b4ca in sub_select (join=0xae72b6f0, join_tab=0xae7132f0, end_of_records=false) at sql_select.cc:15068
#17 0x0833ad1d in do_select (join=0xae72b6f0, fields=0xae729a98, table=0x0, procedure=0x0) at sql_select.cc:14733
#18 0x0831f7cb in JOIN::exec (this=0xae72b6f0) at sql_select.cc:2680
#19 0x0831fff8 in mysql_select (thd=0xa09b6b8, rref_pointer_array=0xae729b40, tables=0xae72a268, wild_num=0, fields=..., conds=0xae712710, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416200192, result=0xae72b428, unit=0xae729724, select_lex=0xae729a04)
    at sql_select.cc:2901
#20 0x08317deb in handle_select (thd=0xa09b6b8, lex=0xae7296c8, result=0xae72b428, setup_tables_done_option=0) at sql_select.cc:283
#21 0x082b31b9 in execute_sqlcom_select (thd=0xa09b6b8, all_tables=0xae72a268) at sql_parse.cc:5090
#22 0x082a9fbd in mysql_execute_command (thd=0xa09b6b8) at sql_parse.cc:2234
#23 0x08362468 in Prepared_statement::execute (this=0xae7255f8, expanded_query=0xae8fe70c, open_cursor=false) at sql_prepare.cc:3736
#24 0x08361983 in Prepared_statement::execute_loop (this=0xae7255f8, expanded_query=0xae8fe70c, open_cursor=false, packet=0x0, packet_end=0x0)
    at sql_prepare.cc:3417
#25 0x08360253 in mysql_sql_stmt_execute (thd=0xa09b6b8) at sql_prepare.cc:2642
#26 0x082a9fe6 in mysql_execute_command (thd=0xa09b6b8) at sql_parse.cc:2243
#27 0x082b57f5 in mysql_parse (thd=0xa09b6b8, rawbuf=0xae7125f0 "EXECUTE st1", length=11, found_semicolon=0xae8ff228) at sql_parse.cc:6091
#28 0x082a7c3a in dispatch_command (command=COM_QUERY, thd=0xa09b6b8, packet=0xa0f4549 "", packet_length=11) at sql_parse.cc:1211
#29 0x082a7095 in do_command (thd=0xa09b6b8) at sql_parse.cc:906
#30 0x082a40fd in handle_one_connection (arg=0xa09b6b8) at sql_connect.cc:1186
#31 0x00821919 in start_thread () from /lib/libpthread.so.0
#32 0x00453cce in clone () from /lib/libc.so.6

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
1 SIMPLE t3 hash_ALL PRIMARY #hash#PRIMARY 4 test.t1.b 10 Using join buffer (flat, BNLH join)
1 SIMPLE t2 hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t1.b 26 Using join buffer (incremental, BNLH join)

minimal switches: join_cache_level=4

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,materialization=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,optimize_join_buffer_size=off,table_elimination=on

bzr version-info:

revision-id: <email address hidden>
date: 2011-08-29 18:16:18 +0300
build-date: 2011-09-01 10:50:00 +0300
revno: 3169
branch-nick: maria-5.3

test case:

SET SESSION join_cache_level=4;

CREATE TABLE t1 ( b int NOT NULL ) ;
INSERT INTO t1 VALUES (9),(10);

CREATE TABLE t2 ( b int NOT NULL, PRIMARY KEY (b)) ;
INSERT INTO t2 VALUES (75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100);

CREATE TABLE t3 ( a int, b int NOT NULL , PRIMARY KEY (b)) ;
INSERT INTO t3 VALUES (0,6),(0,7),(0,8),(2,9),(0,10),(2,21),(0,22),(2,23),(2,24),(2,25);

PREPARE st1 FROM '
        SELECT *
        FROM ( t2 LEFT JOIN t1 ON t1.b = t2.b )
        JOIN t3 ON t1.b = t3.b
';

EXECUTE st1;
EXECUTE st1;

[Sergey Petrunia (sergefp)]

Igor has pushed fix for this today