revision-id: 38c89a95752ee7a59f9366af0cefacf3a71a6fd0 (mariadb-10.4.7-102-g38c89a95752) parent(s): eb0a10b07222b01d40dee24e61aac9502256481f author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-10-04 20:16:39 +0300 message: MDEV-20740: Odd computations in calculate_cond_selectivity_for_table Make SEL_ARG graph traversal code in sel_arg_range_seq_next() set max_key_parts first. (Pushing to 10.4 first) --- mysql-test/main/index_intersect_innodb.result | 8 ++++---- sql/opt_range.cc | 16 ++++++++++------ sql/opt_range_mrr.cc | 5 ++++- 3 files changed, 18 insertions(+), 11 deletions(-) diff --git a/mysql-test/main/index_intersect_innodb.result b/mysql-test/main/index_intersect_innodb.result index 854bcd75e5c..fd07f6b41c6 100644 --- a/mysql-test/main/index_intersect_innodb.result +++ b/mysql-test/main/index_intersect_innodb.result @@ -470,17 +470,17 @@ EXPLAIN SELECT * FROM City WHERE ID BETWEEN 501 AND 1000 AND Population > 700000 AND Country LIKE 'C%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country,Population 4,7,4 NULL # Using sort_intersect(PRIMARY,Country,Population); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Population,Country 4,4,7 NULL # Using sort_intersect(PRIMARY,Population,Country); Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 1 AND 500 AND Population > 700000 AND Country LIKE 'C%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country,Population 4,7,4 NULL # Using sort_intersect(PRIMARY,Country,Population); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Population,Country 4,4,7 NULL # Using sort_intersect(PRIMARY,Population,Country); Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 2001 AND 2500 AND Population > 300000 AND Country LIKE 'H%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country 4,7 NULL # Using sort_intersect(PRIMARY,Country); Using where +1 SIMPLE City range PRIMARY,Population,Country Country 7 NULL # Using index condition; Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3701 AND 4000 AND Population > 1000000 @@ -724,7 +724,7 @@ EXPLAIN SELECT * FROM City WHERE ID BETWEEN 1 AND 500 AND Population > 700000 AND Country LIKE 'C%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country,Population 4,7,4 NULL # Using sort_intersect(PRIMARY,Country,Population); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Population,Country 4,4,7 NULL # Using sort_intersect(PRIMARY,Population,Country); Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 654fab7810a..c47da283e9c 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -315,7 +315,7 @@ class PARAM : public RANGE_OPT_PARAM */ key_map possible_keys; longlong baseflag; - uint max_key_part, range_count; + uint max_key_parts, range_count; bool quick; // Don't calulate possible keys @@ -7387,7 +7387,7 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree, index_scan->idx= idx; index_scan->keynr= keynr; index_scan->key_info= ¶m->table->key_info[keynr]; - index_scan->used_key_parts= param->max_key_part+1; + index_scan->used_key_parts= param->max_key_parts; index_scan->range_count= param->range_count; index_scan->records= found_records; index_scan->sel_arg= key; @@ -11042,7 +11042,7 @@ ha_rows check_quick_select(PARAM *param, uint idx, bool index_only, seq.start= tree; param->range_count=0; - param->max_key_part=0; + param->max_key_parts=0; seq.is_ror_scan= TRUE; if (file->index_flags(keynr, 0, TRUE) & HA_KEY_SCAN_NOT_ROR) @@ -11055,9 +11055,13 @@ ha_rows check_quick_select(PARAM *param, uint idx, bool index_only, *mrr_flags|= HA_MRR_NO_ASSOCIATION | HA_MRR_SORTED; bool pk_is_clustered= file->primary_key_is_clustered(); + // TODO: param->max_key_parts holds 0 now, and not the #keyparts used. + // Passing wrong second argument to index_flags() makes no difference for + // most storage engines but might be an issue for MyRocks with certain + // datatypes. if (index_only && - (file->index_flags(keynr, param->max_key_part, 1) & HA_KEYREAD_ONLY) && - !(file->index_flags(keynr, param->max_key_part, 1) & HA_CLUSTERED_INDEX)) + (file->index_flags(keynr, param->max_key_parts, 1) & HA_KEYREAD_ONLY) && + !(file->index_flags(keynr, param->max_key_parts, 1) & HA_CLUSTERED_INDEX)) *mrr_flags |= HA_MRR_INDEX_ONLY; if (param->thd->lex->sql_command != SQLCOM_SELECT) @@ -11088,7 +11092,7 @@ ha_rows check_quick_select(PARAM *param, uint idx, bool index_only, if (update_tbl_stats) { param->table->quick_keys.set_bit(keynr); - param->table->quick_key_parts[keynr]= param->max_key_part+1; + param->table->quick_key_parts[keynr]= param->max_key_parts; param->table->quick_n_ranges[keynr]= param->range_count; param->table->quick_condition_rows= MY_MIN(param->table->quick_condition_rows, rows); diff --git a/sql/opt_range_mrr.cc b/sql/opt_range_mrr.cc index d3a1e155fb7..4afa06a7ca0 100644 --- a/sql/opt_range_mrr.cc +++ b/sql/opt_range_mrr.cc @@ -247,6 +247,7 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range) uint min_key_length= (uint)(cur->min_key - seq->param->min_key); range->ptr= (char*)(intptr)(key_tree->part); + uint max_key_parts; if (cur->min_key_flag & GEOM_FLAG) { range->range_flag= cur->min_key_flag; @@ -256,9 +257,11 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range) range->start_key.length= min_key_length; range->start_key.keypart_map= make_prev_keypart_map(cur->min_key_parts); range->start_key.flag= (ha_rkey_function) (cur->min_key_flag ^ GEOM_FLAG); + max_key_parts= cur->min_key_parts; } 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; @@ -336,7 +339,7 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range) } } seq->param->range_count++; - seq->param->max_key_part=MY_MAX(seq->param->max_key_part,key_tree->part); + seq->param->max_key_parts= MY_MAX(seq->param->max_key_parts, max_key_parts); return 0; }