Date: Mon, 18 May 2020 13:03:23 -0400
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Vik Fearing <vik@postgresfriends.org>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Missing grammar production for WITH TIES
Message-ID: <20200518170323.GA14610@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="opJtzjQTFsWo+cga"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <6aa690ef-551d-e24f-2690-c38c2442947c@postgresfriends.org>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk


--opJtzjQTFsWo+cga
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

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

--opJtzjQTFsWo+cga
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="0001-WITH-TIES-number-of-rows-is-optional-and-default-to-.patch"

From bdd724370215a550586e49a2f8ce2f554bdf79f4 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@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


--opJtzjQTFsWo+cga--