diff options
| author | Monty <monty@mariadb.org> | 2022-02-02 14:09:21 +0200 |
|---|---|---|
| committer | Monty <monty@mariadb.org> | 2022-02-08 14:32:28 +0200 |
| commit | d314bd266491baf0954d13fa51dc22b730a6f4d1 (patch) | |
| tree | fe23890f8847157ffde44d095284ca1d1a63ea87 | |
| parent | a1c23807530c6ffd5d400f71d9226bca5b91fb62 (diff) | |
| download | mariadb-git-d314bd266491baf0954d13fa51dc22b730a6f4d1.tar.gz | |
MDEV-27442 Wrong result upon query with DISTINCT and EXISTS subquery
The problem was that get_best_group_min_max() did not check if fields used
by the "group_min_max optimization" where used in sub queries.
Because of this, it did not detect that a key (b,a) was used in the WHERE
clause for the statement:
SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 ).
Fixed by also traversing the sub queries when checking if a field is used.
This disables group_min_max_optimization for the above query.
Reviewer: Sergei Petrunia
| -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 | 2 |
3 files changed, 28 insertions, 1 deletions
diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result index a17f3f09c3b..f264f221ab5 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -4027,3 +4027,19 @@ drop table t1; # # End of 10.1 tests # +# +# MDEV-27442 Wrong result upon query with DISTINCT and EXISTS subquery +# +CREATE TABLE t1 (a int, b int, KEY b (b,a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (0,100),(2,100),(2,101),(3,102); +# Must not use Using index for group-by +explain SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL b 10 NULL 4 Using where; Using index +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 ); +b +100 +101 +102 +DROP TABLE t1; diff --git a/mysql-test/main/group_min_max.test b/mysql-test/main/group_min_max.test index 526552dda92..4622fe473a0 100644 --- a/mysql-test/main/group_min_max.test +++ b/mysql-test/main/group_min_max.test @@ -1689,3 +1689,14 @@ drop table t1; --echo # --echo # End of 10.1 tests --echo # + +--echo # +--echo # MDEV-27442 Wrong result upon query with DISTINCT and EXISTS subquery +--echo # + +CREATE TABLE t1 (a int, b int, KEY b (b,a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (0,100),(2,100),(2,101),(3,102); +--echo # Must not use Using index for group-by +explain SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 ); +SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 ); +DROP TABLE t1; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index ae24a257aaa..e3d26896900 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -14000,7 +14000,7 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) key_part_range[1]= last_part; /* Check if cur_part is referenced in the WHERE clause. */ - if (join->conds->walk(&Item::find_item_in_field_list_processor, 0, + if (join->conds->walk(&Item::find_item_in_field_list_processor, true, key_part_range)) { cause= "keypart reference from where clause"; |