revision-id: ed78af9a28dca51f04dce77bcbeaac2cef51c97f (mariadb-10.2.31-785-ged78af9) parent(s): 90780bb5a949d5e27b39df4adaa1d7b1ad98948e author: Igor Babaev committer: Igor Babaev timestamp: 2021-03-15 22:48:30 -0700 message: MDEV-25112 MIN/MAX aggregation over an indexed column may return wrong result If a query with implicit grouping contains in MIN/MAX set function in the select list over a column that is a part of an index then the query might be subject to MIN/MAX optimization. With this optimization the server performs a look-up into an index, fetches a value of the column C used in the MIN/MAX function and substitute the MIN/MAX expression for this value. This allows to eliminate the table containing C from further join processing. In order the optimization to be applied the WHERE condition must be a conjunction of simple equality/inequality predicates or/and BETWEEN predicates. The bug fixed in the patch resulted in fetching a wrong value from the index used for MIN/MAX optimization. It may happened when a BETWEEN predicate containing the MIN/MAX value followed a strict inequality. Approved by dmitry.shulga@mariadb.com --- mysql-test/r/func_group.result | 34 ++++++++++++++++++++++++++++++++++ mysql-test/t/func_group.test | 28 ++++++++++++++++++++++++++++ sql/opt_sum.cc | 3 +++ 3 files changed, 65 insertions(+) diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 0f9fdef..fb07ba7 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -2458,3 +2458,37 @@ select count(*)+sleep(0) from t1; count(*)+sleep(0) 2 drop table t1; +# +# MDEV-25112: MIN/MAX optimization for query containing BETWEEN in WHERE +# +create table t1 (a int) engine=myisam; +insert into t1 values (267), (273), (287), (303), (308); +select max(a) from t1 where a < 303 and (a between 267 AND 287); +max(a) +287 +explain select max(a) from t1 where a < 303 and (a between 267 AND 287); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +select min(a) from t1 where a > 267 and (a between 273 AND 303); +min(a) +273 +explain select min(a) from t1 where a > 267 and (a between 273 AND 303); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +create index idx on t1(a); +select max(a) from t1 where a < 303 and (a between 267 AND 287); +max(a) +287 +explain select max(a) from t1 where a < 303 and (a between 267 AND 287); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +select min(a) from t1 where a > 267 and (a between 273 AND 303); +min(a) +273 +explain select min(a) from t1 where a > 267 and (a between 273 AND 303); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1; +# +# End of 10.2 tests +# diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 8bbc9e6..d7da9fe 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -1703,4 +1703,32 @@ select count(*)+sleep(0) from t1; drop table t1; +--echo # +--echo # MDEV-25112: MIN/MAX optimization for query containing BETWEEN in WHERE +--echo # + +create table t1 (a int) engine=myisam; +insert into t1 values (267), (273), (287), (303), (308); + +let $q1= +select max(a) from t1 where a < 303 and (a between 267 AND 287); +let $q2= +select min(a) from t1 where a > 267 and (a between 273 AND 303); + +eval $q1; +eval explain $q1; +eval $q2; +eval explain $q2; + +create index idx on t1(a); +eval $q1; +eval explain $q1; +eval $q2; +eval explain $q2; + +drop table t1; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc index 02b95da..868d8b2 100644 --- a/sql/opt_sum.cc +++ b/sql/opt_sum.cc @@ -830,7 +830,10 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo, if (is_field_part) { if (between || eq_type) + { *range_fl&= ~(NO_MAX_RANGE | NO_MIN_RANGE); + *range_fl&= ~(max_fl ? NEAR_MAX : NEAR_MIN); + } else { *range_fl&= ~(max_fl ? NO_MAX_RANGE : NO_MIN_RANGE);