revision-id: e29f077e4d8d28931b5747299b4a39227d151b73 (mariadb-10.3.31-70-ge29f077e4d8) parent(s): 9962cda52722b77c2a7e0314bbaa2e4f963f55c1 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-11-23 17:55:08 +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 --- 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); }