[MDEV-31181] Crash with EXPLAIN EXTENDED for single-table DELETE using IN predicand Created: 2023-05-03  Updated: 2023-05-09  Resolved: 2023-05-09

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4, 10.5, 10.6, 10.8, 10.9, 10.10, 10.11, 11.0
Fix Version/s: 10.11.3, 11.0.2, 10.4.29, 10.5.20, 10.6.13, 10.8.8, 10.9.6, 10.10.4

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: regression


 Description    

--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (a INT);
 
INSERT INTO t1 VALUES (1),(2);
 
CREATE TABLE t2 (pk INT PRIMARY KEY);
 
INSERT INTO t2 VALUES (1),(2);
 
 
 
EXPLAIN EXTENDED DELETE FROM t1 WHERE a IN (SELECT pk FROM t2);

10.4 c6ef9b1c

 
#3  <signal handler called>
 
#4  0x0000563ebc361316 in subselect_uniquesubquery_engine::print (this=0x62b0000a6478, str=0x7f7e8b215730, query_type=QT_EXPLAIN_EXTENDED) at /data/src/10.4/sql/item_subselect.cc:4542
 
#5  0x0000563ebc33a11a in Item_subselect::print (this=0x62b0000a3170, str=0x7f7e8b215730, query_type=QT_EXPLAIN_EXTENDED) at /data/src/10.4/sql/item_subselect.cc:1014
 
#6  0x0000563ebc35706a in Item_in_subselect::print (this=0x62b0000a3170, str=0x7f7e8b215730, query_type=QT_EXPLAIN_EXTENDED) at /data/src/10.4/sql/item_subselect.cc:3392
 
#7  0x0000563ebc268526 in Item_func::print_args (this=0x62b0000a3c28, str=0x7f7e8b215730, from=0, query_type=QT_EXPLAIN_EXTENDED) at /data/src/10.4/sql/item_func.cc:613
 
#8  0x0000563ebc2683ab in Item_func::print (this=0x62b0000a3c28, str=0x7f7e8b215730, query_type=QT_EXPLAIN_EXTENDED) at /data/src/10.4/sql/item_func.cc:602
 
#9  0x0000563ebc1df839 in Item_in_optimizer::print (this=0x62b0000a3c28, str=0x7f7e8b215730, query_type=QT_EXPLAIN_EXTENDED) at /data/src/10.4/sql/item_cmpfunc.cc:1254
 
#10 0x0000563ebbaf581f in st_select_lex::print (this=0x62b00009e970, thd=0x62b00009a208, str=0x7f7e8b215730, query_type=QT_EXPLAIN_EXTENDED) at /data/src/10.4/sql/sql_select.cc:28368
 
#11 0x0000563ebb902943 in st_select_lex_unit::print (this=0x62b00009e140, str=0x7f7e8b215730, query_type=QT_EXPLAIN_EXTENDED) at /data/src/10.4/sql/sql_lex.cc:3056
 
#12 0x0000563ebbdf17ae in Explain_query::send_explain (this=0x62b0000a33f0, thd=0x62b00009a208, extended=true) at /data/src/10.4/sql/sql_explain.cc:188
 
#13 0x0000563ebb97e4f7 in mysql_execute_command (thd=0x62b00009a208) at /data/src/10.4/sql/sql_parse.cc:4821
 
#14 0x0000563ebb9942fb in mysql_parse (thd=0x62b00009a208, rawbuf=0x62b0000a1228 "EXPLAIN EXTENDED DELETE FROM t1 WHERE a IN (SELECT pk FROM t2)", length=62, parser_state=0x7f7e8b217860, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7998
 
#15 0x0000563ebb96a7a7 in dispatch_command (command=COM_QUERY, thd=0x62b00009a208, packet=0x62900029e209 "", packet_length=62, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1857
 
#16 0x0000563ebb967320 in do_command (thd=0x62b00009a208) at /data/src/10.4/sql/sql_parse.cc:1378
 
#17 0x0000563ebbd6531c in do_handle_one_connection (connect=0x608000000ba8) at /data/src/10.4/sql/sql_connect.cc:1420
 
#18 0x0000563ebbd64c33 in handle_one_connection (arg=0x608000000ba8) at /data/src/10.4/sql/sql_connect.cc:1324
 
#19 0x0000563ebc9cf74c in pfs_spawn_thread (arg=0x615000006208) at /data/src/10.4/storage/perfschema/pfs.cc:1869
 
#20 0x00007f7e9fea7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
 
#21 0x00007f7e9ff285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

Reproducible with at least MyISAM, InnoDB, Aria, on debug and non-debug builds.

The failure started happening on 10.4 after this commit:

commit f33fc2fae5c3f3e80c4d24348609f3ce5246ca9c
 
Author: Igor Babaev
 
Date:   Wed Mar 22 21:59:18 2023 -0700
 
 
 
    MDEV-30539 EXPLAIN EXTENDED: no message with queries for DML statements



 Comments   
Comment by Oleksandr Byelkin [ 2023-05-04 ]

I have hack for it which can be put before real fix (or can be real fix)

diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
 
index f88e1e7e101..a4a36d96ccc 100644
 
--- a/sql/item_subselect.cc
 
+++ b/sql/item_subselect.cc
 
@@ -4538,6 +4538,12 @@ void subselect_uniquesubquery_engine::print(String *str,
 
 {
 
   str->append(STRING_WITH_LEN("<primary_index_lookup>("));
 
   tab->ref.items[0]->print(str, query_type);
 
+  if (!tab->table)
 
+  {
 
+    // table is not opened so unknown
 
+    str->append(')');
 
+    return;
 
+  }
 
   str->append(STRING_WITH_LEN(" in "));
 
   if (tab->table->s->table_category == TABLE_CATEGORY_TEMPORARY)
 
   {

Comment by Igor Babaev [ 2023-05-05 ]

The following diff fixes this bug in a proper way:

iff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
 
index f88e1e7..ae0ab27 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 1c03b0b..db7a4c0 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);

Comment by Igor Babaev [ 2023-05-08 ]

See also the last build in bb-10.4-igor

Comment by Oleksandr Byelkin [ 2023-05-09 ]

OK to push

Comment by Igor Babaev [ 2023-05-09 ]

A fix for this bug was pushed into 10.4. It has to be pushed upstream as it is.

Generated at Thu Feb 08 10:21:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.