MDEV-31181 Crash with EXPLAIN EXTENDED for single-table DELETE using IN predicand

This bug affected EXPLAIN EXTENDED command for single-table DELETE that
used an IN subquery in its WHERE clause. A crash happened if the optimizer
chose to employ index_subquery or unique_subquery access when processing
such command.
The crash happened when the command tried to print the transformed query.
In the current code of 10.4 for single-table DELETE statements the output
of any explain command is produced after the join structures of all used
subqueries have been destroyed. JOIN::destroy() sets the field tab of the
JOIN_TAB structures created for subquery tables to NULL. As a result
subselect_indexsubquery_engine::print(), subselect_indexsubquery_engine()
cannot use this field to get the alias name of the joined table.

This patch suggests to use the field TABLE_LIST::TAB that can be accessed
from JOIN_TAB::tab_list to get the alias name of the joined table.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

4 files changed, 72 insertions, 7 deletions

diff --git a/mysql-test/main/explain_non_select.result b/mysql-test/main/explain_non_select.result
index d60f10f85c8..d7ed3992572 100644
--- a/mysql-test/main/explain_non_select.result
+++ b/mysql-test/main/explain_non_select.result
@@ -277,3 +277,35 @@ EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
 drop table t1,t2;
+#
+# MDEV-31181: EXPLAIN EXTENDED for single-table DELETE with IN predicand
+#
+create table t1 (a int);
+insert into t1 values (3), (7), (1), (3), (4);
+create table t2 (pk int primary key);
+insert into t2 values (3), (5), (1);
+create table t3 (a int, key(a));
+insert into t3 values (7), (5), (7), (3);
+explain extended delete from t1 where a in (select pk from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+2	DEPENDENT SUBQUERY	t2	unique_subquery	PRIMARY	PRIMARY	4	func	1	100.00	Using index
+Warnings:
+Note	1003	/* select#1 */ delete  from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on PRIMARY)))
+delete from t1 where a in (select pk from t2);
+select * from t1;
+a
+7
+4
+explain extended delete from t1 where a in (select a from t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DEPENDENT SUBQUERY	t3	index_subquery	a	a	5	func	2	100.00	Using index
+Warnings:
+Note	1003	/* select#1 */ delete  from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t3 on a)))
+delete from t1 where a in (select a from t3);
+select * from t1;
+a
+4
+drop table t1,t2,t3;
+# End of 10.4 tests
diff --git a/mysql-test/main/explain_non_select.test b/mysql-test/main/explain_non_select.test
index d9ff0fb7245..c0c543ad273 100644
--- a/mysql-test/main/explain_non_select.test
+++ b/mysql-test/main/explain_non_select.test
@@ -250,3 +250,31 @@ PREPARE stmt FROM 'EXPLAIN INSERT INTO t1 SELECT * FROM t2';
 EXECUTE stmt;
 drop table t1,t2;
 
+--echo #
+--echo # MDEV-31181: EXPLAIN EXTENDED for single-table DELETE with IN predicand
+--echo #
+
+create table t1 (a int);
+insert into t1 values (3), (7), (1), (3), (4);
+create table t2 (pk int primary key);
+insert into t2 values (3), (5), (1);
+create table t3 (a int, key(a));
+insert into t3 values (7), (5), (7), (3);
+
+let $q1=
+delete from t1 where a in (select pk from t2);
+
+eval explain extended $q1;
+eval $q1;
+select * from t1;
+
+let $q2=
+delete from t1 where a in (select a from t3);
+
+eval explain extended $q2;
+eval $q2;
+select * from t1;
+
+drop table t1,t2,t3;
+
+--echo # End of 10.4 tests
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index f88e1e7e101..ae0ab27ec31 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -4536,10 +4536,11 @@ void subselect_union_engine::print(String *str, enum_query_type query_type)
 void subselect_uniquesubquery_engine::print(String *str,
                                             enum_query_type query_type)
 {
+  TABLE *table= tab->tab_list ? tab->tab_list->table : tab->table;
   str->append(STRING_WITH_LEN("<primary_index_lookup>("));
   tab->ref.items[0]->print(str, query_type);
   str->append(STRING_WITH_LEN(" in "));
-  if (tab->table->s->table_category == TABLE_CATEGORY_TEMPORARY)
+  if (table->s->table_category == TABLE_CATEGORY_TEMPORARY)
   {
     /*
       Temporary tables' names change across runs, so they can't be used for
@@ -4548,8 +4549,8 @@ void subselect_uniquesubquery_engine::print(String *str,
     str->append(STRING_WITH_LEN("<temporary table>"));
   }
   else
-    str->append(&tab->table->s->table_name);
-  KEY *key_info= tab->table->key_info+ tab->ref.key;
+    str->append(&table->s->table_name);
+  KEY *key_info= table->key_info+ tab->ref.key;
   str->append(STRING_WITH_LEN(" on "));
   str->append(&key_info->name);
   if (cond)
@@ -4567,12 +4568,13 @@ all other tests pass.
 
 void subselect_uniquesubquery_engine::print(String *str)
 {
-  KEY *key_info= tab->table->key_info + tab->ref.key;
+  TABLE *table= tab->tab_list ? tab->tab_list->table : tab->table;
+  KEY *key_info= table->key_info + tab->ref.key;
   str->append(STRING_WITH_LEN("<primary_index_lookup>("));
   for (uint i= 0; i < key_info->user_defined_key_parts; i++)
     tab->ref.items[i]->print(str);
   str->append(STRING_WITH_LEN(" in "));
-  str->append(&tab->table->s->table_name);
+  str->append(&table->s->table_name);
   str->append(STRING_WITH_LEN(" on "));
   str->append(&key_info->name);
   if (cond)
@@ -4587,11 +4589,12 @@ void subselect_uniquesubquery_engine::print(String *str)
 void subselect_indexsubquery_engine::print(String *str,
                                            enum_query_type query_type)
 {
+  TABLE *table= tab->tab_list ? tab->tab_list->table : tab->table;
   str->append(STRING_WITH_LEN("<index_lookup>("));
   tab->ref.items[0]->print(str, query_type);
   str->append(STRING_WITH_LEN(" in "));
-  str->append(tab->table->s->table_name.str, tab->table->s->table_name.length);
-  KEY *key_info= tab->table->key_info+ tab->ref.key;
+  str->append(&table->s->table_name);
+  KEY *key_info= table->key_info+ tab->ref.key;
   str->append(STRING_WITH_LEN(" on "));
   str->append(&key_info->name);
   if (check_null)
@@ -5271,6 +5274,7 @@ subselect_hash_sj_engine::make_unique_engine()
     DBUG_RETURN(NULL);
 
   tab->table= tmp_table;
+  tab->tab_list= 0;
   tab->preread_init_done= FALSE;
   tab->ref.tmp_table_index_lookup_init(thd, tmp_key, it, FALSE);
 
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 1c03b0bb4a3..db7a4c0fc14 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -4128,6 +4128,7 @@ bool setup_sj_materialization_part1(JOIN_TAB *sjm_tab)
   
   sjm->materialized= FALSE;
   sjm_tab->table= sjm->table;
+  sjm_tab->tab_list= emb_sj_nest;
   sjm->table->pos_in_table_list= emb_sj_nest;
  
   DBUG_RETURN(FALSE);