From 05bf5c020a085cccc442066866ee50df862cf85a Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Wed, 29 Apr 2020 01:13:52 +0300 Subject: MDEV-22401: Optimizer trace: multi-component range is not printed correctly KEY_MULTI_RANGE::range_flag does not have correct flag bits for per-endpoint flags (NEAR_MIN, NEAR_MAX, NO_MIN_RANGE, NO_MAX_RANGE). It only has bits for flags that describe both endpoints. So - Document this. - Switch optimizer trace to using {start|end}_key.flag values, instead. This fixes the bug. - Switch records_in_column_ranges() to doing that too. (This used to work, because KEY_MULTI_RANGE::range_flag had correct flag value for the last key component, and EITS only uses one-component pseudo-indexes) --- include/my_base.h | 8 +++++++- mysql-test/main/opt_trace.result | 31 +++++++++++++++++++++++++++++++ mysql-test/main/opt_trace.test | 12 ++++++++++++ sql/opt_range.cc | 31 +++++++++++++++++++++++-------- sql/opt_range_mrr.cc | 2 +- 5 files changed, 74 insertions(+), 10 deletions(-) diff --git a/include/my_base.h b/include/my_base.h index 68b3aaa6a13..44af7b45075 100644 --- a/include/my_base.h +++ b/include/my_base.h @@ -632,7 +632,13 @@ typedef struct st_key_multi_range key_range start_key; key_range end_key; range_id_t ptr; /* Free to use by caller (ptr to row etc) */ - uint range_flag; /* key range flags see above */ + /* + A set of range flags that describe both endpoints: UNIQUE_RANGE, + NULL_RANGE, EQ_RANGE, GEOM_FLAG. + (Flags that describe one endpoint, NO_{MIN|MAX}_RANGE, NEAR_{MIN|MAX} will + not be set here) + */ + uint range_flag; } KEY_MULTI_RANGE; diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 08c0895e839..1fd22e52243 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -8261,4 +8261,35 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.semijoin_table_pullout')) } ] drop table t1,t2,t3; +# +# MDEV-22401: Optimizer trace: multi-component range is not printed correctly +# +create table t1 (kp1 int, kp2 int, key(kp1, kp2)); +insert into t1 values (1,1),(1,5),(5,1),(5,5); +set optimizer_trace=1; +select * from t1 force index(kp1) where (kp1=2 and kp2 >=4); +kp1 kp2 +select JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) +[ + + [ + + { + "index": "kp1", + "ranges": + [ + "(2,4) <= (kp1,kp2) <= (2)" + ], + "rowid_ordered": false, + "using_mrr": false, + "index_only": true, + "rows": 1, + "cost": 1.3033, + "chosen": false, + "cause": "cost" + } + ] +] +drop table t1; set optimizer_trace='enabled=off'; diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index 51950f00781..09b3de77848 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -567,4 +567,16 @@ select * from t3 where (a,a) in (select t1.a, t2.a from t1, t2 where t1.b=t2.b); select JSON_DETAILED(JSON_EXTRACT(trace, '$**.semijoin_table_pullout')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; drop table t1,t2,t3; + +--echo # +--echo # MDEV-22401: Optimizer trace: multi-component range is not printed correctly +--echo # + +create table t1 (kp1 int, kp2 int, key(kp1, kp2)); +insert into t1 values (1,1),(1,5),(5,1),(5,5); +set optimizer_trace=1; +select * from t1 force index(kp1) where (kp1=2 and kp2 >=4); +select JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +drop table t1; + set optimizer_trace='enabled=off'; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 312999ddc17..a3c5dbcb199 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3206,8 +3206,18 @@ double records_in_column_ranges(PARAM *param, uint idx, key_range *min_endp, *max_endp; min_endp= range.start_key.length? &range.start_key : NULL; max_endp= range.end_key.length? &range.end_key : NULL; - rows= get_column_range_cardinality(field, min_endp, max_endp, - range.range_flag); + int range_flag= range.range_flag; + + if (!range.start_key.length) + range_flag |= NO_MIN_RANGE; + if (!range.end_key.length) + range_flag |= NO_MAX_RANGE; + if (range.start_key.flag == HA_READ_AFTER_KEY) + range_flag |= NEAR_MIN; + if (range.start_key.flag == HA_READ_BEFORE_KEY) + range_flag |= NEAR_MAX; + + rows= get_column_range_cardinality(field, min_endp, max_endp, range_flag); if (DBL_MAX == rows) { total_rows= DBL_MAX; @@ -15814,24 +15824,29 @@ void print_range(String *out, const KEY_PART_INFO *key_part, return; } - if (!(flag & NO_MIN_RANGE)) + if (range->start_key.length) { print_key_value(out, key_part, range->start_key.key, range->start_key.length); - if (flag & NEAR_MIN) + if (range->start_key.flag == HA_READ_AFTER_KEY) out->append(STRING_WITH_LEN(" < ")); - else + else if (range->start_key.flag == HA_READ_KEY_EXACT || + range->start_key.flag == HA_READ_KEY_OR_NEXT) out->append(STRING_WITH_LEN(" <= ")); + else + out->append(STRING_WITH_LEN(" ? ")); } print_keyparts_name(out, key_part, n_key_parts, keypart_map); - if (!(flag & NO_MAX_RANGE)) + if (range->end_key.length) { - if (flag & NEAR_MAX) + if (range->end_key.flag == HA_READ_BEFORE_KEY) out->append(STRING_WITH_LEN(" < ")); - else + else if (range->end_key.flag == HA_READ_AFTER_KEY) out->append(STRING_WITH_LEN(" <= ")); + else + out->append(STRING_WITH_LEN(" ? ")); print_key_value(out, key_part, range->end_key.key, range->end_key.length); } diff --git a/sql/opt_range_mrr.cc b/sql/opt_range_mrr.cc index 4afa06a7ca0..267d764bb3b 100644 --- a/sql/opt_range_mrr.cc +++ b/sql/opt_range_mrr.cc @@ -262,7 +262,6 @@ walk_up_n_right: else { max_key_parts= MY_MAX(cur->min_key_parts, cur->max_key_parts); - range->range_flag= cur->min_key_flag | cur->max_key_flag; range->start_key.key= seq->param->min_key; range->start_key.length= (uint)(cur->min_key - seq->param->min_key); @@ -298,6 +297,7 @@ walk_up_n_right: !memcmp(seq->param->min_key, seq->param->max_key, // (2) range->start_key.length); + range->range_flag= 0; if (is_eq_range_pred) { range->range_flag = EQ_RANGE; -- cgit v1.2.1