diff options
| author | Vicențiu Ciorbaru <vicentiu@mariadb.org> | 2021-04-18 22:58:34 +0300 |
|---|---|---|
| committer | Vicențiu Ciorbaru <vicentiu@mariadb.org> | 2021-04-21 14:09:17 +0300 |
| commit | aeccdddedd5d3d306dd317e6b366d50bc807726b (patch) | |
| tree | 2bbd7733374407baad2c7b4547d5d0b87ccca247 /mysql-test/main | |
| parent | 44a6af65f56ccbcb9a7ca6ba1d021a2b23c07666 (diff) | |
| download | mariadb-git-aeccdddedd5d3d306dd317e6b366d50bc807726b.tar.gz | |
MDEV-25441 WITH TIES is not respected with SQL_BUFFER_RESULT and constant in ORDER BY
Pushing LIMIT to temp aggregation table is possible, but not when WITH
TIES is used. In a degenerate case with constant ORDER BY, the constant
gets removed and the code assumed the limit is push-able.
Ensure that if WITH TIES is present, that this does not happen.
Diffstat (limited to 'mysql-test/main')
| -rw-r--r-- | mysql-test/main/fetch_first.result | 34 | ||||
| -rw-r--r-- | mysql-test/main/fetch_first.test | 11 |
2 files changed, 45 insertions, 0 deletions
diff --git a/mysql-test/main/fetch_first.result b/mysql-test/main/fetch_first.result index 1e63581e0a5..7bb89633081 100644 --- a/mysql-test/main/fetch_first.result +++ b/mysql-test/main/fetch_first.result @@ -1334,3 +1334,37 @@ a b sum(a) 1 2 1 1 3 1 drop table t1; +# +# MDEV-25441 +# WITH TIES is not respected with SQL_BUFFER_RESULT and constant in ORDER BY +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +explain SELECT SQL_BUFFER_RESULT 1 AS f FROM t1 ORDER BY f FETCH NEXT 2 ROW WITH TIES; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using temporary +SELECT SQL_BUFFER_RESULT 1 AS f FROM t1 ORDER BY f FETCH NEXT 2 ROW WITH TIES; +f +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +SELECT 1 AS f FROM t1 ORDER BY f FETCH NEXT 2 ROW WITH TIES; +f +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +drop table t1; diff --git a/mysql-test/main/fetch_first.test b/mysql-test/main/fetch_first.test index 6995c1cb5b1..76c0a8b8b39 100644 --- a/mysql-test/main/fetch_first.test +++ b/mysql-test/main/fetch_first.test @@ -1028,6 +1028,17 @@ group by a, b order by (select 1), a fetch first 1 rows with ties; +drop table t1; + +--echo # +--echo # MDEV-25441 +--echo # WITH TIES is not respected with SQL_BUFFER_RESULT and constant in ORDER BY +--echo # +CREATE TABLE t1 (a INT); +INSERT INTO t1 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +explain SELECT SQL_BUFFER_RESULT 1 AS f FROM t1 ORDER BY f FETCH NEXT 2 ROW WITH TIES; +SELECT SQL_BUFFER_RESULT 1 AS f FROM t1 ORDER BY f FETCH NEXT 2 ROW WITH TIES; +SELECT 1 AS f FROM t1 ORDER BY f FETCH NEXT 2 ROW WITH TIES; drop table t1; |