Assertion `0' failed in get_match_flag_by_pos() with semijoin_with_cache=on,derived_with_keys=off, temptable views, join_cache_level=8, innodb

[Philip Stoev]

This query:

SELECT *
FROM (
        SELECT t3.*
        FROM t1 JOIN t3 ON
        ( t3.b = t1.b )
) AS alias1
WHERE c IN (
        SELECT v4.b
        FROM v4 JOIN t2
);

causes this assertion:

mysqld: sql_join_cache.cc:1654: virtual JOIN_CACHE::Match_flag JOIN_CACHE::get_match_flag_by_pos(uchar*): Assertion `0' failed.

#8 0x006b0d98 in __assert_fail () from /lib/libc.so.6
#9 0x082fee73 in JOIN_CACHE::get_match_flag_by_pos (this=0xa1c35fc0, rec_ptr=0xa1c59559 "\374\374\001x\001x\001x") at sql_join_cache.cc:1654
#10 0x082ff5f9 in JOIN_CACHE::skip_if_not_needed_match (this=0xa1c35fc0) at sql_join_cache.cc:1980
#11 0x08301615 in JOIN_CACHE_BNL::skip_next_candidate_for_match (this=0xa1c35fc0, rec_ptr=0xa1c59559 "\374\374\001x\001x\001x") at sql_join_cache.cc:3511
#12 0x082ffc4b in JOIN_CACHE::join_matching_records (this=0xa1c35fc0, skip_last=false) at sql_join_cache.cc:2261
#13 0x082ff74d in JOIN_CACHE::join_records (this=0xa1c35fc0, skip_last=false) at sql_join_cache.cc:2062
#14 0x0834a31f in sub_select_cache (join=0x9b232e08, join_tab=0xa1c350b4, end_of_records=true) at sql_select.cc:14935
#15 0x0834a537 in sub_select (join=0x9b232e08, join_tab=0xa1c34eb0, end_of_records=true) at sql_select.cc:15097
#16 0x0834a537 in sub_select (join=0x9b232e08, join_tab=0xa1c34cac, end_of_records=true) at sql_select.cc:15097
#17 0x0834a34b in sub_select_cache (join=0x9b232e08, join_tab=0xa1c34cac, end_of_records=true) at sql_select.cc:14937
#18 0x0834a537 in sub_select (join=0x9b232e08, join_tab=0xa1c34aa8, end_of_records=true) at sql_select.cc:15097
#19 0x0834a026 in do_select (join=0x9b232e08, fields=0xa82cf24, table=0x0, procedure=0x0) at sql_select.cc:14819
#20 0x0832e69a in JOIN::exec (this=0x9b232e08) at sql_select.cc:2685
#21 0x0832eec6 in mysql_select (thd=0xa82b450, rref_pointer_array=0xa82cfcc, tables=0xa1c15728, wild_num=1, fields=..., conds=0xa1c2df60, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xa1c2f0e8, unit=0xa82cbb0, select_lex=0xa82ce90)
    at sql_select.cc:2906
#22 0x08326bf3 in handle_select (thd=0xa82b450, lex=0xa82cb54, result=0xa1c2f0e8, setup_tables_done_option=0) at sql_select.cc:283
#23 0x082c12b8 in execute_sqlcom_select (thd=0xa82b450, all_tables=0xa1c15728) at sql_parse.cc:5112
#24 0x082b8075 in mysql_execute_command (thd=0xa82b450) at sql_parse.cc:2250
#25 0x082c38f9 in mysql_parse (thd=0xa82b450,
    rawbuf=0xa1c14060 "SELECT *\nFROM (\nSELECT t3.*\nFROM t1 JOIN t3 ON\n( t3.b = t1.b )\n) AS alias1\nWHERE c IN (\nSELECT v4.b\nFROM v4 JOIN t2\n)",
    length=117, found_semicolon=0xa1f73228) at sql_parse.cc:6113
#26 0x082b5cc4 in dispatch_command (command=COM_QUERY, thd=0xa82b450, packet=0xa884109 "", packet_length=117) at sql_parse.cc:1221
#27 0x082b511f in do_command (thd=0xa82b450) at sql_parse.cc:916
#28 0x082b2103 in handle_one_connection (arg=0xa82b450) at sql_connect.cc:1191
#29 0x00821919 in start_thread () from /lib/libpthread.so.0
#30 0x0076acce in clone () from /lib/libc.so.6

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where
1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join)
1 PRIMARY t1 ref b b 4 test.t3.b 1 Using index
1 PRIMARY <derived4> ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t2); Using join buffer (flat, BNL join)
4 DERIVED t4 ALL NULL NULL NULL NULL 1

minimal switch: semijoin_with_cache=on, derived_with_keys=off; join_cache_level=8
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=off,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

revision-id: <email address hidden>
date: 2011-11-29 15:27:52 +0400
build-date: 2011-11-30 11:12:24 +0200
revno: 3321
branch-nick: maria-5.3

test case:

--source include/have_innodb.inc
SET SESSION join_cache_level = 8;
SET SESSION optimizer_switch = 'semijoin_with_cache=on';
SET SESSION optimizer_switch = 'derived_with_keys=off';

CREATE TABLE t1 (a int, b varchar(1), KEY (b,a));
INSERT INTO t1 VALUES (0,'x'),(5,'r');

CREATE TABLE t2 (a int) ENGINE=InnoDB;
INSERT INTO t2 VALUES (8);

CREATE TABLE t3 (b varchar(1), c varchar(1)) ENGINE=InnoDB;
INSERT INTO t3 VALUES ('x','x');

CREATE TABLE t4 (a int NOT NULL, b varchar(1)) ENGINE=InnoDB;
INSERT INTO t4 VALUES (20,'r');

CREATE ALGORITHM=TEMPTABLE VIEW v4 AS SELECT * FROM t4;

SELECT *
FROM (
        SELECT t3.*
        FROM t1 JOIN t3 ON
        ( t3.b = t1.b )
) AS alias1
WHERE c IN (
        SELECT v4.b
        FROM v4 JOIN t2
);

[Igor Babaev (igorb-seattle)]

The bug can be reproduced without any views.

[Igor Babaev (igorb-seattle)]

The bug can be reproduced without usage of derived tables and views.