From d314bd266491baf0954d13fa51dc22b730a6f4d1 Mon Sep 17 00:00:00 2001 From: Monty Date: Wed, 2 Feb 2022 14:09:21 +0200 Subject: 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 --- mysql-test/main/group_min_max.result | 16 ++++++++++++++++ mysql-test/main/group_min_max.test | 11 +++++++++++ sql/opt_range.cc | 2 +- 3 files changed, 28 insertions(+), 1 deletion(-) 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"; -- cgit v1.2.1