Assertion join->best_read < ((double)1.79...e+308L) failed at bool greedy_search(JOIN*, table_map, uint, uint) with semijoin_with_cache=on and STRAIGHT_JOIN

[Elena Stepanova]

mysqld: sql_select.cc:6050: bool greedy_search(JOIN*, table_map, uint, uint): Assertion `join->best_read < ((double)1.79769313486231570815e+308L)' failed.

#6 0x00007fb9af92a235 in __assert_fail () from /lib64/libc.so.6
#7 0x000000000077edf5 in greedy_search (join=0x1d41880, remaining_tables=4,
    search_depth=62, prune_level=1) at sql_select.cc:6050
#8 0x000000000077e5d9 in choose_plan (join=0x1d41880, join_tables=4)
    at sql_select.cc:5704
#9 0x0000000000778b7a in make_join_statistics (join=0x1d41880,
    tables_list=..., conds=0x1d4a618, keyuse_array=0x1d49738)
    at sql_select.cc:3592
#10 0x00000000007702f0 in JOIN::optimize (this=0x1d41880) at sql_select.cc:1175
#11 0x0000000000776c07 in mysql_select (thd=0x1c244d8,
    rref_pointer_array=0x1c270b0, tables=0x1caa130, wild_num=1, fields=...,
    conds=0x1cabc30, og_num=0, order=0x0, group=0x0, having=0x0,
    proc_param=0x0, select_options=2147764736, result=0x1cabec0,
    unit=0x1c26978, select_lex=0x1c26e60) at sql_select.cc:2951
#12 0x000000000076d5dd in handle_select (thd=0x1c244d8, lex=0x1c268d8,
    result=0x1cabec0, setup_tables_done_option=0) at sql_select.cc:283
#13 0x00000000006fc744 in execute_sqlcom_select (thd=0x1c244d8,
    all_tables=0x1caa130) at sql_parse.cc:5112
#14 0x00000000006f381a in mysql_execute_command (thd=0x1c244d8)
    at sql_parse.cc:2250
#15 0x00000000006ff124 in mysql_parse (thd=0x1c244d8,
    rawbuf=0x1ca9ef0 "SELECT * FROM t1 WHERE a IN ( SELECT b FROM t2 STRAIGHT_JOIN t3 )", length=65, found_semicolon=0x7fb9a6e47c98) at sql_parse.cc:6113
#16 0x00000000006f1032 in dispatch_command (command=COM_QUERY, thd=0x1c244d8,
    packet=0x1ca0a89 "SELECT * FROM t1 WHERE a IN ( SELECT b FROM t2 STRAIGHT_JOIN t3 )", packet_length=65) at sql_parse.cc:1221
#17 0x00000000006f0366 in do_command (thd=0x1c244d8) at sql_parse.cc:916
#18 0x00000000006ed2bc in handle_one_connection (arg=0x1c244d8)
    at sql_connect.cc:1191
#19 0x00007fb9b05e5a4f in start_thread () from /lib64/libpthread.so.0
#20 0x00007fb9af9cf82d 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 15:50:11 +0200
revno: 3337
branch-nick: maria-5.3

Not reproducible on 3315.

Minimal switch: semijoin_with_cache=on (included into the test case)

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

t3 needs to be Aria or MyISAM, but not InnoDB.

EXPLAIN also fails.

Test case:

SET optimizer_switch='semijoin_with_cache=on';

CREATE TABLE t1 ( a INT, UNIQUE KEY(a) );
INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2 ( b INT );
INSERT INTO t2 VALUES (7);
CREATE TABLE t3 ( c INT ) ENGINE=Aria;

SELECT * FROM t1 WHERE a IN ( SELECT b FROM t2 STRAIGHT_JOIN t3 );

[Igor Babaev (igorb-seattle)]

The cause of this bug is the same as the cause of bug #901709.

[Igor Babaev (igorb-seattle)]

So the patch for bug #901709 fixes this bug as well.