Ordered_key::cmp_key_with_search_key(rownum_t): Assertion `!compare_pred[i]->null_value' failed with subquery on both sides of NOT IN and materialization

[Philip Stoev]

Repeatable with maria-5.3 and maria-5.3-mwl89. A crash happens when there is a subquery on both sides of a NOT IN statement. It appears that the right-side table must have at least 100 rows for the crash to occur.

backtrace:

mysqld: item_subselect.cc:4512: int Ordered_key::cmp_key_with_search_key(rownum_t): Assertion `!compare_pred[i]->null_value' failed.

#8 0x006b0d98 in __assert_fail () from /lib/libc.so.6
#9 0x0823f8fd in Ordered_key::cmp_key_with_search_key (this=0xae675bb8, row_num=49) at item_subselect.cc:4512
#10 0x0823f9e0 in Ordered_key::lookup (this=0xae675bb8) at item_subselect.cc:4538
#11 0x08240bdd in subselect_rowid_merge_engine::partial_match (this=0xae675af8) at item_subselect.cc:5056
#12 0x0823fefc in subselect_partial_match_engine::exec (this=0xae675af8) at item_subselect.cc:4698
#13 0x08236995 in Item_subselect::exec (this=0xae62ed10) at item_subselect.cc:484
#14 0x08236b8e in Item_in_subselect::exec (this=0xae62ed10) at item_subselect.cc:581
#15 0x082369c4 in Item_subselect::exec (this=0xae62ed10) at item_subselect.cc:489
#16 0x08236b8e in Item_in_subselect::exec (this=0xae62ed10) at item_subselect.cc:581
#17 0x0823850d in Item_in_subselect::val_bool (this=0xae62ed10) at item_subselect.cc:1257
#18 0x081d659c in Item::val_bool_result (this=0xae62ed10) at item.h:785
#19 0x08201f4d in Item_in_optimizer::val_int (this=0xae62f198) at item_cmpfunc.cc:1960
#20 0x081d653e in Item::val_int_result (this=0xae62f198) at item.h:781
#21 0x081d2262 in Item_cache_int::cache_value (this=0xae674860) at item.cc:7820
#22 0x081da1e2 in Item_cache_wrapper::cache (this=0xae6747f8) at item.cc:6825
#23 0x081cffe1 in Item_cache_wrapper::val_bool (this=0xae6747f8) at item.cc:6989
#24 0x081fe1f7 in Item_func_not::val_int (this=0xae62ee30) at item_cmpfunc.cc:287
#25 0x0832475a in evaluate_join_record (join=0xae665260, join_tab=0xae62f6f0, error=0) at sql_select.cc:13923
#26 0x083243c5 in sub_select (join=0xae665260, join_tab=0xae62f6f0, end_of_records=false) at sql_select.cc:13828
#27 0x08323747 in do_select (join=0xae665260, fields=0xae81624, table=0x0, procedure=0x0) at sql_select.cc:13363
#28 0x0830a3cb in JOIN::exec (this=0xae665260) at sql_select.cc:2435
#29 0x0830ab99 in mysql_select (thd=0xae7fbf8, rref_pointer_array=0xae81694, tables=0xae62dbc0, wild_num=1, fields=..., conds=0xae62ee30, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xae62ef28, unit=0xae812f4, select_lex=0xae81590)
    at sql_select.cc:2652
#30 0x08303441 in handle_select (thd=0xae7fbf8, lex=0xae81298, result=0xae62ef28, setup_tables_done_option=0) at sql_select.cc:282
#31 0x082a0f54 in execute_sqlcom_select (thd=0xae7fbf8, all_tables=0xae62dbc0) at sql_parse.cc:5070
#32 0x08297f63 in mysql_execute_command (thd=0xae7fbf8) at sql_parse.cc:2234
#33 0x082a34ec in mysql_parse (thd=0xae7fbf8, rawbuf=0xae62da10 "SELECT *\nFROM t2\nWHERE ( SELECT f1 FROM t4 ) NOT IN ( SELECT f1 FROM t1 )", length=73,
    found_semicolon=0xae9a0228) at sql_parse.cc:6077
#34 0x08295bfb in dispatch_command (command=COM_QUERY, thd=0xae7fbf8, packet=0xae99d31 "", packet_length=74) at sql_parse.cc:1210
#35 0x082950a8 in do_command (thd=0xae7fbf8) at sql_parse.cc:903
#36 0x08292186 in handle_one_connection (arg=0xae7fbf8) at sql_connect.cc:1154
#37 0x00821919 in start_thread () from /lib/libpthread.so.0
#38 0x0076acce in clone () from /lib/libc.so.6

test case:

CREATE TABLE t1 ( f1 int(11)) ;
INSERT IGNORE INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),(71),(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100);

CREATE TABLE t2 ( f11 varchar(1) );
INSERT IGNORE INTO t2 VALUES (f),(d);

CREATE TABLE t4 ( f1 int(11)) ;

set session optimizer_switch='materialization=on;in_to_exists=off';

SELECT *
FROM t2
WHERE ( SELECT f1 FROM t4 ) NOT IN ( SELECT f1 FROM t1 ) ;

[Philip Stoev (pstoev-askmonty)]

This bug was not detected previously since subselects on the left side of the IN statement were only introduced recently.

[Philip Stoev (pstoev-askmonty)]

By "introduced recently" I meant added to the space of queries the RQG considers.

[Philip Stoev (pstoev-askmonty)]

Fixed test case, some quoting and separators were garbled:

CREATE TABLE t1 ( f1 int(11)) ;
INSERT IGNORE INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),(71),(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100);

CREATE TABLE t2 ( f11 varchar(1) );
INSERT IGNORE INTO t2 VALUES ('f'),('d');

CREATE TABLE t4 ( f1 int(11)) ;

set session optimizer_switch='materialization=on,in_to_exists=off';

SELECT *
FROM t2
WHERE ( SELECT f1 FROM t4 ) NOT IN ( SELECT f1 FROM t1 ) ;

[Philip Stoev (pstoev-askmonty)]

see also https://bugs.launchpad.net/bugs/730604

[Timour Katchaounov (timour)]

The committed fix for the bug is incomplete, because in the following case the
result must be empty, while it isn't.

CREATE TABLE t1 (f1a int, f1b int) ;
INSERT IGNORE INTO t1 VALUES (1,1),(2,2);
CREATE TABLE t2 ( f2 int);
INSERT IGNORE INTO t2 VALUES (3),(4);
CREATE TABLE t3 (f3a int, f3b int);
insert into t3 values (1,1),(2,2);

set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,in_to_exists=off';

-- wrong result with a single column:
SELECT * FROM t2 WHERE (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1);

-- correct empty result with two columns:
SELECT * FROM t2 WHERE (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1);

[Timour Katchaounov (timour)]

In fact, the bug has been fixed properly in 5.3, however, due to the different
query processing of subqueries in MWL#89, the bug is still present there.

[Timour Katchaounov (timour)]

The wrong result mentioned above seems to be unrelated to this bug,
and has been filed as a separate BUG #747278. Thus I am closing this bug.