item_subselect.cc:4285: Ordered_key::Ordered_key(uint, TABLE*, Item*, ha_rows, ha_rows, ha_rows, uchar*): Assertion `tbl->file->stats.records > null_count' failed in maria-5.3-mwl89

[Philip Stoev]

Not reproducible in maria-5.3

Query:

SELECT alias1.f3 FROM t2 AS alias1
WHERE ( alias1.f3 , alias1.f2 ) NOT IN (
SELECT SQ1_t2.f3 , SQ1_t1.f3
FROM t2 AS SQ1_t1 JOIN
( t2 AS SQ1_t2 JOIN t1 AS SQ1_t3 ON SQ1_t3.f3 = SQ1_t2.f2 )
ON SQ1_t3.f2 = SQ1_t2.f2

assertion:

mysqld: item_subselect.cc:4285: Ordered_key::Ordered_key(uint, TABLE*, Item*, ha_rows, ha_rows, ha_rows, uchar*): Assertion `tbl->file->stats.records > null_count' failed.

backtrace:

#8 0x006b0d98 in __assert_fail () from /lib/libc.so.6
#9 0x0823ad3a in Ordered_key::Ordered_key (this=0x9546630, keyid_arg=1, tbl_arg=0x9516610, search_key_arg=0x94d8c78, null_count_arg=180,
    min_null_row_arg=541, max_null_row_arg=720,
    row_num_to_rowid_arg=0x951f168 "\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245"...) at item_subselect.cc:4285
#10 0x0823c0f9 in subselect_rowid_merge_engine::init (this=0x9546370, non_null_key_parts=0x0, partial_match_key_parts=0x95111dc) at item_subselect.cc:4845
#11 0x0823a9d4 in subselect_hash_sj_engine::exec (this=0x9511198) at item_subselect.cc:4196
#12 0x0823243b in Item_subselect::exec (this=0x9502030) at item_subselect.cc:552
#13 0x0823263a in Item_in_subselect::exec (this=0x9502030) at item_subselect.cc:649
#14 0x08233f90 in Item_in_subselect::val_bool (this=0x9502030) at item_subselect.cc:1327
#15 0x081d1670 in Item::val_bool_result (this=0x9502030) at item.h:783
#16 0x081fd14a in Item_in_optimizer::val_int (this=0x9502380) at item_cmpfunc.cc:1893
#17 0x081bb0f2 in Item::val_bool (this=0x9502380) at item.cc:187
#18 0x081f93a3 in Item_func_not::val_int (this=0x9502110) at item_cmpfunc.cc:287
#19 0x08321e1c in evaluate_join_record (join=0x9506a68, join_tab=0x9502ba0, error=0) at sql_select.cc:13234
#20 0x08321a80 in sub_select (join=0x9506a68, join_tab=0x9502ba0, end_of_records=false) at sql_select.cc:13140
#21 0x08320e7c in do_select (join=0x9506a68, fields=0x9488af4, table=0x0, procedure=0x0) at sql_select.cc:12686
#22 0x083071f5 in JOIN::exec (this=0x9506a68) at sql_select.cc:2340
#23 0x08307961 in mysql_select (thd=0x94870e8, rref_pointer_array=0x9488b64, tables=0x94d89b0, wild_num=0, fields=..., conds=0x9502110, og_num=0, order=0x0,
    group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x95021a0, unit=0x94887c4, select_lex=0x9488a60) at sql_select.cc:2550
#24 0x0830004d in handle_select (thd=0x94870e8, lex=0x9488768, result=0x95021a0, setup_tables_done_option=0) at sql_select.cc:281
#25 0x0829d91b in execute_sqlcom_select (thd=0x94870e8, all_tables=0x94d89b0) at sql_parse.cc:5102
#26 0x08294423 in mysql_execute_command (thd=0x94870e8) at sql_parse.cc:2281
#27 0x0829feac in mysql_parse (thd=0x94870e8,
    rawbuf=0x94d86d0 "SELECT alias1.f3 FROM t2 AS alias1\nWHERE ( alias1.f3 , alias1.f2 ) NOT IN (\nSELECT SQ1_t2.f3 , SQ1_t1.f3\nFROM t2 AS SQ1_t1 JOIN\n( t2 AS SQ1_t2 JOIN t1 AS SQ1_t3 ON SQ1_t3.f3 = SQ1_t2.f2 )\nON SQ1_t3"..., length=217, found_semicolon=0xae8a0228) at sql_parse.cc:6109
#28 0x08291f0c in dispatch_command (command=COM_QUERY, thd=0x94870e8, packet=0x949f789 "", packet_length=217) at sql_parse.cc:1209
#29 0x08291376 in do_command (thd=0x94870e8) at sql_parse.cc:902
#30 0x0828e454 in handle_one_connection (arg=0x94870e8) at sql_connect.cc:1154
#31 0x00821919 in start_thread () from /lib/libpthread.so.0
#32 0x0076acce in clone () from /lib/libc.so.6

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY alias1 ALL NULL NULL NULL NULL 13 Using where
2 SUBQUERY SQ1_t1 ALL NULL NULL NULL NULL 13
2 SUBQUERY SQ1_t2 ALL NULL NULL NULL NULL 13
2 SUBQUERY SQ1_t3 ref f2 f2 5 test.SQ1_t2.f2 2 Using index condition; Using where

test case:

SET SESSION join_cache_level=0;
SET SESSION optimizer_switch='semijoin=off,subquery_cache=off,materialization=on';
CREATE TABLE t2 (
f1 int,
f2 varchar(32),
f3 varchar(32)) ;
INSERT IGNORE INTO t2 VALUES ('3','j','j'),('8','h','h'),('5','o','o'),('0',NULL,NULL),('5','k','k'),('1','e','e'),('3','n','n'),('0','t','t'),('1','c','c'),('9','m','m'),('5','y','y'),('6','f','f'),('2','d','d');

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
f2 int,
f3 int,
KEY (f2)) ;
INSERT IGNORE INTO t1 VALUES ('0','0'),('0','0'),('0','0'),('0','0'),('0','0'),('0','0'),('0','0'),('0','0'),('0','0'),('0','0'),('0','0'),('0','0'),('0','0'),('0','0'),('0','0');

EXPLAIN SELECT alias1.f3 FROM t2 AS alias1
WHERE ( alias1.f3 , alias1.f2 ) NOT IN (
        SELECT SQ1_t2.f3 , SQ1_t1.f3
        FROM t2 AS SQ1_t1 JOIN
        ( t2 AS SQ1_t2 JOIN t1 AS SQ1_t3 ON SQ1_t3.f3 = SQ1_t2.f2 )
        ON SQ1_t3.f2 = SQ1_t2.f2
);

SELECT alias1.f3 FROM t2 AS alias1
WHERE ( alias1.f3 , alias1.f2 ) NOT IN (
        SELECT SQ1_t2.f3 , SQ1_t1.f3
        FROM t2 AS SQ1_t1 JOIN
        ( t2 AS SQ1_t2 JOIN t1 AS SQ1_t3 ON SQ1_t3.f3 = SQ1_t2.f2 )
        ON SQ1_t3.f2 = SQ1_t2.f2
);

[Timour Katchaounov (timour)]

This is a duplicate of BUG#680058, which has been fixed in 5.3.
The fix will appear in 5.3-mwl89 after the next merge.