diff options
| -rw-r--r-- | mysql-test/r/group_min_max.result | 21 | ||||
| -rw-r--r-- | mysql-test/t/group_min_max.test | 22 | ||||
| -rw-r--r-- | sql/sql_select.cc | 8 |
3 files changed, 49 insertions, 2 deletions
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 7583aa14db8..2e5193f8563 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -2286,3 +2286,24 @@ Variable_name Value Handler_read_key 8 Handler_read_next 1 DROP TABLE t1,t2,t3; +CREATE TABLE t1 (a int, INDEX idx(a)); +INSERT INTO t1 VALUES +(4), (2), (1), (2), (4), (2), (1), (4), +(4), (2), (1), (2), (2), (4), (1), (4); +EXPLAIN SELECT DISTINCT(a) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range NULL idx 5 NULL 9 Using index for group-by +SELECT DISTINCT(a) FROM t1; +a +1 +2 +4 +EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range NULL idx 5 NULL 9 Using index for group-by +SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; +a +1 +2 +4 +DROP TABLE t1; diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index b3049498f90..e1010c9fcfe 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -870,3 +870,25 @@ DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x SHOW STATUS LIKE 'handler_read__e%'; DROP TABLE t1,t2,t3; + +# +# Bug#25602: queries with DISTINCT and SQL_BIG_RESULT hint +# for which loose scan optimization is applied +# + +CREATE TABLE t1 (a int, INDEX idx(a)); +INSERT INTO t1 VALUES + (4), (2), (1), (2), (4), (2), (1), (4), + (4), (2), (1), (2), (2), (4), (1), (4); + +EXPLAIN SELECT DISTINCT(a) FROM t1; +SELECT DISTINCT(a) FROM t1; +EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; +SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; + +DROP TABLE t1; + + + + + diff --git a/sql/sql_select.cc b/sql/sql_select.cc index f4450bf393f..c73eda10421 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -12552,10 +12552,14 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order, /* When there is SQL_BIG_RESULT do not sort using index for GROUP BY, - and thus force sorting on disk. + and thus force sorting on disk unless a group min-max optimization + is going to be used as it is applied now only for one table queries + with covering indexes. */ if ((order != join->group_list || - !(join->select_options & SELECT_BIG_RESULT)) && + !(join->select_options & SELECT_BIG_RESULT) || + select && select->quick && + select->quick->get_type() == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX) && test_if_skip_sort_order(tab,order,select_limit,0)) DBUG_RETURN(0); for (ORDER *ord= join->order; ord; ord= ord->next) |