[MDEV-25576] The statement EXPLAIN running as regular statement and as prepared statement produces different results for UPDATE with subquery Created: 2021-05-01  Updated: 2021-06-17  Resolved: 2021-05-30

Status: Closed
Project: MariaDB Server
Component/s: Prepared Statements
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s: 10.6.2, 10.2.39, 10.3.30, 10.4.20, 10.5.11

Type: Bug Priority: Major
Reporter: Dmitry Shulga Assignee: Dmitry Shulga
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-25845 Remove one SELECT_DESCRIBE check in J... Open
relates to MDEV-25846 Remove one SELECT_DESCRIBE check in J... Closed

 Description   

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

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)

Different results are also produced by the statement 'DELETE FROM with subquery' in case it is run in normal way and in PS mode.
See test case below:

MariaDB [test]> PREPARE stmt FROM "EXPLAIN DELETE FROM t3 WHERE 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,001 sec)
 
Statement prepared
 
 
 
MariaDB [test]> EXECUTE stmt;
 
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
 
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
 
|    1 | PRIMARY     | t3    | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where                    |
 
|    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | no matching row in const table |
 
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
 
2 rows in set (0,001 sec)
 



MariaDB [test]> EXPLAIN DELETE FROM t3 WHERE 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 | Extra                                               |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
 
|    1 | PRIMARY     | t3    | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where                                         |
 
|    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
 
2 rows in set (0,001 sec)



 Comments   
Comment by Oleksandr Byelkin [ 2021-05-19 ]

fix to ex->all_selects_list and ok to push

Comment by Sergei Petrunia [ 2021-05-28 ]

Some general background.

Reading the code, one can see two kinds of JOIN optimization:

1. Optimization with intent to execute the JOIN
2. Optimization with intent to produce a query plan, but not execute the JOIN.

Which kind of optimization is done is governed by (join->select_options & SELECT_DESCRIBE). Note that also there is (select_lex->options & SELECT_DESCRIBE). I am not sure if these two are equivalent (seems so).

( A question: We have a feature where "cheap" subqueries can be evaluated at the optimization phase. But before we know if the subquery is cheap, we need to optimize it... When we are optimizing it, should we use SELECT_DESCRIBE flag or not?
Answer: look at JOIN::optimize_constant_subqueries... it temporarily clears SELECT_DESCRIBE flag from select_lex->options... That is, it is valid for EXPLAIN to call some subqueries with SELECT_DESCRIBE flag and some without)

Comment by Sergei Petrunia [ 2021-05-28 ]

Prepared statement variant

mysql_update calls st_select_lex::optimize_unflattened_subqueries which calls
JOIN::optimize.

