Crash in check_and_do_in_subquery_rewrites() with corrlated subquery in select list

[Philip Stoev]

The second execution of the following query as a stored procedure crashes:

SELECT (
        SELECT t1.a
        FROM t1
        WHERE t1.b = t3.b
        AND t1.b IN ( SELECT a FROM t2 )
)
FROM t3
GROUP BY 1;

backtrace:

#3 <signal handler called>
#4 0x000000000080af35 in check_and_do_in_subquery_rewrites (join=0x1104d640) at opt_subselect.cc:293
#5 0x000000000074f35f in JOIN::prepare (this=0x1104d640, rref_pointer_array=0x10ff2a10, tables_init=0x10ff3148, wild_num=0, conds_init=0x0, og_num=0,
    order_init=0x0, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x10ff27c0, unit_arg=0x10ff2b00) at sql_select.cc:607
#6 0x0000000000633431 in subselect_single_select_engine::prepare (this=0x10ff36d8) at item_subselect.cc:2737
#7 0x00000000006383b5 in Item_subselect::fix_fields (this=0x10ff3530, thd_param=0x10f2d108, ref=0x11039b40) at item_subselect.cc:233
#8 0x000000000063898d in Item_in_subselect::fix_fields (this=0x10ff3530, thd_arg=0x10f2d108, ref=0x11039b40) at item_subselect.cc:2450
#9 0x00000000005f13cb in Item_in_optimizer::fix_fields (this=0x11039aa8, thd=0x10f2d108, ref=0x1103f400) at item_cmpfunc.cc:1448
#10 0x00000000005eee03 in Item_cond::fix_fields (this=0x1103f2d8, thd=0x10f2d108, ref=0x1104d4e0) at item_cmpfunc.cc:4106
#11 0x00000000006f9c68 in setup_conds (thd=0x10f2d108, tables=0x10ff1f08, leaves=..., conds=0x1104d4e0) at sql_base.cc:8333
#12 0x00000000007597b9 in setup_without_group (thd=0x10f2d108, ref_pointer_array=0x110398f8, tables=0x10ff1f08, leaves=..., fields=..., all_fields=...,
    conds=0x1104d4e0, order=0x0, group=0x0, hidden_group_fields=0x1104d3d7) at sql_select.cc:449
#13 0x000000000074f061 in JOIN::prepare (this=0x11047420, rref_pointer_array=0x10ff1758, tables_init=0x10ff1f08, wild_num=0, conds_init=0x1103f2d8,
    og_num=0, order_init=0x0, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x10ff1508, unit_arg=0x10ff1848) at sql_select.cc:572
#14 0x0000000000633431 in subselect_single_select_engine::prepare (this=0x10ff3988) at item_subselect.cc:2737
#15 0x00000000006383b5 in Item_subselect::fix_fields (this=0x10ff3848, thd_param=0x10f2d108, ref=0x10ff39d0) at item_subselect.cc:233
#16 0x00000000006fdac3 in setup_fields (thd=0x10f2d108, ref_pointer_array=0x110397b8, fields=..., mark_used_columns=MARK_COLUMNS_READ,
    sum_func_list=0x110471d8, allow_sum_func=true) at sql_base.cc:7672
#17 0x000000000074efa6 in JOIN::prepare (this=0x11041200, rref_pointer_array=0x10ff0c30, tables_init=0x11039200, wild_num=0, conds_init=0x0, og_num=1,
    order_init=0x0, group_init=0x10ff3ba0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x10ff09e0, unit_arg=0x10ff04f8) at sql_select.cc:572
#18 0x0000000000750088 in mysql_select (thd=0x10f2d108, rref_pointer_array=0x10ff0c30, tables=0x11039200, wild_num=0, fields=..., conds=0x0, og_num=1,
    order=0x0, group=0x10ff3ba0, having=0x0, proc_param=0x0, select_options=2147765760, result=0x1103f4b0, unit=0x10ff04f8, select_lex=0x10ff09e0)
    at sql_select.cc:2869
#19 0x0000000000756564 in handle_select (thd=0x10f2d108, lex=0x10ff0458, result=0x1103f4b0, setup_tables_done_option=0) at sql_select.cc:283
#20 0x00000000006a312e in execute_sqlcom_select (thd=0x10f2d108, all_tables=0x11039200) at sql_parse.cc:5087
#21 0x00000000006a4e0c in mysql_execute_command (thd=0x10f2d108) at sql_parse.cc:2231
#22 0x00000000008bbb8f in sp_instr_stmt::exec_core (this=0x10ff3be0, thd=0x10f2d108, nextp=0x412adaa8) at sp_head.cc:2947
#23 0x00000000008bc57f in sp_lex_keeper::reset_lex_and_exec_core (this=0x10ff3c20, thd=0x10f2d108, nextp=0x412adaa8, open_tables=false, instr=0x10ff3be0)
    at sp_head.cc:2765
#24 0x00000000008bcd1e in sp_instr_stmt::execute (this=0x10ff3be0, thd=0x10f2d108, nextp=0x412adaa8) at sp_head.cc:2890
#25 0x00000000008bedc3 in sp_head::execute (this=0x10fefd80, thd=0x10f2d108) at sp_head.cc:1270
#26 0x00000000008bfb53 in sp_head::execute_procedure (this=0x10fefd80, thd=0x10f2d108, args=0x10f30008) at sp_head.cc:2002
#27 0x00000000006ab9ea in mysql_execute_command (thd=0x10f2d108) at sql_parse.cc:4416
#28 0x00000000006adba5 in mysql_parse (thd=0x10f2d108, rawbuf=0x10fb2d40 "CALL sp1()", length=10, found_semicolon=0x412aef08) at sql_parse.cc:6088
#29 0x00000000006aea3d in dispatch_command (command=COM_QUERY, thd=0x10f2d108, packet=0x10fa99c9 "CALL sp1()", packet_length=10) at sql_parse.cc:1208
#30 0x00000000006b004b in do_command (thd=0x10f2d108) at sql_parse.cc:906
#31 0x000000000069a9eb in handle_one_connection (arg=0x10f2d108) at sql_connect.cc:1178
#32 0x00000033b600673d in start_thread () from /lib64/libpthread.so.0
#33 0x00000033b58d40cd in clone () from /lib64/libc.so.6

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 system NULL NULL NULL NULL 1
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where

does not appear to require any particular optimizer switches. 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

test case:

CREATE TABLE t1 (a int, b int);
INSERT INTO t1 VALUES (10,1),(11,7);

CREATE TABLE t2 (a int);
INSERT INTO t2 VALUES (2),(3);

CREATE TABLE t3 (a int, b int);
INSERT INTO t3 VALUES (1,1);

CREATE PROCEDURE sp1 () LANGUAGE SQL
SELECT (
        SELECT t1.a
        FROM t1
        WHERE t1.b = t3.b
        AND t1.b IN ( SELECT a FROM t2 )
)
FROM t3
GROUP BY 1;

CALL sp1();
CALL sp1();

Seems to require that t3 contains exactly 1 row. Reproducible in maria-5.3. Not reproducible on maria-5.2, mysql-5.5 .

[Timour Katchaounov (timour)]

Unlike other similar bugs, in this case it is essential to execute
the query inside a stored procedure. The crash is not reproducible
inside a prepared statement.