Crash or invalid read at st_join_table::cleanup, st_table::disable_keyread with materialization=ON, semijoin=ON

[Elena Stepanova]

Queries similar to the one provided in the test case either cause a crash or valgrind 'Invalid read' errors.
The crash is sporadic and was mainly observed on a non-simplified test case.
Valgrind complaints are persistent.

Stack trace from a crash:

#2 0x00000000006e0125 in handle_segfault (sig=11) at mysqld.cc:2827
#3 <signal handler called>
#4 0x000000000076ce2c in st_table::disable_keyread (this=0x2ffbbf0)
    at table.h:990
#5 0x000000000078821f in st_join_table::cleanup (this=0x3069280)
    at sql_select.cc:9733
#6 0x0000000000776d53 in JOIN::destroy (this=0x300fa60) at sql_select.cc:2788
#7 0x00000000008f3a49 in st_select_lex::cleanup (this=0x2f7f228)
    at sql_union.cc:930
#8 0x00000000008f35fe in st_select_lex_unit::cleanup (this=0x2f9e190)
    at sql_union.cc:795
#9 0x00000000008f3aa3 in st_select_lex::cleanup (this=0x7fab8c0cafd0)
    at sql_union.cc:937
#10 0x00000000007774b6 in mysql_select (thd=0x7fab8c0c8648,
    rref_pointer_array=0x7fab8c0cb220, tables=0x2f7e470, wild_num=0,
    fields=..., conds=0x2fa0d08, og_num=3, order=0x2fa1228, group=0x0,
    having=0x2fa0f78, proc_param=0x0, select_options=2147764736,
    result=0x2fa15b8, unit=0x7fab8c0caae8, select_lex=0x7fab8c0cafd0)
    at sql_select.cc:2987
#11 0x000000000076dd1d in handle_select (thd=0x7fab8c0c8648,
    lex=0x7fab8c0caa48, result=0x2fa15b8, setup_tables_done_option=0)
    at sql_select.cc:283
#12 0x00000000006fce84 in execute_sqlcom_select (thd=0x7fab8c0c8648,
    all_tables=0x2f7e470) at sql_parse.cc:5112
#13 0x00000000006f3f5a in mysql_execute_command (thd=0x7fab8c0c8648)
    at sql_parse.cc:2250
#14 0x00000000006ff864 in mysql_parse (thd=0x7fab8c0c8648,
    rawbuf=0x2f7d720 "SELECT alias1 . `col_int_key` AS field1 , alias1 . `col_varchar_key` AS field2 FROM ( B AS alias1 , C AS alias2 , CC AS alias3 ) WHERE alias2 . `col_varchar_nokey` > SOME ( SELECT DISTINCT SQ1_al"...,
    length=701, found_semicolon=0x7faba8c71c98) at sql_parse.cc:6113
#15 0x00000000006f1772 in dispatch_command (command=COM_QUERY,
    thd=0x7fab8c0c8648, packet=0x7fab8c203ac9 "", packet_length=701)
    at sql_parse.cc:1221
#16 0x00000000006f0aa6 in do_command (thd=0x7fab8c0c8648) at sql_parse.cc:916
#17 0x00000000006ed9fc in handle_one_connection (arg=0x7fab8c0c8648)
    at sql_connect.cc:1191
#18 0x00007faba7f50a4f in start_thread () from /lib64/libpthread.so.0

One of the traces from Valgrind (for the provided test case, it produces 13 of those, the full output is attached):

