From 6dc6c22c14fe204dbac43b6132c5bd130c69aba1 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 21 Apr 2023 18:49:52 -0700 Subject: MDEV-31085 Crash when processing multi-update using view with optimizer_trace on This bug caused server crash when processing a multi-update statement that used views if optimizer tracing was enabled. The bug was introduced in the patch for MDEV-30539 that could incorrectly detect the most top level selects of queries if views were used in them. Approved by Oleksandr Byelkin --- mysql-test/main/opt_trace.result | 356 +++++++++++++++++++++++++++++++++++++++ mysql-test/main/opt_trace.test | 19 +++ sql/sql_select.cc | 4 +- 3 files changed, 377 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index a8b391ffbe8..a7a8fb88e6d 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -8505,5 +8505,361 @@ SELECT a FROM t1 WHERE (a,b) in (SELECT @c,@d); a DROP TABLE t1; # +# MDEV-31085: multi-update using view with optimizer trace enabled +# +SET SESSION optimizer_trace = 'enabled=on'; +CREATE TABLE t (a int, b int); +CREATE VIEW v AS SELECT 1 AS c UNION SELECT 2 AS c; +INSERT INTO t VALUES (0,4),(5,6); +UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a; +SELECT * FROM information_schema.optimizer_trace; +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES +UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a { + "steps": [ + { + "view": { + "table": "v", + "select_id": 2, + "algorithm": "materialized" + } + }, + { + "join_preparation": { + "select_id": 2, + "steps": [ + { + "expanded_query": "/* select#2 */ select 1 AS c" + } + ] + } + }, + { + "join_preparation": { + "select_id": 3, + "steps": [ + { + "expanded_query": "/* select#3 */ select 2 AS c" + } + ] + } + }, + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "/* select#1 */ update t join v set t.b = t.a,t.a = v.c where v.c < t.a" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "v.c < t.a", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "v.c < t.a" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "v.c < t.a" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "v.c < t.a" + } + ] + } + }, + { + "join_optimization": { + "select_id": 2, + "steps": [] + } + }, + { + "join_optimization": { + "select_id": 3, + "steps": [] + } + }, + { + "table_dependencies": [ + { + "table": "t", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + }, + { + "table": "", + "row_may_be_null": false, + "map_bit": 1, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [] + }, + { + "rows_estimation": [ + { + "table": "t", + "table_scan": { + "rows": 2, + "cost": 2.0044 + } + }, + { + "table": "", + "table_scan": { + "rows": 2, + "cost": 2 + } + } + ] + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 2, + "cost": 2.0044, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 2, + "cost": 2.0044, + "uses_join_buffering": false + } + }, + "rows_for_plan": 2, + "cost_for_plan": 2.4044, + "rest_of_plan": [ + { + "plan_prefix": ["t"], + "table": "", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 2, + "cost": 2, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 2, + "cost": 2, + "uses_join_buffering": true + } + }, + "rows_for_plan": 4, + "cost_for_plan": 5.2044, + "estimated_join_cardinality": 4 + } + ] + }, + { + "plan_prefix": [], + "table": "", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 2, + "cost": 2, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 2, + "cost": 2, + "uses_join_buffering": false + } + }, + "rows_for_plan": 2, + "cost_for_plan": 2.4, + "rest_of_plan": [ + { + "plan_prefix": [""], + "table": "t", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 2, + "cost": 2.0044, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 2, + "cost": 2.0044, + "uses_join_buffering": true + } + }, + "rows_for_plan": 4, + "cost_for_plan": 5.2044, + "pruned_by_cost": true + } + ] + } + ] + }, + { + "best_join_order": ["t", ""] + }, + { + "attaching_conditions_to_tables": { + "original_condition": "v.c < t.a", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t", + "attached": null + }, + { + "table": "", + "attached": "v.c < t.a" + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [ + { + "join_execution": { + "select_id": 2, + "steps": [] + } + }, + { + "join_execution": { + "select_id": 3, + "steps": [] + } + }, + { + "join_preparation": { + "select_id": "fake", + "steps": [ + { + "expanded_query": "select c AS c from dual" + } + ] + } + }, + { + "join_optimization": { + "select_id": "fake", + "steps": [ + { + "table_dependencies": [ + { + "table": "union", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "rows_estimation": [ + { + "table": "union", + "table_scan": { + "rows": 2, + "cost": 10.1 + } + } + ] + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "union", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 2, + "cost": 10.1, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 2, + "cost": 10.1, + "uses_join_buffering": false + } + }, + "rows_for_plan": 2, + "cost_for_plan": 10.5, + "estimated_join_cardinality": 2 + } + ] + }, + { + "best_join_order": ["union"] + }, + { + "attaching_conditions_to_tables": { + "original_condition": null, + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "union", + "attached": null + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": "fake", + "steps": [] + } + } + ] + } + } + ] +} 0 0 +SELECT * FROM t; +a b +0 4 +1 5 +SET optimizer_trace=DEFAULT; +DROP VIEW v; +DROP TABLE t; +# # End of 10.4 tests # diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index 0785d828a07..e0be5360069 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -677,6 +677,25 @@ INSERT INTO t1 VALUES (0,0); SELECT a FROM t1 WHERE (a,b) in (SELECT @c,@d); DROP TABLE t1; +--echo # +--echo # MDEV-31085: multi-update using view with optimizer trace enabled +--echo # + +SET SESSION optimizer_trace = 'enabled=on'; + +CREATE TABLE t (a int, b int); +CREATE VIEW v AS SELECT 1 AS c UNION SELECT 2 AS c; +INSERT INTO t VALUES (0,4),(5,6); +UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a; +SELECT * FROM information_schema.optimizer_trace; + +SELECT * FROM t; + +SET optimizer_trace=DEFAULT; + +DROP VIEW v; +DROP TABLE t; + --echo # --echo # End of 10.4 tests --echo # diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 0651c1d58bd..03a2c3d0853 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -28050,7 +28050,7 @@ void st_select_lex::print_item_list(THD *thd, String *str, outer_select() can not be used here because it is for name resolution and will return NULL at any end of name resolution chain (view/derived) */ - bool top_level= (get_master()->get_master() == 0); + bool top_level= (get_master() == &thd->lex->unit); List_iterator_fast it(item_list); Item *item; while ((item= it++)) @@ -28157,7 +28157,7 @@ void st_select_lex::print(THD *thd, String *str, enum_query_type query_type) return; } - bool top_level= (get_master()->get_master() == 0); + bool top_level= (get_master() == &thd->lex->unit); enum explainable_cmd_type sel_type= SELECT_CMD; if (top_level) sel_type= get_explainable_cmd_type(thd); -- cgit v1.2.1