MDEV-30324: Wrong result upon SELECT DISTINCT ... WITH TIES

WITH TIES would not take effect if SELECT DISTINCT was used in a
context where an INDEX is used to resolve the ORDER BY clause.

WITH TIES relies on the `JOIN::order` to contain the non-constant
fields to test the equality of ORDER BY fiels required for WITH TIES.

The cause of the problem was a premature removal of the `JOIN::order`
member during a DISTINCT optimization. This lead to WITH TIES code assuming
ORDER BY only contained "constant" elements.

Disable this optimization when WITH TIES is in effect.

(side-note: the order by removal does not impact any current tests, thus
it will be removed in a future version)

Reviewed by: monty@mariadb.org

2 files changed, 47 insertions, 0 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;