==2328== Invalid read of size 1
==2328== at 0x831667F: st_table::disable_keyread() (table.h:987)
==2328== by 0x832FADA: st_join_table::cleanup() (sql_select.cc:9733)
==2328== by 0x831F3F7: JOIN::destroy() (sql_select.cc:2788)
==2328== by 0x8484E59: st_select_lex::cleanup() (sql_union.cc:930)
==2328== by 0x8484A6E: st_select_lex_unit::cleanup() (sql_union.cc:795)
==2328== by 0x8484EAE: st_select_lex::cleanup() (sql_union.cc:937)
==2328== by 0x831FB1D: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:2987)
==2328== by 0x83174A9: handle_select(THD*, st_lex*, select_result*, unsigned long) (sql_select.cc:283)
==2328== by 0x82B03DE: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:5112)
==2328== by 0x82A7196: mysql_execute_command(THD*) (sql_parse.cc:2250)
==2328== by 0x82B2A2A: mysql_parse(THD*, char*, unsigned int, char const**) (sql_parse.cc:6113)
==2328== by 0x82A4DC8: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1221)
==2328== by 0x82A421B: do_command(THD*) (sql_parse.cc:916)
==2328== by 0x82A114B: handle_one_connection (sql_connect.cc:1191)
==2328== by 0xBC9AB4: start_thread (in /lib/libpthread-2.11.so)
==2328== by 0xB2083D: clone (in /lib/libc-2.11.so)
==2328== Address 0x5925c98 is 1,688 bytes inside a block of size 2,964 free'd
==2328== at 0x40057F6: free (vg_replace_malloc.c:325)
==2328== by 0x879BACA: _myfree (safemalloc.c:335)
==2328== by 0x879AE29: free_root (my_alloc.c:364)
==2328== by 0x833A991: free_tmp_table(THD*, st_table*) (sql_select.cc:14769)
==2328== by 0x8241B5D: subselect_hash_sj_engine::cleanup() (item_subselect.cc:4396)
==2328== by 0x823748D: Item_subselect::cleanup() (item_subselect.cc:130)
==2328== by 0x823764C: Item_in_subselect::cleanup() (item_subselect.cc:167)
==2328== by 0x832FB97: st_join_table::cleanup() (sql_select.cc:9747)
==2328== by 0x8330552: JOIN::cleanup(bool) (sql_select.cc:10047)
==2328== by 0x8484F51: st_select_lex::cleanup_all_joins(bool) (sql_union.cc:951)
==2328== by 0x8330302: JOIN::join_free() (sql_select.cc:9985)
==2328== by 0x833B099: do_select(JOIN*, List<Item>*, st_table*, Procedure*) (sql_select.cc:14959)
==2328== by 0x831F22F: JOIN::exec() (sql_select.cc:2744)
==2328== by 0x831FA5E: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:2965)
==2328== by 0x83174A9: handle_select(THD*, st_lex*, select_result*, unsigned long) (sql_select.cc:283)
==2328== by 0x82B03DE: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:5112)

revision-id: <email address hidden>
date: 2011-12-09 14:30:50 -0800
build-date: 2011-12-11 00:53:46 +0300
revno: 3339
branch-nick: maria-5.3

Crash was also observed on older versions, including 5.3.2 release, with materialization=on,semijoin=on.

Minimal optimizer_switch: not required for the current 5.3-main (materialization=on,semijoin=on by default)

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=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

EXPLAIN output:

1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2100.00 Using where; Using temporary
2 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 5 test.t2.b 1 100.00 Using where; Distinct
3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 Using temporary

Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(exists(select distinct `test`.`t2`.`b` from <materialize> (select `test`.`t3`.`c` from `test`.`t3` group by `test`.`t3`.`c`) join `test`.`t2` where ((`<subquery3>`.`c` = `test`.`t2`.`b`) and (`test`.`t2`.`b` <= `test`.`t1`.`a`))))

Test case (if it doesn't produce a crash, run with valgrind):

# These are defaults now, but setting them for older versions
SET optimizer_switch='materialization=on,semijoin=on';

CREATE TABLE t1 ( a INT );
INSERT INTO t1 VALUES (1), (2);
CREATE TABLE t2 ( b INT );
INSERT INTO t2 VALUES (3), (4);
CREATE TABLE t3 ( c INT );
INSERT INTO t3 VALUES (5), (6);

SELECT * FROM t1 WHERE EXISTS (
   SELECT DISTINCT b FROM t2
      WHERE b <= a
        AND b IN ( SELECT c FROM t3 GROUP BY c )
   );

[Elena Stepanova (elenst)]

Fix released with 5.3.3-rc.