From c301c2e739c642199f9e4c62d867dc7bd5ef0fd1 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Mon, 18 May 2020 19:28:46 -0400 Subject: [PATCH] WITH TIES: number of rows is optional and defaults to one FETCH FIRST .. ONLY implements this correctly, but we missed to include it for FETCH FIRST .. WITH TIES in commit 357889eb17bb. Author: Vik Fearing Discussion: https://postgr.es/m/6aa690ef-551d-e24f-2690-c38c2442947c@postgresfriends.org --- src/backend/parser/gram.y | 8 ++++++++ src/test/regress/expected/limit.out | 17 +++++++++++++++++ src/test/regress/sql/limit.sql | 5 +++++ 3 files changed, 30 insertions(+) diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 3c78f2d1b5..a24b30f06f 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -11816,6 +11816,14 @@ limit_clause: n->limitOption = LIMIT_OPTION_COUNT; $$ = n; } + | FETCH first_or_next row_or_rows WITH TIES + { + SelectLimit *n = (SelectLimit *) palloc(sizeof(SelectLimit)); + n->limitOffset = NULL; + n->limitCount = makeIntConst(1, -1); + n->limitOption = LIMIT_OPTION_WITH_TIES; + $$ = n; + } ; offset_clause: diff --git a/src/test/regress/expected/limit.out b/src/test/regress/expected/limit.out index a4e175855c..e6f6809fbe 100644 --- a/src/test/regress/expected/limit.out +++ b/src/test/regress/expected/limit.out @@ -576,6 +576,23 @@ SELECT thousand 0 (10 rows) +SELECT thousand + FROM onek WHERE thousand < 5 + ORDER BY thousand FETCH FIRST ROWS WITH TIES; + thousand +---------- + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 +(10 rows) + SELECT thousand FROM onek WHERE thousand < 5 ORDER BY thousand FETCH FIRST 1 ROW WITH TIES; diff --git a/src/test/regress/sql/limit.sql b/src/test/regress/sql/limit.sql index afce5019b2..d2d4ef132d 100644 --- a/src/test/regress/sql/limit.sql +++ b/src/test/regress/sql/limit.sql @@ -161,6 +161,10 @@ SELECT thousand FROM onek WHERE thousand < 5 ORDER BY thousand FETCH FIRST 2 ROW WITH TIES; +SELECT thousand + FROM onek WHERE thousand < 5 + ORDER BY thousand FETCH FIRST ROWS WITH TIES; + SELECT thousand FROM onek WHERE thousand < 5 ORDER BY thousand FETCH FIRST 1 ROW WITH TIES; @@ -168,6 +172,7 @@ SELECT thousand SELECT thousand FROM onek WHERE thousand < 5 ORDER BY thousand FETCH FIRST 2 ROW ONLY; + -- should fail SELECT ''::text AS two, unique1, unique2, stringu1 FROM onek WHERE unique1 > 50 -- 2.39.5