Execution hits these lines:

  if (const_table_map != found_const_table_map &&
 
      !(select_options & SELECT_DESCRIBE))
 
  {
 
    // There is at least one empty const table
 
    zero_result_cause= "no matching row in const table";
 
    ...
 
    goto setup_subq_exit;

We have:

(select_options & SELECT_DESCRIBE) = 0

which is not entirely correct (this is EXPLAIN statement and we will never execute this query even as "cheap-to-compute subselect")

Non-prepared-statement case

The subquery is invoked with 

(select_options & SELECT_DESCRIBE) = 1

The above if-branch is not taken. We proceed a bit further in the query (I don't see much value in what is executed) until these lines:

  if (conds && const_table_map != found_const_table_map &&
 
      (select_options & SELECT_DESCRIBE))
 
  {
 
    conds=new (thd->mem_root) Item_int(thd, (longlong) 0, 1); // Always false
 
  }

...then into make_join_select() which computes the Item_int and returns 1:

  if (make_join_select(this, select, conds))
 
  {
 
    zero_result_cause=
 
      "Impossible WHERE noticed after reading const tables";
 
    ...
 
    goto setup_subq_exit;
 
  }

Comment by Sergei Petrunia [ 2021-05-28 ]

I don't see any reason why this should be fixed in a non-development version. This behavior does not cause any problems.

Comment by Sergei Petrunia [ 2021-05-28 ]

I've tried to investigate what's the point of having this check for SELECT_DESCRIBE:

  if (const_table_map != found_const_table_map &&
 
      !(select_options & SELECT_DESCRIBE))
 
  {
 
    // There is at least one empty const table
 
    zero_result_cause= "no matching row in const table";
 
    ...
 
    goto setup_subq_exit;

I don't see any reason for having this. The check itself is very ancient, it was there in the "Import changeset" in 2000.

Comment by Sergei Petrunia [ 2021-05-28 ]

I would suggest this patch:

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
 
index ce706209017..56f727654d2 100644
 
--- a/sql/sql_select.cc
 
+++ b/sql/sql_select.cc
 
@@ -1624,13 +1624,13 @@ JOIN::optimize_inner()
 
     DBUG_PRINT("error",("Error: initialize_tables() failed"));
 
     DBUG_RETURN(1);                            // error == -1
 
   }
 
-  if (const_table_map != found_const_table_map &&
 
-      !(select_options & SELECT_DESCRIBE))
 
+  if (const_table_map != found_const_table_map)
 
   {
 
     // There is at least one empty const table
 
     zero_result_cause= "no matching row in const table";
 
     DBUG_PRINT("error",("Error: %s", zero_result_cause));
 
     error= 0;
 
+    select_lex->mark_const_derived(zero_result_cause); // psergey
 
     handle_implicit_grouping_with_window_funcs();
 
     goto setup_subq_exit;
 
   }

It causes A LOT of differences in EXPLAIN output, though.
The most common change I observe when running MTR is like so:

-1      PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 
+1      PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table

And the cause is as described in the above comments: when there is a const table where we couldn't find the row, the execution doesn't leave with zero_result_cause= "no matching row in const table". Instead, it proceeds further where the WHERE clause is changed into Item_int(0). Then, make_join_select() computes it and sets "Impossible where" status.

Comment by Sergei Petrunia [ 2021-05-28 ]

The second most popular difference is

-1      SIMPLE  t1      system  NULL    NULL    NULL    NULL    0       const row not found
 
+1      SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table

which I don't find meaningful

Comment by Sergei Petrunia [ 2021-05-28 ]

This piece looks like some hack:

  if (conds && const_table_map != found_const_table_map &&
 
      (select_options & SELECT_DESCRIBE))
 
  {
 
    conds=new (thd->mem_root) Item_int(thd, (longlong) 0, 1); // Always false
 
  }

It was introduced by https://github.com/mariadb/server/commit/8762539d477d949a5bd4c2df8b03b28727e5ee98.

Comment by Sergei Petrunia [ 2021-05-28 ]

There are two approaches to addressing the issue (= making the EXPLAIN output the same):

A. Unify PS and non-PS execution paths: make sure they both either pass or don't pass the SELECT_DESCRIBE flag when invoking child select's JOIN::optimize

B. Make JOIN::optimize produce the same output regardless of whether SELECT_DESCRIBE flag is set.

My comments above have been mostly about B.
As far as I understand, Dmitry's efforts were about A.

Comment by Sergei Petrunia [ 2021-05-28 ]

This patch fixes the test failures in bb-10.2-MDEV-25576 : 

diff --git a/sql/sql_base.cc b/sql/sql_base.cc
 
index f11027546c1..01ec985227c 100644
 
--- a/sql/sql_base.cc
 
+++ b/sql/sql_base.cc
 
@@ -8806,9 +8806,7 @@ void promote_select_describe_flag_if_needed(LEX *lex)
 
 {
 
   if (lex->describe)
 
   {
 
-    SELECT_LEX *sl= lex->all_selects_list;
 
-    for (; sl; sl= sl->next_select_in_list())
 
-      sl->options|= SELECT_DESCRIBE;
 
+    lex->select_lex.options |= SELECT_DESCRIBE;
 
   }
 
 }

Comment by Dmitry Shulga [ 2021-05-30 ]

For 10.4 and upper the following changeset should applied instead the original one from commit.

diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
 
index 9fa1e015274..ff3e48f53ed 100644
 
--- a/sql/sql_delete.cc
 
+++ b/sql/sql_delete.cc
 
@@ -378,6 +378,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);
 
 
 
+  thd->lex->promote_select_describe_flag_if_needed();
 
+
 
   if (mysql_prepare_delete(thd, table_list, select_lex->with_wild,
 
                            select_lex->item_list, &conds,
 
                            &delete_while_scanning))
 
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
 
index eb21419c7af..16bdf580317 100644
 
--- a/sql/sql_lex.h
 
+++ b/sql/sql_lex.h
 
@@ -4595,6 +4595,16 @@ struct LEX: public Query_tables_list
 
   bool resolve_references_to_cte(TABLE_LIST *tables,
 
                                  TABLE_LIST **tables_last);
 
 
 
+  /**
 
+    Turn on the SELECT_DESCRIBE flag for the primary SELECT_LEX of
 
+    the EXPLAIN UPDATE/DELETE statement.
 
+  */
 
+
 
+  void promote_select_describe_flag_if_needed()
 
+  {
 
+    if (describe)
 
+      builtin_select.options |= SELECT_DESCRIBE;
 
+  }
 
 };
 
 
 
 
 
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
 
index 85db99f3a62..5d72dd88e7f 100644
 
--- a/sql/sql_update.cc
 
+++ b/sql/sql_update.cc
 
@@ -470,6 +470,8 @@ int mysql_update(THD *thd,
 
   want_privilege= (table_list->view ? UPDATE_ACL :
 
                    table_list->grant.want_privilege);
 
 #endif
 
+  thd->lex->promote_select_describe_flag_if_needed();
 
+
 
   if (mysql_prepare_update(thd, table_list, &conds, order_num, order))
 
     DBUG_RETURN(1);

Comment by Sergei Petrunia [ 2021-06-02 ]

Input from the last optimizer call:

  • There's lack of agreement about the desired/acceptable level of verbosity in
    commit comments. Will follow up on this.
  • For this particular patch: if some code has cleared the SELECT_DESCRIBE,
    maybe it should be also responsible for setting it back (if I understood this
    correctly)
  • The attempt to remove "if SELECT_DESCRIBE.. " should be followed up on in a
    separate patch, for 10.7.
Generated at Thu Feb 08 09:38:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.