diff options
author | Sergei Petrunia <sergey@mariadb.com> | 2023-03-28 10:25:59 +0300 |
---|---|---|
committer | Sergei Petrunia <sergey@mariadb.com> | 2023-04-18 14:42:47 +0300 |
commit | be7ef6566fab6088b5222eae184226ed6b5994d3 (patch) | |
tree | 3854160d481c3a266e75252d0bbdc6dcad90c643 /sql | |
parent | 6c196090c8c265bfd93df1e2ee6b18cda2b1d2d8 (diff) | |
download | mariadb-git-be7ef6566fab6088b5222eae184226ed6b5994d3.tar.gz |
MDEV-30605: Wrong result while using index for group-by
A GROUP BY query which uses "MIN(pk)" and has "pk<>const" in the
WHERE clause would produce wrong result when handled with "Using index
for group-by". Here "pk" column is the table's primary key.
The problem was introduced by fix for MDEV-23634. It made the range
optimizer to not produce ranges for conditions in form "pk != const".
However, LooseScan code requires that the optimizer is able to
convert the condition on the MIN/MAX column into an equivalent range.
The range is used to locate the row that has the MIN/MAX value.
LooseScan checks this in check_group_min_max_predicates(). This fix
makes the code in that function to take into account that "pk != const"
does not produce a range.
Diffstat (limited to 'sql')
-rw-r--r-- | sql/opt_range.cc | 46 |
1 files changed, 28 insertions, 18 deletions
diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 223799a3235..82b19e23fd4 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -461,7 +461,7 @@ void print_range_for_non_indexed_field(String *out, Field *field, static void print_min_range_operator(String *out, const ha_rkey_function flag); static void print_max_range_operator(String *out, const ha_rkey_function flag); -static bool is_field_an_unique_index(RANGE_OPT_PARAM *param, Field *field); +static bool is_field_an_unique_index(Field *field); /* SEL_IMERGE is a list of possible ways to do index merge, i.e. it is @@ -7752,8 +7752,13 @@ SEL_TREE *Item_func_ne::get_func_mm_tree(RANGE_OPT_PARAM *param, If this condition is a "col1<>...", where there is a UNIQUE KEY(col1), do not construct a SEL_TREE from it. A condition that excludes just one row in the table is not selective (unless there are only a few rows) + + Note: this logic must be in sync with code in + check_group_min_max_predicates(). That function walks an Item* condition + and checks if the range optimizer would produce an equivalent range for + it. */ - if (is_field_an_unique_index(param, field)) + if (param->using_real_indexes && is_field_an_unique_index(field)) DBUG_RETURN(NULL); DBUG_RETURN(get_ne_mm_tree(param, field, value, value)); } @@ -7865,7 +7870,7 @@ SEL_TREE *Item_func_in::get_func_mm_tree(RANGE_OPT_PARAM *param, - if there are a lot of constants, the overhead of building and processing enormous range list is not worth it. */ - if (is_field_an_unique_index(param, field)) + if (param->using_real_indexes && is_field_an_unique_index(field)) DBUG_RETURN(0); /* Get a SEL_TREE for "(-inf|NULL) < X < c_0" interval. */ @@ -8574,24 +8579,18 @@ SEL_TREE *Item_equal::get_mm_tree(RANGE_OPT_PARAM *param, Item **cond_ptr) In the future we could also add "almost unique" indexes where any value is present only in a few rows (but necessarily exactly one row) */ -static bool is_field_an_unique_index(RANGE_OPT_PARAM *param, Field *field) +static bool is_field_an_unique_index(Field *field) { DBUG_ENTER("is_field_an_unique_index"); - - // The check for using_real_indexes is there because of the heuristics - // this function is used for. - if (param->using_real_indexes) + key_map::Iterator it(field->key_start); + uint key_no; + while ((key_no= it++) != key_map::Iterator::BITMAP_END) { - key_map::Iterator it(field->key_start); - uint key_no; - while ((key_no= it++) != key_map::Iterator::BITMAP_END) + KEY *key_info= &field->table->key_info[key_no]; + if (key_info->user_defined_key_parts == 1 && + (key_info->flags & HA_NOSAME)) { - KEY *key_info= &field->table->key_info[key_no]; - if (key_info->user_defined_key_parts == 1 && - (key_info->flags & HA_NOSAME)) - { - DBUG_RETURN(true); - } + DBUG_RETURN(true); } } DBUG_RETURN(false); @@ -13475,7 +13474,7 @@ cost_group_min_max(TABLE* table, KEY *index_info, uint used_key_parts, - (C between const_i and const_j) - C IS NULL - C IS NOT NULL - - C != const + - C != const (unless C is the primary key) SA4. If Q has a GROUP BY clause, there are no other aggregate functions except MIN and MAX. For queries with DISTINCT, aggregate functions are allowed. @@ -14358,6 +14357,17 @@ check_group_min_max_predicates(Item *cond, Item_field *min_max_arg_item, if (!simple_pred(pred, args, &inv)) DBUG_RETURN(FALSE); + /* + Follow the logic in Item_func_ne::get_func_mm_tree(): condition + in form "tbl.primary_key <> const" is not used to produce intervals. + + If the condition doesn't have an equivalent interval, this means we + fail LooseScan's condition SA3. Return FALSE to indicate this. + */ + if (pred_type == Item_func::NE_FUNC && + is_field_an_unique_index(min_max_arg_item->field)) + DBUG_RETURN(FALSE); + if (args[0] && args[1]) // this is a binary function or BETWEEN { DBUG_ASSERT(pred->fixed_type_handler()); |