MDEV-25576: The statement EXPLAIN running as regular statement and as prepared statement produces different results for UPDATE with subquery

Both EXPLAIN and EXPLAIN EXTENDED statements produce different results set
in case it is run in normal way and in PS mode for the statements
UPDATE/DELETE with subquery.

The use case below reproduces the issue:
MariaDB [test]> CREATE TABLE t1 (c1 INT KEY) ENGINE=MyISAM;
Query OK, 0 rows affected (0,128 sec)

MariaDB [test]> CREATE TABLE t2 (c2 INT) ENGINE=MyISAM;
Query OK, 0 rows affected (0,023 sec)

MariaDB [test]> CREATE TABLE t3 (c3 INT) ENGINE=MyISAM;
Query OK, 0 rows affected (0,021 sec)

MariaDB [test]> EXPLAIN EXTENDED UPDATE t3 SET c3 =
    -> ( SELECT COUNT(d1.c1) FROM ( SELECT a11.c1 FROM t1 AS a11
    -> STRAIGHT_JOIN t2 AS a21 ON a21.c2 = a11.c1 JOIN t1 AS a12
    -> ON a12.c1 = a11.c1 ) d1 );
+------+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+
|    1 | PRIMARY     | t3    | ALL  | NULL          | NULL | NULL    | NULL |    0 |   100.00 |                                |
|    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE noticed after reading const tables
+------+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+
2 rows in set (0,002 sec)

MariaDB [test]> PREPARE stmt FROM
    -> EXPLAIN EXTENDED UPDATE t3 SET c3 =
    -> ( SELECT COUNT(d1.c1) FROM ( SELECT a11.c1 FROM t1 AS a11
    -> STRAIGHT_JOIN t2 AS a21 ON a21.c2 = a11.c1 JOIN t1 AS a12
    -> ON a12.c1 = a11.c1 ) d1 );
Query OK, 0 rows affected (0,000 sec)
Statement prepared

MariaDB [test]>  EXECUTE stmt;
+------+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+
|    1 | PRIMARY     | t3    | ALL  | NULL          | NULL | NULL    | NULL |    0 |   100.00 |                                |
|    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+
2 rows in set (0,000 sec)

The reason by that different result sets are produced is that on execution
of the statement 'EXECUTE stmt' the flag SELECT_DESCRIBE not set
in the data member SELECT_LEX::options for instances of SELECT_LEX that
correspond to subqueries used in the UPDTAE/DELETE statements.

Initially, these flags were set on parsing the statement
  PREPARE stmt FROM "EXPLAIN EXTENDED UPDATE t3 SET ..."
but latter they were reset before starting real execution of
the parsed query during handling the statement 'EXECUTE stmt';

So, to fix the issue the functions mysql_update()/mysql_delete()
have been modified to set the flag SELECT_DESCRIBE forcibly
in the data member SELECT_LEX::options for the primary SELECT_LEX
of the UPDATE/DELETE statement.

4 files changed, 23 insertions, 0 deletions

diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index c9a34221544..11f4cb9890b 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -8794,6 +8794,23 @@ int dynamic_column_error_message(enum_dyncol_func_result rc)
   return rc;
 }
 
+
+/**
+  Turn on the SELECT_DESCRIBE flag for the primary SELECT_LEX of the statement
+  being processed in case the statement is EXPLAIN UPDATE/DELETE.
+
+  @param lex  current LEX
+*/
+
+void promote_select_describe_flag_if_needed(LEX *lex)
+{
+  if (lex->describe)
+  {
+    lex->select_lex.options |= SELECT_DESCRIBE;
+  }
+}
+
+
 /**
   @} (end of group Data_Dictionary)
 */
diff --git a/sql/sql_base.h b/sql/sql_base.h
index b67341bcbda..5674e8378c5 100644
--- a/sql/sql_base.h
+++ b/sql/sql_base.h
@@ -516,6 +516,8 @@ bool extend_table_list(THD *thd, TABLE_LIST *tables,
                        Prelocking_strategy *prelocking_strategy,
                        bool has_prelocking_list);
 
+void promote_select_describe_flag_if_needed(LEX *lex);
+
 /**
   A context of open_tables() function, used to recover
   from a failed open_table() or open_routine() attempt.
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index e2bf2d727b1..a26e15431a6 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -274,6 +274,8 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
   query_plan.table= table;
   query_plan.updating_a_view= MY_TEST(table_list->view);
 
+  promote_select_describe_flag_if_needed(thd->lex);
+
   if (mysql_prepare_delete(thd, table_list, select_lex->with_wild,
                                             select_lex->item_list, &conds))
     DBUG_RETURN(TRUE);
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index ec27ccda778..b7ef7c2a0db 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -342,6 +342,8 @@ int mysql_update(THD *thd,
   want_privilege= (table_list->view ? UPDATE_ACL :
                    table_list->grant.want_privilege);
 #endif
+  promote_select_describe_flag_if_needed(thd->lex);
+
   if (mysql_prepare_update(thd, table_list, &conds, order_num, order))
     DBUG_RETURN(1);