revision-id: fd0d355e7c9a6756bc66946bf76080623d128c2d (mariadb-10.3.6-226-gfd0d355) parent(s): 88d89ee0bae24b71416c2af4f4c2f2be7b6a033a author: Varun Gupta committer: Varun Gupta timestamp: 2019-03-11 18:36:36 +0530 message: MDEV-18471: Optimizer trace: multi-part key ranges are printed incorrectly Keys with multi-key parts were not being printed correctly, only the first key part was getting printed. Fixed it by making sure append_range_all_keyparts function is called for the remaining keyparts. --- mysql-test/main/opt_trace.result | 425 +++++++++++++++++++++++++++++++++++++++ mysql-test/main/opt_trace.test | 17 ++ sql/opt_range.cc | 3 +- 3 files changed, 443 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 4c3e2b3..307a81c 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -6022,3 +6022,428 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { set @@optimizer_switch= @save_optimizer_switch; drop table t1,t2; set optimizer_trace='enabled=off'; +# +# MDEV-18471: Optimizer trace: multi-part key ranges are printed incorrectly +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table one_k (a int); +insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C; +create table t1 ( a int, b int, c int, d int, key (a,b), key(c,d)); +insert into t1 select a,a,a,a from one_k; +set optimizer_trace=1; +explain format=json select * from t1 where a > 10 and b < 10 and c=0 and d=1; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["a", "c"], + "key": "c", + "key_length": "10", + "used_key_parts": ["c", "d"], + "ref": ["const", "const"], + "rows": 1, + "filtered": 98.9, + "attached_condition": "t1.a > 10 and t1.b < 10" + } + } +} +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACe; +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES +explain format=json select * from t1 where a > 10 and b < 10 and c=0 and d=1 { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select t1.a AS a,t1.b AS b,t1.c AS c,t1.d AS d from t1 where t1.a > 10 and t1.b < 10 and t1.c = 0 and t1.d = 1" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "t1.a > 10 and t1.b < 10 and t1.c = 0 and t1.d = 1", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "t1.a > 10 and t1.b < 10 and multiple equal(0, t1.c) and multiple equal(1, t1.d)" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "t1.a > 10 and t1.b < 10 and multiple equal(0, t1.c) and multiple equal(1, t1.d)" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "t1.a > 10 and t1.b < 10 and multiple equal(0, t1.c) and multiple equal(1, t1.d)" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [ + { + "table": "t1", + "field": "c", + "equals": "0", + "null_rejecting": false + }, + { + "table": "t1", + "field": "d", + "equals": "1", + "null_rejecting": false + } + ] + }, + { + "rows_estimation": [ + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 1000, + "cost": 208.25 + }, + "potential_range_indexes": [ + { + "index": "a", + "usable": true, + "key_parts": ["a", "b"] + }, + { + "index": "c", + "usable": true, + "key_parts": ["c", "d"] + } + ], + "setup_range_conditions": [], + "group_index_range": { + "chosen": false, + "cause": "no group by or distinct" + }, + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "a", + "ranges": ["10 < a AND NULL < b < 10"], + "rowid_ordered": false, + "using_mrr": false, + "index_only": false, + "rows": 989, + "cost": 1272.8, + "chosen": false, + "cause": "cost" + }, + { + "index": "c", + "ranges": ["0 <= c <= 0 AND 1 <= d <= 1"], + "rowid_ordered": true, + "using_mrr": false, + "index_only": false, + "rows": 1, + "cost": 2.3783, + "chosen": true + } + ], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": [] + }, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "range_scan", + "index": "c", + "rows": 1, + "ranges": ["0 <= c <= 0 AND 1 <= d <= 1"] + }, + "rows_for_plan": 1, + "cost_for_plan": 2.3783, + "chosen": true + } + } + }, + { + "selectivity_for_indexes": [ + { + "index_name": "c", + "selectivity_from_index": 0.001 + }, + { + "index_name": "a", + "selectivity_from_index": 0.989 + } + ], + "selectivity_for_columns": [], + "cond_selectivity": 9.9e-4 + } + ] + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "ref", + "index": "c", + "used_range_estimates": true, + "rows": 1, + "cost": 2, + "chosen": true + }, + { + "type": "scan", + "chosen": false, + "cause": "cost" + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": "t1.c = 0 and t1.d = 1 and t1.a > 10 and t1.b < 10", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": "t1.a > 10 and t1.b < 10" + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] +} 0 0 +explain format=json select * from t1 force index(a) where a=10 and b=20; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "10", + "used_key_parts": ["a", "b"], + "ref": ["const", "const"], + "rows": 1, + "filtered": 100 + } + } +} +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACe; +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES +explain format=json select * from t1 force index(a) where a=10 and b=20 { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select t1.a AS a,t1.b AS b,t1.c AS c,t1.d AS d from t1 FORCE INDEX (a) where t1.a = 10 and t1.b = 20" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "t1.a = 10 and t1.b = 20", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "multiple equal(10, t1.a) and multiple equal(20, t1.b)" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "multiple equal(10, t1.a) and multiple equal(20, t1.b)" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "multiple equal(10, t1.a) and multiple equal(20, t1.b)" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [ + { + "table": "t1", + "field": "a", + "equals": "10", + "null_rejecting": false + }, + { + "table": "t1", + "field": "b", + "equals": "20", + "null_rejecting": false + } + ] + }, + { + "rows_estimation": [ + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 1000, + "cost": 2e308 + }, + "potential_range_indexes": [ + { + "index": "a", + "usable": true, + "key_parts": ["a", "b"] + }, + { + "index": "c", + "usable": false, + "cause": "not applicable" + } + ], + "setup_range_conditions": [], + "group_index_range": { + "chosen": false, + "cause": "no group by or distinct" + }, + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "a", + "ranges": ["10 <= a <= 10 AND 20 <= b <= 20"], + "rowid_ordered": true, + "using_mrr": false, + "index_only": false, + "rows": 1, + "cost": 2.3783, + "chosen": true + } + ], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": [] + }, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "range_scan", + "index": "a", + "rows": 1, + "ranges": ["10 <= a <= 10 AND 20 <= b <= 20"] + }, + "rows_for_plan": 1, + "cost_for_plan": 2.3783, + "chosen": true + } + } + }, + { + "selectivity_for_indexes": [ + { + "index_name": "a", + "selectivity_from_index": 0.001 + } + ], + "selectivity_for_columns": [], + "cond_selectivity": 0.001 + } + ] + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "ref", + "index": "a", + "used_range_estimates": true, + "rows": 1, + "cost": 2, + "chosen": true + }, + { + "type": "scan", + "chosen": false, + "cause": "cost" + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": "t1.a = 10 and t1.b = 20", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": null + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] +} 0 0 +drop table t1,t0,one_k; diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index e59a11f..86f2a82 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -374,3 +374,20 @@ select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; set @@optimizer_switch= @save_optimizer_switch; drop table t1,t2; set optimizer_trace='enabled=off'; + +--echo # +--echo # MDEV-18471: Optimizer trace: multi-part key ranges are printed incorrectly +--echo # + +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table one_k (a int); +insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C; +create table t1 ( a int, b int, c int, d int, key (a,b), key(c,d)); +insert into t1 select a,a,a,a from one_k; +set optimizer_trace=1; +explain format=json select * from t1 where a > 10 and b < 10 and c=0 and d=1; +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACe; +explain format=json select * from t1 force index(a) where a=10 and b=20; +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACe; +drop table t1,t0,one_k; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 1e60bb9..92161f7 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -15812,8 +15812,7 @@ static void append_range_all_keyparts(Json_writer_array *range_trace, if (keypart_range->next_key_part && keypart_range->next_key_part->part == - keypart_range->part + 1 && - keypart_range->is_singlepoint()) + keypart_range->part + 1) { append_range_all_keyparts(range_trace, range_string, range_so_far, keypart_range->next_key_part, key_parts);