Elena Stepanova added a comment - 2011-12-08 15:16 Assertion `tmp > 0 || record_count == 0' failed at best_access_path with semijoin_with_cache=on and STRAIGHT_JOIN #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: psergey@askmonty.org-20111208002238-zeewdrj61uqiscwo 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 );

Rasmus Johansson added a comment - 2011-12-13 16:27 Launchpad bug id: 901709