diff options
Diffstat (limited to 'mysql-test/r/group_min_max.result')
| -rw-r--r-- | mysql-test/r/group_min_max.result | 28 |
1 files changed, 28 insertions, 0 deletions
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 25cd4a25279..a4cb8da5cce 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -3733,5 +3733,33 @@ id MIN(a) MAX(a) 4 2001-01-04 2001-01-04 DROP TABLE t1; # +# MDEV-17039: Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4 +# and use_stat_tables= PREFERABLY +# +CREATE TABLE t1 (a INT, b INT,c INT DEFAULT 0, INDEX (a,b)); +INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5), +(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set @@optimizer_use_condition_selectivity=4; +set @@use_stat_tables=PREFERABLY; +explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 8 100.00 +1 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 2 100.00 Using index +2 MATERIALIZED t1 range NULL a 5 NULL 8 100.00 Using index for group-by +Warnings: +Note 1003 select `test`.`t1_outer`.`a` AS `a` from <materialize> (select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` `t1_outer` where (`test`.`t1_outer`.`a` = `<subquery2>`.`max(b)`) +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set @@use_stat_tables=@save_use_stat_tables; +explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 8 100.00 +1 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 2 100.00 Using index +2 MATERIALIZED t1 range NULL a 5 NULL 8 100.00 Using index for group-by +Warnings: +Note 1003 select `test`.`t1_outer`.`a` AS `a` from <materialize> (select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` `t1_outer` where (`test`.`t1_outer`.`a` = `<subquery2>`.`max(b)`) +drop table t1; +# # End of 10.0 tests # |