item_subselect.h:1007: void Ordered_key::add_key(rownum_t): Assertion `key_buff_elements && cur_key_idx < key_buff_elements' failed

[Philip Stoev]

Backtrace:

Ordered_key::add_key(unsigned long long)+0x64) [0x823dada]
subselect_rowid_merge_engine::init(st_bitmap*, st_bitmap*)+0x74f) [0x823c435]
subselect_hash_sj_engine::exec()+0x3fa) [0x823a9de]
Item_subselect::exec()+0x83) [0x823243b]
Item_in_subselect::exec()+0xf2) [0x823263a]
Item_in_subselect::val_bool()+0x6c) [0x8233f90]
Item::val_bool_result()+0x18) [0x81d1670]
Item_in_optimizer::val_int()+0x242) [0x81fd14a]
Item::val_bool()+0x48) [0x81bb0f2]
Item_func_not::val_int()+0x2d) [0x81f93a3]
Item::val_bool()+0x48) [0x81bb0f2]
Item_cond_and::val_int()+0x68) [0x8204fa4]
SQL_SELECT::skip_record(THD*)+0x28) [0x82dd9de]
JOIN_CACHE::check_match(unsigned char*)+0x34) [0x82de286]
JOIN_CACHE::generate_full_extensions(unsigned char*)+0x1f) [0x82db97d]
JOIN_CACHE_BNL::join_matching_records(bool)+0x32b) [0x82db7fd]
JOIN_CACHE::join_records(bool)+0x6c) [0x82db2c2]
sub_select_cache(JOIN*, st_join_table*, bool)+0xa2) [0x8321746]
sub_select(JOIN*, st_join_table*, bool)+0x71) [0x832195f]
) [0x8320f0e]
JOIN::exec()+0xba9) [0x83055c5]
mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0x34a) [0x8307961]
handle_select(THD*, st_lex*, select_result*, unsigned long)+0x1cd) [0x830004d]
) [0x829d91b]
mysql_execute_command(THD*)+0x769) [0x8294423]
mysql_parse(THD*, char*, unsigned int, char const**)+0x26f) [0x829feac]
dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x8ff) [0x8291f0c]
do_command(THD*)+0x273) [0x8291376]
handle_one_connection+0x152) [0x828e454]

[Philip Stoev (pstoev-askmonty)]

RQG command line

perl runall.pl --grammar=conf/optimizer/optimizer_subquery.yy --threads=1 --queries=1M --basedir=/home/philips/bzr/maria-5.3-mwl89/ --seed=1290434804 --mysqld=--optimizer_switch=subquery_cache=off,semijoin=off,materialization=on

[Philip Stoev (pstoev-askmonty)]

Test case:

NORE INTO t1 VALUES
(7,8,0,0),(1,9,0,0),(5,9,0,0),(3,1,0,0),(6,NULL,0,0),
(9,2,0,0),(7,3,0,0),(NULL,0,0,0),(3,1,0,0),(5,1,0,0),
(1,8,0,0),(2,5,0,0),(NULL,5,0,0),(1,8,0,0),(0,6,0,0),
(2,5,0,0),(8,4,0,0),(7,7,0,0),(5,6,0,0),(NULL,4,0,0);

CREATE TABLE t2 (
f1 varchar(32),
f2 varchar(32),
f3 varchar(32),
f4 varchar(32),
KEY (f2),
KEY (f3),
KEY (f2)) ENGINE=MyISAM;
INSERT IGNORE INTO t2 VALUES ('2','9','t','t'),('5','5','o','o'),('6','5','k','k'),('1','1','e','e'),('2','3','n','n'),('1','0','t','t'),('1','1','c','c'),('$

SELECT *
FROM t2 AS alias1
JOIN t1 AS alias3 ON alias3.f1
AND ( alias3.f4 , alias1.f4 ) NOT IN (
        SELECT SQ1_t2.f3 , SQ1_t1.f3
        FROM t2 AS SQ1_t1
        RIGHT JOIN t2 AS SQ1_t2 JOIN t2 AS SQ1_t3 ON SQ1_t3.f3 = SQ1_t2.f3 ON SQ1_t2.f2
) ;

Note that, in the most simplified form, SQ1_t3 is not really used in the query, but if it is removed, the issue is no longer reproducible.

[Philip Stoev (pstoev-askmonty)]

Test case:

SET SESSION optimizer_switch='semijoin=off,subquery_cache=off,materialization=on';

CREATE TABLE t1 (
f1 int,
f2 int,
f3 int,
f4 varchar(32)) ;
INSERT IGNORE INTO t1 VALUES
(7,8,0,0),(1,9,0,0),(5,9,0,0),(3,1,0,0),(6,NULL,0,0),
(9,2,0,0),(7,3,0,0),(NULL,0,0,0),(3,1,0,0),(5,1,0,0),
(1,8,0,0),(2,5,0,0),(NULL,5,0,0),(1,8,0,0),(0,6,0,0),
(2,5,0,0),(8,4,0,0),(7,7,0,0),(5,6,0,0),(NULL,4,0,0);

CREATE TABLE t2 (
f1 varchar(32),
f2 varchar(32),
f3 varchar(32),
f4 varchar(32),
KEY (f2),
KEY (f3),
KEY (f2)) ENGINE=MyISAM;
INSERT IGNORE INTO t2 VALUES ('2','9','t','t'),('5','5','o','o'),('6','5','k','k'),('1','1','e','e'),('2','3','n','n'),('1','0','t','t'),('1','1','c','c'),('0','9','m','m'),('9','5','y','y'),(NULL,'6','f','f'),('4','2','d','d');

SELECT *
FROM t2 AS alias1
JOIN t1 AS alias3 ON alias3.f1
AND ( alias3.f4 , alias1.f4 ) NOT IN (
        SELECT SQ1_t2.f3 , SQ1_t1.f3
        FROM t2 AS SQ1_t1
        RIGHT JOIN t2 AS SQ1_t2 JOIN t2 AS SQ1_t3 ON SQ1_t3.f3 = SQ1_t2.f3 ON SQ1_t2.f2
) ;

[Timour Katchaounov (timour)]

Simplified test case:

create table t1 (f1 char(1), f2 char(1));
insert into t1 values ('t', '0'), ('0', 't');

create table t2 (f3 char(1), f4 char(1));
insert into t2 values ('t', NULL), ('t', NULL), ('d', 'y');

SET @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,in_to_exists=off,subquery_cache=off,semijoin=off';

select * from t1 where (f1, f2) not in (select * from t2);