[Commits] 84b659c2546: MDEV-26249: Crash in Explain_node::print_explain_for_children with slow query log

psergey sergey at mariadb.com
Tue Nov 23 16:55:40 EET 2021


revision-id: 84b659c25467439680a9c161615ee7fe0699842e (mariadb-10.3.31-70-g84b659c2546)
parent(s): 9962cda52722b77c2a7e0314bbaa2e4f963f55c1
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-11-23 17:55:40 +0300
message:

MDEV-26249: Crash in Explain_node::print_explain_for_children with slow query log

The problem was caused by this:
- The select in derived table uses two-phase optimization (due to a
  possible LATERAL DERIVED).
- The primary select has "Impossible where" and so it short-cuts its
  optimization.
- The optimization for the SELECT in the derived table is never finished,
  and EXPLAIN data structure has a dangling pointer to select #2.

Fixed with this: if the select has "Impossible where", do not add links
to derived table subselects into the EXPLAIN data structure. We are not
going to execute those anyway.

---
 mysql-test/main/derived_cond_pushdown.result |  1 -
 mysql-test/main/derived_view.result          |  1 -
 mysql-test/main/explain_innodb.result        | 15 +++++++++++++++
 mysql-test/main/explain_innodb.test          | 17 +++++++++++++++++
 mysql-test/main/join.result                  |  1 -
 mysql-test/main/ps.result                    |  3 ---
 mysql-test/main/view.result                  |  1 -
 sql/sql_select.cc                            |  4 +++-
 8 files changed, 35 insertions(+), 8 deletions(-)

diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index d2c116913f4..a21bb48509a 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -16898,7 +16898,6 @@ EXPLAIN EXTENDED
 SELECT * FROM v1 JOIN v2 ON v1.f = v2.f;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 Warnings:
 Note	1003	/* select#1 */ select NULL AS `f`,`v2`.`f` AS `f` from `test`.`t1` `a` straight_join `test`.`t1` `b` join `test`.`v2` where 0
 DROP VIEW v1,v2;
diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result
index 65a1adcaddd..d1ce6c8e623 100644
--- a/mysql-test/main/derived_view.result
+++ b/mysql-test/main/derived_view.result
@@ -1558,7 +1558,6 @@ EXPLAIN EXTENDED
 SELECT * FROM (SELECT b FROM v2 WHERE b = 0) t WHERE b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using temporary; Using filesort
 Warnings:
 Note	1003	/* select#1 */ select `v2`.`b` AS `b` from `test`.`v2` where 0
 DROP VIEW v1,v2;
diff --git a/mysql-test/main/explain_innodb.result b/mysql-test/main/explain_innodb.result
new file mode 100644
index 00000000000..ca7479a8d6a
--- /dev/null
+++ b/mysql-test/main/explain_innodb.result
@@ -0,0 +1,15 @@
+#
+# MDEV-26249: Crash in in Explain_node::print_explain_for_children while writing to the slow query log
+#
+set @sql_tmp=@@slow_query_log;
+SET GLOBAL slow_query_log = 1;
+SET long_query_time = 0.000000;
+SET log_slow_verbosity = 'explain';
+CREATE TABLE t1 ( id varchar(50), KEY (id)) engine=innodb;
+SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1>0;
+id
+select 1;
+1
+1
+SET GLOBAL slow_query_log = @sql_tmp;
+drop table t1;
diff --git a/mysql-test/main/explain_innodb.test b/mysql-test/main/explain_innodb.test
new file mode 100644
index 00000000000..7282616d3d0
--- /dev/null
+++ b/mysql-test/main/explain_innodb.test
@@ -0,0 +1,17 @@
+--echo #
+--echo # MDEV-26249: Crash in in Explain_node::print_explain_for_children while writing to the slow query log
+--echo #
+
+--source include/have_innodb.inc
+
+set @sql_tmp=@@slow_query_log;
+SET GLOBAL slow_query_log = 1;
+SET long_query_time = 0.000000;
+SET log_slow_verbosity = 'explain';
+
+CREATE TABLE t1 ( id varchar(50), KEY (id)) engine=innodb;
+SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1>0;
+select 1;
+
+SET GLOBAL slow_query_log = @sql_tmp;
+drop table t1;
diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result
index e667eab7eb9..88f67fb8074 100644
--- a/mysql-test/main/join.result
+++ b/mysql-test/main/join.result
@@ -1499,7 +1499,6 @@ EXPLAIN EXTENDED
 SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
 Note	1003	/* select#1 */ select NULL AS `i1`,`v2`.`i2` AS `i2`,`v2`.`a` AS `a`,`v2`.`b` AS `b` from `test`.`v2` where `v2`.`i2` = NULL and `v2`.`a` < `v2`.`b`
 DROP VIEW v2;
diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result
index 051f40cfd78..222afd6c1f3 100644
--- a/mysql-test/main/ps.result
+++ b/mysql-test/main/ps.result
@@ -160,7 +160,6 @@ prepare stmt1 from @stmt ;
 execute stmt1 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-6	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 5	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 4	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
@@ -168,7 +167,6 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 execute stmt1 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-6	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 5	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 4	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
@@ -176,7 +174,6 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-6	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 5	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 4	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result
index 34dd8f3c8dc..37678a1b50d 100644
--- a/mysql-test/main/view.result
+++ b/mysql-test/main/view.result
@@ -5714,7 +5714,6 @@ ON t36.f36 = v60.f60
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 drop table t0, t1, t2, t3, t4, t5, t6, t7, t8, t9,
 t10, t11, t12, t13, t14, t15, t16, t17, t18,
 t19, t20, t21, t22, t23, t24, t25, t26, t27,
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index fe02e7b44e4..b2c1d1e3db5 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -26322,6 +26322,7 @@ int JOIN::save_explain_data_intern(Explain_query *output,
           elimination.
       (2) they are not merged derived tables
       (3) they are not hanging CTEs (they are needed for execution)
+      (4) this is not a derived subquery in a degenerate select.
     */
     if (!(tmp_unit->item && tmp_unit->item->eliminated) &&    // (1)
         (!tmp_unit->derived ||
@@ -26329,7 +26330,8 @@ int JOIN::save_explain_data_intern(Explain_query *output,
         (!tmp_unit->with_element  ||
          (tmp_unit->derived &&
           tmp_unit->derived->derived_result &&
-          !tmp_unit->with_element->is_hanging_recursive())))  // (3)
+          !tmp_unit->with_element->is_hanging_recursive())) &&   // (3)
+        !(tmp_unit->derived && zero_result_cause)) // (4)
    {
       explain->add_child(tmp_unit->first_select()->select_number);
     }


More information about the commits mailing list