diff options
| -rw-r--r-- | mysql-test/r/group_min_max.result | 20 | ||||
| -rw-r--r-- | mysql-test/t/group_min_max.test | 16 | ||||
| -rw-r--r-- | sql/opt_range.cc | 37 | ||||
| -rw-r--r-- | sql/opt_range.h | 2 |
4 files changed, 75 insertions, 0 deletions
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index fe6f7c4ca55..826e642ce88 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -2142,3 +2142,23 @@ t1; id2 id3 id5 id4 id3 id6 id5 id1 1 1 1 1 1 1 1 1 DROP TABLE t1,t2,t3,t4,t5,t6; +CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b)); +INSERT INTO t1 VALUES (1,1),(1,2); +explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY,b PRIMARY 8 NULL 2 Using where; Using index for group-by +SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a; +MAX(b) a +1 1 +SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a; +MIN(b) a +2 1 +CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c)); +INSERT INTO t2 SELECT a,b,b FROM t1; +explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range PRIMARY PRIMARY 12 NULL 2 Using where; Using index for group-by +SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a; +MIN(c) +2 +DROP TABLE t1,t2; diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index 5427727a8f4..9f3fb5ea51e 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -794,3 +794,19 @@ SELECT * FROM t1; DROP TABLE t1,t2,t3,t4,t5,t6; + +# +# Bug#22342: No results returned for query using max and group by +# +CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b)); +INSERT INTO t1 VALUES (1,1),(1,2); + +explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a; +SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a; +SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a; +CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c)); +INSERT INTO t2 SELECT a,b,b FROM t1; +explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a; +SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a; + +DROP TABLE t1,t2; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 79cfbc72fe7..eb7d627de96 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -8371,6 +8371,7 @@ TRP_GROUP_MIN_MAX::make_quick(PARAM *param, bool retrieve_full_rows, quick->quick_prefix_select= NULL; quick->update_key_stat(); + quick->adjust_prefix_ranges(); DBUG_RETURN(quick); } @@ -8601,6 +8602,42 @@ bool QUICK_GROUP_MIN_MAX_SELECT::add_range(SEL_ARG *sel_range) /* + Opens the ranges if there are more conditions in quick_prefix_select than + the ones used for jumping through the prefixes. + + SYNOPSIS + QUICK_GROUP_MIN_MAX_SELECT::adjust_prefix_ranges() + + NOTES + quick_prefix_select is made over the conditions on the whole key. + It defines a number of ranges of length x. + However when jumping through the prefixes we use only the the first + few most significant keyparts in the range key. However if there + are more keyparts to follow the ones we are using we must make the + condition on the key inclusive (because x < "ab" means + x[0] < 'a' OR (x[0] == 'a' AND x[1] < 'b'). + To achive the above we must turn off the NEAR_MIN/NEAR_MAX +*/ +void QUICK_GROUP_MIN_MAX_SELECT::adjust_prefix_ranges () +{ + if (quick_prefix_select && + group_prefix_len < quick_prefix_select->max_used_key_length) + { + DYNAMIC_ARRAY *arr; + uint inx; + + for (inx= 0, arr= &quick_prefix_select->ranges; inx < arr->elements; inx++) + { + QUICK_RANGE *range; + + get_dynamic(arr, (gptr)&range, inx); + range->flag &= ~(NEAR_MIN | NEAR_MAX); + } + } +} + + +/* Determine the total number and length of the keys that will be used for index lookup. diff --git a/sql/opt_range.h b/sql/opt_range.h index 9474f2d469f..784dd648ad2 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -294,6 +294,7 @@ protected: friend class QUICK_SELECT_DESC; friend class QUICK_INDEX_MERGE_SELECT; friend class QUICK_ROR_INTERSECT_SELECT; + friend class QUICK_GROUP_MIN_MAX_SELECT; DYNAMIC_ARRAY ranges; /* ordered array of range ptrs */ QUICK_RANGE **cur_range; /* current element in ranges */ @@ -642,6 +643,7 @@ public: ~QUICK_GROUP_MIN_MAX_SELECT(); bool add_range(SEL_ARG *sel_range); void update_key_stat(); + void adjust_prefix_ranges(); bool alloc_buffers(); int init(); int reset(); |