From b34bb81eaf0f1fd653f93257fbbdaa4c31ea8ea5 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Mon, 15 Mar 2021 22:48:30 -0700 Subject: 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 0f9fdefb81a..fb07ba76fb2 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 8bbc9e6a040..d7da9fe8c96 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 02b95dae44a..868d8b26f4b 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); -- cgit v1.2.1 From 4ca4d606ac96cabe013eb5c9e4eda92233cf1697 Mon Sep 17 00:00:00 2001 From: Daniel Black Date: Fri, 19 Mar 2021 11:14:47 +1100 Subject: myseek: AIX has no "tell" AIX detects tell in the configure however it really isn't there. Use the my_seek aka lseek implementation. --- mysys/my_seek.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/mysys/my_seek.c b/mysys/my_seek.c index 6a370b0ad43..db364ccddda 100644 --- a/mysys/my_seek.c +++ b/mysys/my_seek.c @@ -86,7 +86,7 @@ my_off_t my_tell(File fd, myf MyFlags) DBUG_ENTER("my_tell"); DBUG_PRINT("my",("fd: %d MyFlags: %lu",fd, MyFlags)); DBUG_ASSERT(fd >= 0); -#if defined (HAVE_TELL) && !defined (_WIN32) +#if defined (HAVE_TELL) && !defined (_WIN32) && !defined(_AIX) pos= tell(fd); #else pos= my_seek(fd, 0L, MY_SEEK_CUR,0); -- cgit v1.2.1 From 4e825b0e8ae07e1e847cbbc3c5b7203ae5b96a89 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 19 Mar 2021 11:46:07 +0100 Subject: update libmariadb This partially reverts 66106130a6c --- libmariadb | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/libmariadb b/libmariadb index a746c3af449..fc431a035a2 160000 --- a/libmariadb +++ b/libmariadb @@ -1 +1 @@ -Subproject commit a746c3af449a8754e78ad7971e59e79af7957cdb +Subproject commit fc431a035a21ac1d4ef25d9d3cd8c4d7e64a8ee7 -- cgit v1.2.1