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
Date: Mon, 18 May 2020 13:03:23 -0400
Message-ID: <20200518170323.GA14610@alvherre.pgsql> (raw)
In-Reply-To: <6aa690ef-551d-e24f-2690-c38c2442947c@postgresfriends.org>
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
Attachments:0001-WITH-TIES-number-of-rows-is-optional-and-default-to-.patch (text/x-diff, 2.3K) download
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.yindex 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.outindex 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.sqlindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: alvherre@2ndquadrant.com, vik@postgresfriends.org, pgsql-hackers@lists.postgresql.org
Subject: Re: Missing grammar production for WITH TIES
In-Reply-To: <20200518170323.GA14610@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox