From 4afa3b64c4e5ca12d755124befc373e0f35ff1e1 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Vicen=C8=9Biu=20Ciorbaru?= Date: Wed, 15 Feb 2023 16:20:25 +0200 Subject: 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 --- mysql-test/main/fetch_first.test | 19 +++++++++++++++++++ 1 file changed, 19 insertions(+) (limited to 'mysql-test/main/fetch_first.test') 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; -- cgit v1.2.1