diff options
| author | Rex <rex.johnston@mariadb.com> | 2023-03-07 09:29:56 +1200 |
|---|---|---|
| committer | Rex <rex.johnston@mariadb.com> | 2023-03-15 10:04:44 +1200 |
| commit | 602d26b9ad991d557bfdd1881f6136611bdc99b2 (patch) | |
| tree | 6767a554a0a2c1b398d08a760f0c395f4f08b67c | |
| parent | b1646d0433c98662c50af029a121d681ddfb7a2b (diff) | |
| download | mariadb-git-bb-10.5-MDEV-30605.tar.gz | |
MDEV-30605 Wrong result while using index for group-bybb-10.5-MDEV-30605
incorrect result when group function applied on a primary key
| -rw-r--r-- | mysql-test/main/group_min_max.result | 16 | ||||
| -rw-r--r-- | mysql-test/main/group_min_max.test | 11 | ||||
| -rw-r--r-- | sql/opt_range.cc | 6 |
3 files changed, 30 insertions, 3 deletions
diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result index d1bd4d8cedb..65ec07d0b97 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -881,10 +881,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by +1 SIMPLE t1 index NULL idx_t1_1 163 NULL 512 Using where; Using index explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by +1 SIMPLE t1 index NULL idx_t1_1 163 NULL 512 Using where; Using index explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by @@ -4083,5 +4083,17 @@ MIN(pk) 1 DROP TABLE t1, t2; # +# MDEV-30605 Wrong result while using index for group-by +# +CREATE TABLE t (pk INT primary key, a int, key(a)); +INSERT INTO t VALUES (1,-1),(2,8),(3,5),(4,-1),(5,10), (6,-1); +SELECT MIN(pk), a FROM t WHERE pk <> 1 GROUP BY a; +MIN(pk) a +4 -1 +3 5 +2 8 +5 10 +DROP TABLE t; +# # End of 10.5 tests # diff --git a/mysql-test/main/group_min_max.test b/mysql-test/main/group_min_max.test index 7de57d75d36..181d11e7d2a 100644 --- a/mysql-test/main/group_min_max.test +++ b/mysql-test/main/group_min_max.test @@ -1738,5 +1738,16 @@ SELECT MIN(pk) FROM t1, t2; DROP TABLE t1, t2; --echo # +--echo # MDEV-30605 Wrong result while using index for group-by +--echo # + +CREATE TABLE t (pk INT primary key, a int, key(a)); +INSERT INTO t VALUES (1,-1),(2,8),(3,5),(4,-1),(5,10), (6,-1); + +SELECT MIN(pk), a FROM t WHERE pk <> 1 GROUP BY a; + +DROP TABLE t; + +--echo # --echo # End of 10.5 tests --echo # diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 223799a3235..6c990e6e405 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3037,7 +3037,11 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, if (unlikely(thd->trace_started())) group_trp->trace_basic_info(¶m, &grp_summary); - if (group_trp->read_cost < best_read_time || force_group_by) + // if there is no range tree and there IS an attached cond + // QUICK_GROUP_MIN_MAX_SELECT will not filter results correctly + // do not choose it + if ((group_trp->read_cost < best_read_time || force_group_by) + && (tree || !cond) ) { grp_summary.add("chosen", true); best_trp= group_trp; |