diff options
| -rw-r--r-- | mysql-test/r/group_min_max.result | 39 | ||||
| -rw-r--r-- | mysql-test/t/group_min_max.test | 35 | ||||
| -rw-r--r-- | sql/opt_range.cc | 20 |
3 files changed, 90 insertions, 4 deletions
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 58be2af1bb8..1138a5e1d88 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -2443,4 +2443,43 @@ c 1 2 DROP TABLE t1; +# +# Bug #45386: Wrong query result with MIN function in field list, +# WHERE and GROUP BY clause +# +CREATE TABLE t (a INT, b INT, INDEX (a,b)); +INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1); +INSERT INTO t SELECT * FROM t; +# test MIN +#should use range with index for group by +EXPLAIN +SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t range NULL a 10 NULL 9 Using where; Using index for group-by +#should return 1 row +SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a; +a MIN(b) +2 1 +# test MAX +#should use range with index for group by +EXPLAIN +SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t range NULL a 10 NULL 9 Using where; Using index for group-by +#should return 1 row +SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a; +a MAX(b) +2 0 +# test 3 ranges and use the middle one +INSERT INTO t SELECT a, 2 FROM t; +#should use range with index for group by +EXPLAIN +SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t range NULL a 10 NULL 9 Using where; Using index for group-by +#should return 1 row +SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a; +a MAX(b) +2 1 +DROP TABLE t; End of 5.0 tests diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index e124bf9e786..163c170eaa0 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -957,4 +957,39 @@ SELECT DISTINCT c FROM t1 WHERE d=4; DROP TABLE t1; +--echo # +--echo # Bug #45386: Wrong query result with MIN function in field list, +--echo # WHERE and GROUP BY clause +--echo # + +CREATE TABLE t (a INT, b INT, INDEX (a,b)); +INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1); +INSERT INTO t SELECT * FROM t; + +--echo # test MIN +--echo #should use range with index for group by +EXPLAIN +SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a; +--echo #should return 1 row +SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a; + +--echo # test MAX +--echo #should use range with index for group by +EXPLAIN +SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a; +--echo #should return 1 row +SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a; + +--echo # test 3 ranges and use the middle one +INSERT INTO t SELECT a, 2 FROM t; + +--echo #should use range with index for group by +EXPLAIN +SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a; +--echo #should return 1 row +SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a; + +DROP TABLE t; + + --echo End of 5.0 tests diff --git a/sql/opt_range.cc b/sql/opt_range.cc index d52db41d64c..571a342fa17 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -9395,8 +9395,14 @@ int QUICK_GROUP_MIN_MAX_SELECT::next_min_in_range() /* Compare the found key with max_key. */ int cmp_res= key_cmp(index_info->key_part, max_key, real_prefix_len + min_max_arg_len); - if (!(((cur_range->flag & NEAR_MAX) && (cmp_res == -1)) || - (cmp_res <= 0))) + /* + The key is outside of the range if: + the interval is open and the key is equal to the maximum boundry + or + the key is greater than the maximum + */ + if (((cur_range->flag & NEAR_MAX) && cmp_res == 0) || + cmp_res > 0) { result = HA_ERR_KEY_NOT_FOUND; continue; @@ -9511,8 +9517,14 @@ int QUICK_GROUP_MIN_MAX_SELECT::next_max_in_range() /* Compare the found key with min_key. */ int cmp_res= key_cmp(index_info->key_part, min_key, real_prefix_len + min_max_arg_len); - if (!(((cur_range->flag & NEAR_MIN) && (cmp_res == 1)) || - (cmp_res >= 0))) + /* + The key is outside of the range if: + the interval is open and the key is equal to the minimum boundry + or + the key is less than the minimum + */ + if (((cur_range->flag & NEAR_MIN) && cmp_res == 0) || + cmp_res < 0) continue; } /* If we got to this point, the current key qualifies as MAX. */ |