Assertion `keypart_map' failed in mi_rkey with semijoin

[Philip Stoev]

mysqld: mi_rkey.c:58: mi_rkey: Assertion `keypart_map' failed.

#8 0x006b0d98 in __assert_fail () from /lib/libc.so.6
#9 0x084da6b7 in mi_rkey (info=0xae62a2e0, buf=0xae6298b0 "\374\003", inx=0, key=0xae661a00 "", keypart_map=0, search_flag=HA_READ_KEY_EXACT)
    at mi_rkey.c:58
#10 0x084d55df in ha_myisam::index_read_map (this=0xae6293b8, buf=0xae6298b0 "\374\003", key=0xae661a00 "", keypart_map=0, find_flag=HA_READ_KEY_EXACT)
    at ha_myisam.cc:1796
#11 0x081bbfee in handler::ha_index_read_map (this=0xae6293b8, buf=0xae6298b0 "\374\003", key=0xae661a00 "", keypart_map=0, find_flag=HA_READ_KEY_EXACT)
    at sql_class.h:3655
#12 0x0833eb40 in join_read_always_key (tab=0xae6617e8) at sql_select.cc:15771
#13 0x0833d3ed in sub_select (join=0xae635280, join_tab=0xae6617e8, end_of_records=false) at sql_select.cc:15097
#14 0x083e4bef in join_tab_execution_startup (tab=0xae6615d4) at opt_subselect.cc:4399
#15 0x0833d37a in sub_select (join=0xae635280, join_tab=0xae6615d4, end_of_records=false) at sql_select.cc:15088
#16 0x0833cc93 in do_select (join=0xae635280, fields=0xae612e0c, table=0x0, procedure=0x0) at sql_select.cc:14763
#17 0x08321669 in JOIN::exec (this=0xae635280) at sql_select.cc:2679
#18 0x0824c4fb in subselect_single_select_engine::exec (this=0xae614230) at item_subselect.cc:2968
#19 0x08246247 in Item_subselect::exec (this=0xae614128) at item_subselect.cc:572
#20 0x082466ee in Item_in_subselect::exec (this=0xae614128) at item_subselect.cc:725
#21 0x08248242 in Item_in_subselect::val_bool (this=0xae614128) at item_subselect.cc:1438
#22 0x081e2064 in Item::val_bool_result (this=0xae614128) at item.h:842
#23 0x0820e462 in Item_in_optimizer::val_int (this=0xae614518) at item_cmpfunc.cc:1717
#24 0x081ca2cc in Item::val_bool (this=0xae614518) at item.cc:197
#25 0x0820b010 in Item_func_not::val_int (this=0xae614258) at item_cmpfunc.cc:333
#26 0x0833d7d5 in evaluate_join_record (join=0xae62b7d0, join_tab=0xae6314d8, error=0) at sql_select.cc:15195
#27 0x0833d588 in sub_select (join=0xae62b7d0, join_tab=0xae6314d8, end_of_records=false) at sql_select.cc:15140
#28 0x0833cc93 in do_select (join=0xae62b7d0, fields=0xad601b4, table=0x0, procedure=0x0) at sql_select.cc:14763
#29 0x08321669 in JOIN::exec (this=0xae62b7d0) at sql_select.cc:2679
#30 0x08321e96 in mysql_select (thd=0xad5e6e0, rref_pointer_array=0xad6025c, tables=0xae612a80, wild_num=0, fields=..., conds=0xae614258, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416200192, result=0xae6142f0, unit=0xad5fe40, select_lex=0xad60120)
    at sql_select.cc:2900
#31 0x08319c67 in handle_select (thd=0xad5e6e0, lex=0xad5fde4, result=0xae6142f0, setup_tables_done_option=0) at sql_select.cc:283
#32 0x082ac5f9 in mysql_execute_command (thd=0xad5e6e0) at sql_parse.cc:2740
#33 0x082b6a63 in mysql_parse (thd=0xad5e6e0,
    rawbuf=0xae6125f0 "CREATE TABLE cr1\nSELECT f3\nFROM t1 WHERE ( f3 ) NOT IN (\nSELECT f3 \nFROM t2\nWHERE f5 IN (\nSELECT f4\nFROM t3\nWHERE t3.f3 < 3\n)\n)", length=127, found_semicolon=0xae7ff228) at sql_parse.cc:6110
#34 0x082a8e40 in dispatch_command (command=COM_QUERY, thd=0xad5e6e0,
    packet=0xadb7321 "CREATE TABLE cr1\nSELECT f3\nFROM t1 WHERE ( f3 ) NOT IN (\nSELECT f3 \nFROM t2\nWHERE f5 IN (\nSELECT f4\nFROM t3\nWHERE t3.f3 < 3\n)\n)", packet_length=127) at sql_parse.cc:1221
#35 0x082a829b in do_command (thd=0xad5e6e0) at sql_parse.cc:916
#36 0x082a527f in handle_one_connection (arg=0xad5e6e0) at sql_connect.cc:1191
#37 0x00821919 in start_thread () from /lib/libpthread.so.0
#38 0x0076acce in clone () from /lib/libc.so.6

minimal optimzer switch for this test case: semijoin=ON,materialization=ON

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

also observed with a different query and this switch: semijoin=ON,loosescan=ON,firstmatch=OFF,materialization=OFF

explain of the SELECT part:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
2 SUBQUERY t2 system f3 NULL NULL NULL 1
2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 5 func 1
3 SUBQUERY t3 ref f3,f4 f4 4 const 1 Using where; Using index

test case:

CREATE TABLE t1 (f3 int) ;
INSERT INTO t1 VALUES (1),(7);

CREATE TABLE t2 (f3 int , f5 varchar(1), KEY (f3)) ;
INSERT INTO t2 VALUES (7,'b');

CREATE TABLE t3 (f3 int , f4 varchar(1) , KEY(f3), KEY (f4,f3)) ;
INSERT INTO t3 VALUES (1,'t'),(7,'g');

SET SESSION optimizer_switch='semijoin=ON,materialization=ON';

CREATE TABLE t4
SELECT f3
FROM t1 WHERE ( f3 ) NOT IN (
        SELECT f3
        FROM t2
        WHERE f5 IN (
                SELECT f4
                FROM t3
                WHERE t3.f3 < 3
        )
);