Assertion `cache != __null' failed in sub_select_cache() on EXPLAIN in maria-5.3-mwl89

[Philip Stoev]

The following query:

EXPLAIN
SELECT table1.f5
FROM t2 AS table1 JOIN t1 AS table3 ON table3.f1
WHERE table1.f1 IN (
SELECT SUBQUERY1_t1.f2
FROM t1 AS SUBQUERY1_t1 JOIN t3 AS SUBQUERY1_t3 ON SUBQUERY1_t3.f4
);

asserts in maria-5.3-mwl89 with the following backtrace:

#8 0x00a72d98 in __assert_fail () from /lib/libc.so.6
#9 0x08321c82 in sub_select_cache (join=0xae66d920, join_tab=0xae674068, end_of_records=true) at sql_select.cc:12914
#10 0x08321ecc in sub_select (join=0xae66d920, join_tab=0xae673ea8, end_of_records=true) at sql_select.cc:13082
#11 0x0832147b in do_select (join=0xae66d920, fields=0xae63248c, table=0x0, procedure=0x0) at sql_select.cc:12678
#12 0x083077ac in JOIN::exec (this=0xae66d920) at sql_select.cc:2338
#13 0x08238172 in subselect_single_select_engine::exec (this=0xae6331c0) at item_subselect.cc:2766
#14 0x0823268f in Item_subselect::exec (this=0xae6330c8) at item_subselect.cc:552
#15 0x0823288e in Item_in_subselect::exec (this=0xae6330c8) at item_subselect.cc:649
#16 0x082341e4 in Item_in_subselect::val_bool (this=0xae6330c8) at item_subselect.cc:1327
#17 0x081d18c2 in Item::val_bool_result (this=0xae6330c8) at item.h:783
#18 0x081fd39e in Item_in_optimizer::val_int (this=0xae633828) at item_cmpfunc.cc:1893
#19 0x0830554f in JOIN::exec (this=0xae668aa0) at sql_select.cc:1821
#20 0x08307f17 in mysql_select (thd=0xa8891e0, rref_pointer_array=0xa88ac5c, tables=0xae631bc0, wild_num=0, fields=..., conds=0xae6330c8, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764740, result=0xae633258, unit=0xa88a8bc, select_lex=0xa88ab58)
    at sql_select.cc:2548
#21 0x083313dc in mysql_explain_union (thd=0xa8891e0, unit=0xa88a8bc, result=0xae633258) at sql_select.cc:18901
#22 0x0829db0a in execute_sqlcom_select (thd=0xa8891e0, all_tables=0xae631bc0) at sql_parse.cc:5080
#23 0x082947a3 in mysql_execute_command (thd=0xa8891e0) at sql_parse.cc:2281
#24 0x082a022c in mysql_parse (thd=0xa8891e0,
    rawbuf=0xae631920 "EXPLAIN\nSELECT table1.f5\nFROM t2 AS table1 JOIN t1 AS table3 ON table3.f1\nWHERE table1.f1 IN\n(\nSELECT SUBQUERY1_t1.f2\nFROM t1 AS SUBQUERY1_t1 JOIN t3 AS SUBQUERY1_t3 ON SUBQUERY1_t3.f4 )", length=186, found_semicolon=0xae7ff228) at sql_parse.cc:6109
#25 0x0829228c in dispatch_command (command=COM_QUERY, thd=0xa8891e0, packet=0xa8a1a71 "", packet_length=186) at sql_parse.cc:1209
#26 0x082916f6 in do_command (thd=0xa8891e0) at sql_parse.cc:902
#27 0x0828e7d4 in handle_one_connection (arg=0xa8891e0) at sql_connect.cc:1154
#28 0x00bea919 in start_thread () from /lib/libpthread.so.0
#29 0x00b2ccbe in clone () from /lib/libc.so.6

Working EXPLAIN is vitally important for automatic testing, so this bug needs to be fixed before testing on mwl-89 can continue.

Test case:
CREATE TABLE t1 (f1 int,f2 int) ;
INSERT IGNORE INTO t1 VALUES ('2','5'),('2',NULL);

CREATE TABLE t2 (f1 int, f5 int) ;
INSERT IGNORE INTO t2 VALUES (1,0);

CREATE TABLE t3 (f4 int) ;
INSERT IGNORE INTO t3 VALUES (0),(0);

EXPLAIN
SELECT table1.f5
FROM t2 AS table1 JOIN t1 AS table3 ON table3.f1
WHERE table1.f1 IN
(
SELECT SUBQUERY1_t1.f2
FROM t1 AS SUBQUERY1_t1 JOIN t3 AS SUBQUERY1_t3 ON SUBQUERY1_t3.f4 );

EXPLAIN from 5.3:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2 DEPENDENT SUBQUERY SUBQUERY1_t1 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY SUBQUERY1_t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer

[Timour Katchaounov (timour)]

Even simpler test case (based on bug analysis):

set @@optimizer_switch='semijoin=off';
EXPLAIN
SELECT * FROM t2
WHERE f1 IN (SELECT SUBQUERY1_t1.f2
                       FROM t1 AS SUBQUERY1_t1 JOIN t3 AS SUBQUERY1_t3 ON SUBQUERY1_t3.f4 );

[Philip Stoev (pstoev-askmonty)]

New test case that still crashes:

CREATE TABLE t1 (f1 int,f2 int,f4 int,f6 int,KEY (f4)) ;
INSERT IGNORE INTO t1 VALUES ('1','5','1','0'),('2','1','1','0'),('2','2','2','0'),('0',NULL,'0','0'),('2','1','2','0'),('2','0','0','0'),('2','2','2','0'),('2','8','2','0'),('2','7','2','0'),('2','5','2','0'),('2',NULL,'1','0');

CREATE TABLE t3 (f3 int) ;
INSERT IGNORE INTO t3 VALUES ('2');

CREATE TABLE t2 (f3 int) ;
INSERT IGNORE INTO t2 VALUES ('7');

EXPLAIN
SELECT table2.f4 AS field1
FROM t2 AS table1 JOIN t1 AS table2 ON table2.f6
WHERE
( table2.f2 ) IN (
SELECT SUBQUERY2_t1.f3
FROM t3 AS SUBQUERY2_t1 JOIN (
        t1 AS SUBQUERY2_t2
        JOIN t1 AS SUBQUERY2_t3 ON SUBQUERY2_t3.f1
)
ON SUBQUERY2_t3.f2 )
GROUP BY field1
ORDER BY table2.f1
LIMIT 10;

[Philip Stoev (pstoev-askmonty)]

Second test case separated into a new bug 680005