mysqld: opt_subselect.cc:4865: bool JOIN::choose_subquery_plan(table_map): Assertion `(0)' failed.

#6 0x00007f1750279235 in __assert_fail () from /lib64/libc.so.6
#7 0x000000000085cdf1 in JOIN::choose_subquery_plan (this=0x1cdaf20,
join_tables=2) at opt_subselect.cc:4865
#8 0x0000000000779b3a in make_join_statistics (join=0x1cdaf20,
tables_list=..., conds=0x0, keyuse_array=0x1ce2dd8) at sql_select.cc:3553
#9 0x0000000000771153 in JOIN::optimize (this=0x1cdaf20) at sql_select.cc:1119
#10 0x000000000068397f in subselect_single_select_engine::exec (this=0x1cc1c28)
at item_subselect.cc:3100
#11 0x000000000067bf13 in Item_subselect::exec (this=0x1ce35b8)
at item_subselect.cc:589
#12 0x000000000067c484 in Item_in_subselect::exec (this=0x1ce35b8)
at item_subselect.cc:747
#13 0x000000000067e3e7 in Item_in_subselect::val_bool (this=0x1ce35b8)
at item_subselect.cc:1467
#14 0x0000000000604b4d in Item::val_bool_result (this=0x1ce35b8) at item.h:843
#15 0x00000000006396a4 in Item_in_optimizer::val_int (this=0x1ce3228)
at item_cmpfunc.cc:1736
#16 0x0000000000604ac1 in Item::val_int_result (this=0x1ce3228) at item.h:839
#17 0x00000000005fef9f in Item_cache_int::cache_value (this=0x1cea308)
at item.cc:8228
#18 0x000000000060a114 in Item_cache_wrapper::cache (this=0x1cea250)
at item.cc:7061
#19 0x00000000005fc336 in Item_cache_wrapper::val_bool (this=0x1cea250)
at item.cc:7225
#20 0x0000000000642f01 in Item_cond_or::val_int (this=0x1ce67c0)
at item_cmpfunc.cc:4536
#21 0x0000000000795e45 in evaluate_join_record (join=0x1cd2e00,
join_tab=0x1ce58f8, error=0) at sql_select.cc:15255
#22 0x0000000000795a21 in sub_select (join=0x1cd2e00, join_tab=0x1ce58f8,
end_of_records=false) at sql_select.cc:15160
#23 0x00000000007951ef in do_select (join=0x1cd2e00, fields=0x1c2a2e8,
table=0x0, procedure=0x0) at sql_select.cc:14821
#24 0x000000000077725e in JOIN::exec (this=0x1cd2e00) at sql_select.cc:2688
#25 0x0000000000777af1 in mysql_select (thd=0x1c27858,
rref_pointer_array=0x1c2a430, tables=0x1cc0618, wild_num=1, fields=...,
conds=0x1cc1f78, og_num=0, order=0x0, group=0x0, having=0x0,
proc_param=0x0, select_options=2147764736, result=0x1cc2120,
unit=0x1c29cf8, select_lex=0x1c2a1e0) at sql_select.cc:2909
#26 0x000000000076e60d in handle_select (thd=0x1c27858, lex=0x1c29c58,
result=0x1cc2120, setup_tables_done_option=0) at sql_select.cc:283
#27 0x00000000006fd7ec in execute_sqlcom_select (thd=0x1c27858,
all_tables=0x1cc0618) at sql_parse.cc:5112
#28 0x00000000006f48c2 in mysql_execute_command (thd=0x1c27858)
at sql_parse.cc:2250
#29 0x00000000007001cc in mysql_parse (thd=0x1c27858,
rawbuf=0x1cc03d0 "SELECT * FROM t1 WHERE\nEXISTS ( SELECT * FROM t2 WHERE b = a )\nOR a > 0", length=71, found_semicolon=0x7f1747796c98)
at sql_parse.cc:6113
#30 0x00000000006f20da in dispatch_command (command=COM_QUERY, thd=0x1c27858,
packet=0x1ca2e39 "", packet_length=71) at sql_parse.cc:1221
#31 0x00000000006f140e in do_command (thd=0x1c27858) at sql_parse.cc:916
#32 0x00000000006ee364 in handle_one_connection (arg=0x1c27858)
at sql_connect.cc:1191
#33 0x00007f1750f34a4f in start_thread () from /lib64/libpthread.so.0
#34 0x00007f175031e82d in clone () from /lib64/libc.so.6

bzr version-info
revision-id: <email address hidden>
date: 2011-12-07 15:40:41 +0200
build-date: 2011-12-08 21:24:59 +0200
revno: 3330
branch-nick: 5.3-exists2in

Minimal optimizer_switch: exists_to_in=on (included into the test case).

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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,exists_to_in=on

EXPLAIN output:

1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 100.00
2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00

Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in (select `test`.`t2`.`b` from `test`.`t2` where (1 = 1)))) or (`test`.`t1`.`a` > 0)))

Test case:

SET optimizer_switch='exists_to_in=on';

CREATE TABLE t1 ( a INT );
INSERT INTO t1 VALUES (7),(0);
CREATE TABLE t2 ( b INT );
INSERT INTO t2 VALUES (0),(8);

SELECT * FROM t1 WHERE
EXISTS ( SELECT * FROM t2 WHERE b = a )
OR a > 0;