On 2020-May-18, Vik Fearing wrote:
> The syntax for FETCH FIRST allows the <fetch first quantity> to be
> absent (implying 1).
>
> We implement this correctly for ONLY, but WITH TIES didn't get the memo.
Oops, yes. I added a test. Will get this pushed immediately after I
see beta1 produced.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From bdd724370215a550586e49a2f8ce2f554bdf79f4 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Mon, 18 May 2020 11:57:05 -0400
Subject: [PATCH] WITH TIES: number of rows is optional and default to one
Author: Vik Fearing
---
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.20.1
