Assertion `tmp > 0 || record_count == 0' failed at best_access_path with semijoin_with_cache=on and STRAIGHT_JOIN

[Elena Stepanova]

mysqld: sql_select.cc:5426: void best_access_path(JOIN*, JOIN_TAB*, table_map, uint, bool, double, POSITION*, POSITION*): Assertion `tmp > 0 || record_count == 0' failed.

#6 0x00007fb374daf235 in __assert_fail () from /lib64/libc.so.6
#7 0x000000000077d98f in best_access_path (join=0x1d52a00, s=0x1d62c40,
    remaining_tables=1, idx=3, disable_jbuf=false,
    record_count=-nan(0x8000000000000), pos=0x1d56bc8,
    loose_scan_pos=0x7fb36c2ca9a0) at sql_select.cc:5426
#8 0x000000000077f7e3 in best_extension_by_limited_search (join=0x1d52a00,
    remaining_tables=1, idx=3, record_count=-nan(0x8000000000000),
    read_time=3, search_depth=60, prune_level=0) at sql_select.cc:6425
#9 0x000000000077fb64 in best_extension_by_limited_search (join=0x1d52a00,
    remaining_tables=9, idx=2, record_count=1, read_time=1, search_depth=61,
    prune_level=0) at sql_select.cc:6488
#10 0x000000000077fb64 in best_extension_by_limited_search (join=0x1d52a00,
    remaining_tables=11, idx=1, record_count=1, read_time=0, search_depth=62,
    prune_level=0) at sql_select.cc:6488
#11 0x000000000077ed8a in greedy_search (join=0x1d52a00, remaining_tables=11,
    search_depth=62, prune_level=0) at sql_select.cc:6043
#12 0x000000000077e5d9 in choose_plan (join=0x1d52a00, join_tables=11)
    at sql_select.cc:5704
#13 0x0000000000778b7a in make_join_statistics (join=0x1d52a00,
    tables_list=..., conds=0x1d3e2b8, keyuse_array=0x1d5a8b8)
    at sql_select.cc:3592
#14 0x00000000007702f0 in JOIN::optimize (this=0x1d52a00) at sql_select.cc:1175
#15 0x0000000000776c07 in mysql_select (thd=0x1c25818,
    rref_pointer_array=0x1c283f0, tables=0x1caa100, wild_num=1, fields=...,
    conds=0x1d3d2f8, og_num=0, order=0x0, group=0x0, having=0x0,
    proc_param=0x0, select_options=2147764736, result=0x1cabde0,
    unit=0x1c27cb8, select_lex=0x1c281a0) at sql_select.cc:2951
#16 0x000000000076d5dd in handle_select (thd=0x1c25818, lex=0x1c27c18,
    result=0x1cabde0, setup_tables_done_option=0) at sql_select.cc:283
#17 0x00000000006fc744 in execute_sqlcom_select (thd=0x1c25818,
    all_tables=0x1caa100) at sql_parse.cc:5112
#18 0x00000000006f381a in mysql_execute_command (thd=0x1c25818)
    at sql_parse.cc:2250
#19 0x00000000006ff124 in mysql_parse (thd=0x1c25818,
    rawbuf=0x1ca9e40 "SELECT * FROM t1 AS alias1, t1 AS alias2 \nWHERE alias1.a = alias2.a \nAND alias2.a IN ( SELECT b FROM t2 STRAIGHT_JOIN t3 )",
    length=122, found_semicolon=0x7fb36c2ccc98) at sql_parse.cc:6113
#20 0x00000000006f1032 in dispatch_command (command=COM_QUERY, thd=0x1c25818,
    packet=0x1ca0b09 "SELECT * FROM t1 AS alias1, t1 AS alias2 \nWHERE alias1.a = alias2.a \nAND alias2.a IN ( SELECT b FROM t2 STRAIGHT_JOIN t3 )",
    packet_length=122) at sql_parse.cc:1221
#21 0x00000000006f0366 in do_command (thd=0x1c25818) at sql_parse.cc:916
#22 0x00000000006ed2bc in handle_one_connection (arg=0x1c25818)
    at sql_connect.cc:1191
#23 0x00007fb375a6aa4f in start_thread () from /lib64/libpthread.so.0
#24 0x00007fb374e5482d in clone () from /lib64/libc.so.6

bzr version-info
revision-id: <email address hidden>
date: 2011-12-08 04:22:38 +0400
build-date: 2011-12-08 17:10:52 +0200
revno: 3337
branch-nick: maria-5.3

Not reproducible on 3315.

Minimal switch: semijoin_with_cache=on

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

optimizer_prune_level=0 is also required for the test case (included)

t2 and t3 need to be Aria or MyISAM.

EXPLAIN also fails.

Test case:

SET optimizer_switch='semijoin_with_cache=on';
SET optimizer_prune_level=0;

CREATE TABLE t1 ( a INT, KEY (a) );
INSERT INTO t1 VALUES (4),(6);
CREATE TABLE t2 ( b INT ) ENGINE=Aria;
INSERT INTO t2 VALUES (4);
CREATE TABLE t3 ( c INT ) ENGINE=Aria;

# t2 and t3 need to be Aria or MyISAM

SELECT * FROM t1 AS alias1, t1 AS alias2
 WHERE alias1.a = alias2.a
  AND alias2.a IN ( SELECT b FROM t2 STRAIGHT_JOIN t3 );