diff options
| -rw-r--r-- | mysql-test/main/fetch_first.result | 28 | ||||
| -rw-r--r-- | mysql-test/main/fetch_first.test | 19 | ||||
| -rw-r--r-- | sql/sql_select.cc | 2 |
3 files changed, 48 insertions, 1 deletions
diff --git a/mysql-test/main/fetch_first.result b/mysql-test/main/fetch_first.result index 69a0336d722..df182381d1c 100644 --- a/mysql-test/main/fetch_first.result +++ b/mysql-test/main/fetch_first.result @@ -1378,3 +1378,31 @@ a bar foo DROP TABLE t; +# +# MDEV-30324: Wrong result upon SELECT DISTINCT .. WITH TIES using index +# +CREATE TABLE t1 (a int, b char(3), KEY (a)); +INSERT INTO t1 VALUES (2,'foo'),(3,'bar'),(3,'bar'),(3,'zzz'); +EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 1 ROWS WITH TIES; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 5 NULL 1 Using temporary +SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 1 ROWS WITH TIES; +a b +2 foo +EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 2 ROWS WITH TIES; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 5 NULL 2 Using temporary +SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 2 ROWS WITH TIES; +a b +2 foo +3 bar +3 zzz +EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 3 ROWS WITH TIES; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort +SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 3 ROWS WITH TIES; +a b +2 foo +3 bar +3 zzz +DROP TABLE t1; diff --git a/mysql-test/main/fetch_first.test b/mysql-test/main/fetch_first.test index 62cdd717f81..98bbf1ca06b 100644 --- a/mysql-test/main/fetch_first.test +++ b/mysql-test/main/fetch_first.test @@ -1059,3 +1059,22 @@ SELECT a FROM t ORDER BY a FETCH FIRST 2 ROWS WITH TIES; # Cleanup DROP TABLE t; + +--echo # +--echo # MDEV-30324: Wrong result upon SELECT DISTINCT .. WITH TIES using index +--echo # +CREATE TABLE t1 (a int, b char(3), KEY (a)); +INSERT INTO t1 VALUES (2,'foo'),(3,'bar'),(3,'bar'),(3,'zzz'); + +EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 1 ROWS WITH TIES; +--sorted_result +SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 1 ROWS WITH TIES; +EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 2 ROWS WITH TIES; +--sorted_result +SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 2 ROWS WITH TIES; +EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 3 ROWS WITH TIES; +--sorted_result +SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 3 ROWS WITH TIES; + +# Cleanup +DROP TABLE t1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index e0beda6ec61..57bf9363b47 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4170,7 +4170,7 @@ JOIN::optimize_distinct() } /* Optimize "select distinct b from t1 order by key_part_1 limit #" */ - if (order && skip_sort_order) + if (order && skip_sort_order && !unit->lim.is_with_ties()) { /* Should already have been optimized away */ DBUG_ASSERT(ordered_index_usage == ordered_index_order_by); |