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

[Philip Stoev]

Even after the fix for bug #675981 ,the test case below still crashes with the following backtrace:

#8 0x00a72d98 in __assert_fail () from /lib/libc.so.6
#9 0x08321715 in sub_select_cache (join=0xae673878, join_tab=0xae679330, end_of_records=true) at sql_select.cc:12922
#10 0x0832195f in sub_select (join=0xae673878, join_tab=0xae679170, end_of_records=true) at sql_select.cc:13090
#11 0x08320f0e in do_select (join=0xae673878, fields=0xae632554, table=0x0, procedure=0x0) at sql_select.cc:12686
#12 0x083071f5 in JOIN::exec (this=0xae673878) at sql_select.cc:2340
#13 0x08237f1e in subselect_single_select_engine::exec (this=0xae633828) at item_subselect.cc:2766
#14 0x0823243b in Item_subselect::exec (this=0xae633730) at item_subselect.cc:552
#15 0x0823263a in Item_in_subselect::exec (this=0xae633730) at item_subselect.cc:649
#16 0x08233f90 in Item_in_subselect::val_bool (this=0xae633730) at item_subselect.cc:1327
#17 0x081d1670 in Item::val_bool_result (this=0xae633730) at item.h:783
#18 0x081fd14a in Item_in_optimizer::val_int (this=0xae669ad8) at item_cmpfunc.cc:1893
#19 0x081d1612 in Item::val_int_result (this=0xae669ad8) at item.h:779
#20 0x081cd450 in Item_cache_int::cache_value (this=0xae679d90) at item.cc:7733
#21 0x081d52bc in Item_cache_wrapper::cache (this=0xae679d30) at item.cc:6738
#22 0x081cb04b in Item_cache_wrapper::val_bool (this=0xae679d30) at item.cc:6902
#23 0x08204fa4 in Item_cond_and::val_int (this=0xae66a9e0) at item_cmpfunc.cc:4591
#24 0x082dd9de in SQL_SELECT::skip_record (this=0xae66aa98, thd=0x98910e8) at opt_range.h:847
#25 0x083c8dd4 in find_all_keys (param=0xae9dff18, select=0xae66aa98, sort_keys=0xae67eb08, buffpek_pointers=0xae9dfd50, tempfile=0xae9dfe34, indexfile=0x0)
    at filesort.cc:619
#26 0x083c7984 in filesort (thd=0x98910e8, table=0xae63eb20, sortorder=0xae679f88, s_length=1, select=0xae66aa98, max_rows=18446744073709551615,
    sort_positions=false, examined_rows=0xae9e0080) at filesort.cc:246
#27 0x08328d4e in create_sort_index (thd=0x98910e8, join=0xae66e9f8, order=0xae6692f8, filesort_limit=18446744073709551615,
    select_limit=18446744073709551615, is_order_by=false) at sql_select.cc:15904
#28 0x08303c37 in JOIN::init_execution (this=0xae66e9f8) at sql_select.cc:1469
#29 0x08304a8a in JOIN::exec (this=0xae66e9f8) at sql_select.cc:1750
#30 0x08307961 in mysql_select (thd=0x98910e8, rref_pointer_array=0x9892b64, tables=0xae631c88, wild_num=0, fields=..., conds=0xae633730, og_num=2,
    order=0xae6693d0, group=0xae6692f8, having=0x0, proc_param=0x0, select_options=2147764740, result=0xae669480, unit=0x98927c4, select_lex=0x9892a60)
    at sql_select.cc:2550
#31 0x08330ed0 in mysql_explain_union (thd=0x98910e8, unit=0x98927c4, result=0xae669480) at sql_select.cc:18914
#32 0x0829d78a in execute_sqlcom_select (thd=0x98910e8, all_tables=0xae631c88) at sql_parse.cc:5080
#33 0x08294423 in mysql_execute_command (thd=0x98910e8) at sql_parse.cc:2281
#34 0x0829feac in mysql_parse (thd=0x98910e8,
    rawbuf=0xae631908 "EXPLAIN\nSELECT table2.f4 AS field1\nFROM t2 AS table1 JOIN t1 AS table2 ON table2.f6\nWHERE\n( table2.f2 ) IN (\nSELECT SUBQUERY2_t1.f3\nFROM t3 AS SUBQUERY2_t1 JOIN (\nt1 AS SUBQUERY2_t2\nJOIN t1 AS SUBQUER"..., length=291, found_semicolon=0xae9e1228) at sql_parse.cc:6109
#35 0x08291f0c in dispatch_command (command=COM_QUERY, thd=0x98910e8, packet=0x98a9789 "", packet_length=291) at sql_parse.cc:1209
#36 0x08291376 in do_command (thd=0x98910e8) at sql_parse.cc:902
#37 0x0828e454 in handle_one_connection (arg=0x98910e8) at sql_connect.cc:1154
#38 0x00bea919 in start_thread () from /lib/libpthread.so.0
#39 0x00b2ccbe in clone () from /lib/libc.so.6

[Philip Stoev (pstoev-askmonty)]

Test case:

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;

[Timour Katchaounov (timour)]

Analysis:

This another instance of the problem fixed in LP BUG#675981 -
evaluation of subqueries during EXPLAIN when the subquery plan
is incomplete because JOIN::optimize() generally doesn't create
complete execution plans for EXPLAIN statements.

In this case the call path is:
mysql_explain_union -> outer_join.exec -> outer_join.init_execution ->
create_sort_index -> filesort -> find_all_keys -> SQL_SELECT::skip_record ->
outer_where_clause.val_int -> ... -> subselect_join.exec -> ... -> sub_select_cache