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

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6
Fix Version/s: 10.6.2, 10.2.39, 10.3.30, 10.4.20, 10.5.11
Component/s: Prepared Statements
Labels:
None

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)

Attachments

Issue Links

relates to

MDEV-25845 Remove one SELECT_DESCRIBE check in JOIN::optimize_inner()

Open

MDEV-25846 Remove one SELECT_DESCRIBE check in JOIN::optimize_inner()

Closed

Activity

Ascending order - Click to sort in descending order

Oleksandr Byelkin added a comment - 2021-05-19 12:38

fix to ex->all_selects_list and ok to push

Oleksandr Byelkin added a comment - 2021-05-19 12:38 fix to ex->all_selects_list and ok to push

Sergei Petrunia added a comment - 2021-05-28 13:04 - edited

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)

Sergei Petrunia added a comment - 2021-05-28 13:04 - edited 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)

Sergei Petrunia added a comment - 2021-05-28 13:05

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;

Sergei Petrunia added a comment - 2021-05-28 13:05 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; }

Sergei Petrunia added a comment - 2021-05-28 14:32

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

Sergei Petrunia added a comment - 2021-05-28 14:32 I don't see any reason why this should be fixed in a non-development version. This behavior does not cause any problems.

Sergei Petrunia added a comment - 2021-05-28 14:34 - edited

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.

Sergei Petrunia added a comment - 2021-05-28 14:34 - edited 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.

Sergei Petrunia added a comment - 2021-05-28 14:41

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.

Sergei Petrunia added a comment - 2021-05-28 14:41 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.

Sergei Petrunia added a comment - 2021-05-28 14:44

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

Sergei Petrunia added a comment - 2021-05-28 14:44 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

Sergei Petrunia added a comment - 2021-05-28 14:50

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.

Sergei Petrunia added a comment - 2021-05-28 14:50 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 .

Sergei Petrunia added a comment - 2021-05-28 16:08 - edited

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.

Sergei Petrunia added a comment - 2021-05-28 16:08 - edited 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.

Sergei Petrunia added a comment - 2021-05-28 18:59 - edited

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;

Sergei Petrunia added a comment - 2021-05-28 18:59 - edited 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; } }

Dmitry Shulga added a comment - 2021-05-30 16:07

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);

Dmitry Shulga added a comment - 2021-05-30 16:07 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);

Sergei Petrunia added a comment - 2021-06-02 13:39

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.

Sergei Petrunia added a comment - 2021-06-02 13:39 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.

People

Assignee:: Dmitry Shulga

Reporter:: Dmitry Shulga

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2021-05-01 02:17

Updated:: 2021-06-17 17:30

Resolved:: 2021-05-30 13:52

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server