On Thu, Nov 28, 2019 at 12:36 AM Alvaro Herrera <alvhe...@2ndquadrant.com>
wrote:
> Thanks.
>
> (I would suggest renaming the new state LIMIT_WINDOWEND_TIES, because it
> seems to convey what it does a little better.)
>
> I think you should add a /* fall-though */ comment after changing state.
> Like this (this flow seems clearer; also DRY):
>
> if (!node->noCount &&
> node->position - node->offset >= node->count)
> {
> if (node->limitOption == LIMIT_OPTION_COUNT)
> {
> node->lstate = LIMIT_WINDOWEND;
> return NULL;
> }
> else
> {
> node->lstate = LIMIT_WINDOWEND_TIES;
> /* fall-through */
> }
> }
> else
> ...
>
>
changed
> I've been playing with this a little more, and I think you've overlooked
> a few places in ExecLimit that need to be changed. In the regression
> database, I tried this:
>
> 55432 13devel 17282=# begin;
> BEGIN
> 55432 13devel 17282=# declare c1 cursor for select * from int8_tbl order
> by q1 fetch first 2 rows with ties;
> DECLARE CURSOR
> 55432 13devel 17282=# fetch all in c1;
> q1 │ q2
> ─────┼──────────────────
> 123 │ 456
> 123 │ 4567890123456789
> (2 filas)
>
> 55432 13devel 17282=# fetch all in c1;
> q1 │ q2
> ────┼────
> (0 filas)
>
> 55432 13devel 17282=# fetch forward all in c1;
> q1 │ q2
> ────┼────
> (0 filas)
>
> So far so good .. things look normal. But here's where things get
> crazy:
>
> 55432 13devel 17282=# fetch backward all in c1;
> q1 │ q2
> ──────────────────┼──────────────────
> 4567890123456789 │ 123
> 123 │ 4567890123456789
> (2 filas)
>
> (huh???)
>
> 55432 13devel 17282=# fetch backward all in c1;
> q1 │ q2
> ────┼────
> (0 filas)
>
> Okay -- ignoring the fact that we got a row that should not be in the
> result, we've exhausted the cursor going back. Let's scan it again from
> the start:
>
> 55432 13devel 17282=# fetch forward all in c1;
> q1 │ q2
> ──────────────────┼───────────────────
> 123 │ 4567890123456789
> 4567890123456789 │ 123
> 4567890123456789 │ 4567890123456789
> 4567890123456789 │ -4567890123456789
> (4 filas)
>
> This is just crazy.
>
> I think you need to stare a that thing a little harder.
>
>
This is because the new state didn't know about backward scan
and there was off by one error it scan one position past to detect
ties. The attached patch fix both
regards
Surafel
From cad411bf6bd5ffc7f5bb5cb8d9ce456fc55ac694 Mon Sep 17 00:00:00 2001
From: Surafel Temesgen <surafel3...@gmail.com>
Date: Thu, 28 Nov 2019 16:18:45 +0300
Subject: [PATCH] FETCH FIRST clause WITH TIES option
---
doc/src/sgml/ref/select.sgml | 15 ++-
src/backend/catalog/sql_features.txt | 2 +-
src/backend/executor/nodeLimit.c | 156 +++++++++++++++++++++---
src/backend/nodes/copyfuncs.c | 7 ++
src/backend/nodes/equalfuncs.c | 2 +
src/backend/nodes/outfuncs.c | 7 ++
src/backend/nodes/readfuncs.c | 6 +
src/backend/optimizer/plan/createplan.c | 44 ++++++-
src/backend/optimizer/plan/planner.c | 1 +
src/backend/optimizer/util/pathnode.c | 2 +
src/backend/parser/analyze.c | 3 +
src/backend/parser/gram.y | 117 ++++++++++++++----
src/backend/utils/adt/ruleutils.c | 10 +-
src/include/nodes/execnodes.h | 4 +
src/include/nodes/nodes.h | 13 ++
src/include/nodes/parsenodes.h | 2 +
src/include/nodes/pathnodes.h | 1 +
src/include/nodes/plannodes.h | 5 +
src/include/optimizer/pathnode.h | 1 +
src/include/optimizer/planmain.h | 3 +-
src/test/regress/expected/limit.out | 104 ++++++++++++++++
src/test/regress/sql/limit.sql | 31 +++++
22 files changed, 482 insertions(+), 54 deletions(-)
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 691e402803..2b11c38087 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -44,7 +44,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
[ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
- [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY ]
+ [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } { ONLY | WITH TIES } ]
[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
<phrase>where <replaceable class="parameter">from_item</replaceable> can be one of:</phrase>
@@ -1438,7 +1438,7 @@ OFFSET <replaceable class="parameter">start</replaceable>
which <productname>PostgreSQL</productname> also supports. It is:
<synopsis>
OFFSET <replaceable class="parameter">start</replaceable> { ROW | ROWS }
-FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY
+FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } { ONLY | WITH TIES }
</synopsis>
In this syntax, the <replaceable class="parameter">start</replaceable>
or <replaceable class="parameter">count</replaceable> value is required by
@@ -1448,10 +1448,13 @@ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] {
ambiguity.
If <replaceable class="parameter">count</replaceable> is
omitted in a <literal>FETCH</literal> clause, it defaults to 1.
- <literal>ROW</literal>
- and <literal>ROWS</literal> as well as <literal>FIRST</literal>
- and <literal>NEXT</literal> are noise words that don't influence
- the effects of these clauses.
+ The <literal>WITH TIES</literal> option is used to return any additional
+ rows that tie for the last place in the result set according to
+ <literal>ORDER BY</literal> clause; <literal>ORDER BY</literal>
+ is mandatory in this case.
+ <literal>ROW</literal> and <literal>ROWS</literal> as well as
+ <literal>FIRST</literal> and <literal>NEXT</literal> are noise
+ words that don't influence the effects of these clauses.
According to the standard, the <literal>OFFSET</literal> clause must come
before the <literal>FETCH</literal> clause if both are present; but
<productname>PostgreSQL</productname> is laxer and allows either order.
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index ab3e381cff..cd720eb19a 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -345,7 +345,7 @@ F863 Nested <result offset clause> in <query expression> YES
F864 Top-level <result offset clause> in views YES
F865 <offset row count> in <result offset clause> YES
F866 FETCH FIRST clause: PERCENT option NO
-F867 FETCH FIRST clause: WITH TIES option NO
+F867 FETCH FIRST clause: WITH TIES option YES
R010 Row pattern recognition: FROM clause NO
R020 Row pattern recognition: WINDOW clause NO
R030 Row pattern recognition: full aggregate support NO
diff --git a/src/backend/executor/nodeLimit.c b/src/backend/executor/nodeLimit.c
index 5e4d02ce4a..f4303d98fe 100644
--- a/src/backend/executor/nodeLimit.c
+++ b/src/backend/executor/nodeLimit.c
@@ -41,6 +41,7 @@ static TupleTableSlot * /* return: a tuple or NULL */
ExecLimit(PlanState *pstate)
{
LimitState *node = castNode(LimitState, pstate);
+ ExprContext *econtext = node->ps.ps_ExprContext;
ScanDirection direction;
TupleTableSlot *slot;
PlanState *outerPlan;
@@ -102,6 +103,15 @@ ExecLimit(PlanState *pstate)
node->lstate = LIMIT_EMPTY;
return NULL;
}
+ /*
+ * Tuple at limit is needed for comparation in subsequent execution
+ * to detect ties.
+ */
+ if (node->limitOption == LIMIT_OPTION_WITH_TIES &&
+ node->position - node->offset == node->count - 1)
+ {
+ ExecCopySlot(node->last_slot, slot);
+ }
node->subSlot = slot;
if (++node->position > node->offset)
break;
@@ -125,10 +135,12 @@ ExecLimit(PlanState *pstate)
if (ScanDirectionIsForward(direction))
{
/*
- * Forwards scan, so check for stepping off end of window. If
- * we are at the end of the window, return NULL without
- * advancing the subplan or the position variable; but change
- * the state machine state to record having done so.
+ * Forwards scan, so check for stepping off end of window. At
+ * the end of the window, the behavior depends on whether WITH
+ * TIES was specified: in that case, we need to change the state
+ * machine to LIMIT_WINDOWTIES.If not (nothing was specified, or ONLY was)
+ * return NULL without advancing the subplan or the position variable
+ * but change the state machine to record having done so
*
* Once at the end, ideally, we can shut down parallel
* resources but that would destroy the parallel context which
@@ -137,14 +149,74 @@ ExecLimit(PlanState *pstate)
* are possible.
*/
if (!node->noCount &&
- node->position - node->offset >= node->count)
+ node->position - node->offset >= node->count &&
+ node->limitOption == LIMIT_OPTION_COUNT)
{
node->lstate = LIMIT_WINDOWEND;
return NULL;
}
+ else if (!node->noCount &&
+ node->position - node->offset >= node->count &&
+ node->limitOption == LIMIT_OPTION_WITH_TIES)
+ {
+ node->lstate = LIMIT_WINDOWEND_TIES;
+ /* fall-through */
+ }
+ else
+ {
+ /*
+ * Get next tuple from subplan, if any.
+ */
+ slot = ExecProcNode(outerPlan);
+ if (TupIsNull(slot))
+ {
+ node->lstate = LIMIT_SUBPLANEOF;
+ return NULL;
+ }
+
+ /*
+ * Tuple at limit is needed for comparation in subsequent execution
+ * to detect ties.
+ */
+ if (node->limitOption == LIMIT_OPTION_WITH_TIES &&
+ node->position - node->offset == node->count - 1)
+ {
+ ExecCopySlot(node->last_slot, slot);
+ }
+ node->subSlot = slot;
+ node->position++;
+ break;
+ }
+ }
+ else
+ {
+ /*
+ * Backwards scan, so check for stepping off start of window.
+ * As above, change only state-machine status if so.
+ */
+ if (node->position <= node->offset + 1)
+ {
+ node->lstate = LIMIT_WINDOWSTART;
+ return NULL;
+ }
/*
- * Get next tuple from subplan, if any.
+ * Get previous tuple from subplan; there should be one!
+ */
+ slot = ExecProcNode(outerPlan);
+ if (TupIsNull(slot))
+ elog(ERROR, "LIMIT subplan failed to run backwards");
+ node->subSlot = slot;
+ node->position--;
+ break;
+ }
+
+ case LIMIT_WINDOWEND_TIES:
+ if (ScanDirectionIsForward(direction))
+ {
+ /*
+ * Advance the subplan until we find the first row
+ * with different ORDER BY pathkeys.
*/
slot = ExecProcNode(outerPlan);
if (TupIsNull(slot))
@@ -152,14 +224,29 @@ ExecLimit(PlanState *pstate)
node->lstate = LIMIT_SUBPLANEOF;
return NULL;
}
- node->subSlot = slot;
- node->position++;
+
+ /*
+ * Test if the new tuple and the last tuple match.
+ * If so we return the tuple.
+ */
+ econtext->ecxt_innertuple = slot;
+ econtext->ecxt_outertuple = node->last_slot;
+ if (ExecQualAndReset(node->eqfunction, econtext))
+ {
+ node->subSlot = slot;
+ node->position++;
+ }
+ else
+ {
+ node->lstate = LIMIT_WINDOWEND;
+ return NULL;
+ }
}
else
{
/*
* Backwards scan, so check for stepping off start of window.
- * As above, change only state-machine status if so.
+ * Change only state-machine status if so.
*/
if (node->position <= node->offset + 1)
{
@@ -168,7 +255,8 @@ ExecLimit(PlanState *pstate)
}
/*
- * Get previous tuple from subplan; there should be one!
+ * Get previous tuple from subplan; there should be one !
+ * And change state-machine status.
*/
slot = ExecProcNode(outerPlan);
if (TupIsNull(slot))
@@ -199,12 +287,27 @@ ExecLimit(PlanState *pstate)
return NULL;
/*
- * Backing up from window end: simply re-return the last tuple
- * fetched from the subplan.
+ * We already past one position to detect ties so re-fetch previous tuple; there
+ * should be one! Note previous tuple must be in window.
*/
- slot = node->subSlot;
- node->lstate = LIMIT_INWINDOW;
- /* position does not change 'cause we didn't advance it before */
+ if (node->limitOption == LIMIT_OPTION_WITH_TIES)
+ {
+ slot = ExecProcNode(outerPlan);
+ if (TupIsNull(slot))
+ elog(ERROR, "LIMIT subplan failed to run backwards");
+ node->subSlot = slot;
+ node->lstate = LIMIT_INWINDOW;
+ }
+ else
+ {
+ /*
+ * Backing up from window end: simply re-return the last tuple
+ * fetched from the subplan.
+ */
+ slot = node->subSlot;
+ node->lstate = LIMIT_INWINDOW;
+ /* position does not change 'cause we didn't advance it before */
+ }
break;
case LIMIT_WINDOWSTART:
@@ -319,7 +422,7 @@ recompute_limits(LimitState *node)
static int64
compute_tuples_needed(LimitState *node)
{
- if (node->noCount)
+ if ((node->noCount) || (node->limitOption == LIMIT_OPTION_WITH_TIES))
return -1;
/* Note: if this overflows, we'll return a negative value, which is OK */
return node->count + node->offset;
@@ -372,6 +475,7 @@ ExecInitLimit(Limit *node, EState *estate, int eflags)
(PlanState *) limitstate);
limitstate->limitCount = ExecInitExpr((Expr *) node->limitCount,
(PlanState *) limitstate);
+ limitstate->limitOption = node->limitOption;
/*
* Initialize result type.
@@ -388,6 +492,26 @@ ExecInitLimit(Limit *node, EState *estate, int eflags)
*/
limitstate->ps.ps_ProjInfo = NULL;
+ /*
+ * Initialize the equality evaluation, to detect ties.
+ */
+ if (node->limitOption == LIMIT_OPTION_WITH_TIES)
+ {
+ TupleDesc desc;
+ const TupleTableSlotOps *ops;
+
+ desc = ExecGetResultType(outerPlanState(limitstate));
+ ops = ExecGetResultSlotOps(outerPlanState(limitstate), NULL);
+
+ limitstate->last_slot = ExecInitExtraTupleSlot(estate, desc, ops);
+ limitstate->eqfunction = execTuplesMatchPrepare(desc,
+ node->uniqNumCols,
+ node->uniqColIdx,
+ node->uniqOperators,
+ node->uniqCollations,
+ &limitstate->ps);
+ }
+
return limitstate;
}
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 2f267e4bb6..ee0e362ebb 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -1147,6 +1147,11 @@ _copyLimit(const Limit *from)
*/
COPY_NODE_FIELD(limitOffset);
COPY_NODE_FIELD(limitCount);
+ COPY_SCALAR_FIELD(limitOption);
+ COPY_SCALAR_FIELD(uniqNumCols);
+ COPY_POINTER_FIELD(uniqColIdx, from->uniqNumCols * sizeof(AttrNumber));
+ COPY_POINTER_FIELD(uniqOperators, from->uniqNumCols * sizeof(Oid));
+ COPY_POINTER_FIELD(uniqCollations, from->uniqNumCols * sizeof(Oid));
return newnode;
}
@@ -3035,6 +3040,7 @@ _copyQuery(const Query *from)
COPY_NODE_FIELD(sortClause);
COPY_NODE_FIELD(limitOffset);
COPY_NODE_FIELD(limitCount);
+ COPY_SCALAR_FIELD(limitOption);
COPY_NODE_FIELD(rowMarks);
COPY_NODE_FIELD(setOperations);
COPY_NODE_FIELD(constraintDeps);
@@ -3119,6 +3125,7 @@ _copySelectStmt(const SelectStmt *from)
COPY_NODE_FIELD(sortClause);
COPY_NODE_FIELD(limitOffset);
COPY_NODE_FIELD(limitCount);
+ COPY_SCALAR_FIELD(limitOption);
COPY_NODE_FIELD(lockingClause);
COPY_NODE_FIELD(withClause);
COPY_SCALAR_FIELD(op);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index da0e1d139a..4c25f61472 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -975,6 +975,7 @@ _equalQuery(const Query *a, const Query *b)
COMPARE_NODE_FIELD(sortClause);
COMPARE_NODE_FIELD(limitOffset);
COMPARE_NODE_FIELD(limitCount);
+ COMPARE_SCALAR_FIELD(limitOption);
COMPARE_NODE_FIELD(rowMarks);
COMPARE_NODE_FIELD(setOperations);
COMPARE_NODE_FIELD(constraintDeps);
@@ -1049,6 +1050,7 @@ _equalSelectStmt(const SelectStmt *a, const SelectStmt *b)
COMPARE_NODE_FIELD(sortClause);
COMPARE_NODE_FIELD(limitOffset);
COMPARE_NODE_FIELD(limitCount);
+ COMPARE_SCALAR_FIELD(limitOption);
COMPARE_NODE_FIELD(lockingClause);
COMPARE_NODE_FIELD(withClause);
COMPARE_SCALAR_FIELD(op);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index b0dcd02ff6..2448f16428 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -911,6 +911,11 @@ _outLimit(StringInfo str, const Limit *node)
WRITE_NODE_FIELD(limitOffset);
WRITE_NODE_FIELD(limitCount);
+ WRITE_ENUM_FIELD(limitOption, LimitOption);
+ WRITE_INT_FIELD(uniqNumCols);
+ WRITE_ATTRNUMBER_ARRAY(uniqColIdx, node->uniqNumCols);
+ WRITE_OID_ARRAY(uniqOperators, node->uniqNumCols);
+ WRITE_OID_ARRAY(uniqCollations, node->uniqNumCols);
}
static void
@@ -2714,6 +2719,7 @@ _outSelectStmt(StringInfo str, const SelectStmt *node)
WRITE_NODE_FIELD(sortClause);
WRITE_NODE_FIELD(limitOffset);
WRITE_NODE_FIELD(limitCount);
+ WRITE_ENUM_FIELD(limitOption, LimitOption);
WRITE_NODE_FIELD(lockingClause);
WRITE_NODE_FIELD(withClause);
WRITE_ENUM_FIELD(op, SetOperation);
@@ -2924,6 +2930,7 @@ _outQuery(StringInfo str, const Query *node)
WRITE_NODE_FIELD(sortClause);
WRITE_NODE_FIELD(limitOffset);
WRITE_NODE_FIELD(limitCount);
+ WRITE_ENUM_FIELD(limitOption, LimitOption);
WRITE_NODE_FIELD(rowMarks);
WRITE_NODE_FIELD(setOperations);
WRITE_NODE_FIELD(constraintDeps);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 764e3bb90c..117470f28f 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -278,6 +278,7 @@ _readQuery(void)
READ_NODE_FIELD(sortClause);
READ_NODE_FIELD(limitOffset);
READ_NODE_FIELD(limitCount);
+ READ_ENUM_FIELD(limitOption, LimitOption);
READ_NODE_FIELD(rowMarks);
READ_NODE_FIELD(setOperations);
READ_NODE_FIELD(constraintDeps);
@@ -2337,6 +2338,11 @@ _readLimit(void)
READ_NODE_FIELD(limitOffset);
READ_NODE_FIELD(limitCount);
+ READ_ENUM_FIELD(limitOption, LimitOption);
+ READ_INT_FIELD(uniqNumCols);
+ READ_ATTRNUMBER_ARRAY(uniqColIdx, local_node->uniqNumCols);
+ READ_OID_ARRAY(uniqOperators, local_node->uniqNumCols);
+ READ_OID_ARRAY(uniqCollations, local_node->uniqNumCols);
READ_DONE();
}
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index aee81bd755..101e13c2f8 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -2333,7 +2333,9 @@ create_minmaxagg_plan(PlannerInfo *root, MinMaxAggPath *best_path)
plan = (Plan *) make_limit(plan,
subparse->limitOffset,
- subparse->limitCount);
+ subparse->limitCount,
+ subparse->limitOption,
+ 0, NULL, NULL, NULL);
/* Must apply correct cost/width data to Limit node */
plan->startup_cost = mminfo->path->startup_cost;
@@ -2643,13 +2645,43 @@ create_limit_plan(PlannerInfo *root, LimitPath *best_path, int flags)
{
Limit *plan;
Plan *subplan;
+ int numUniqkeys = 0;
+ AttrNumber *uniqColIdx = NULL;
+ Oid *uniqOperators = NULL;
+ Oid *uniqCollations = NULL;
/* Limit doesn't project, so tlist requirements pass through */
subplan = create_plan_recurse(root, best_path->subpath, flags);
+ /* Extract information necessary for comparing rows for WITH TIES. */
+ if (best_path->limitOption == LIMIT_OPTION_WITH_TIES)
+ {
+ Query *parse = root->parse;
+ ListCell *l;
+
+ numUniqkeys = list_length(parse->sortClause);
+ uniqColIdx = (AttrNumber *) palloc(numUniqkeys * sizeof(AttrNumber));
+ uniqOperators = (Oid *) palloc(numUniqkeys * sizeof(Oid));
+ uniqCollations = (Oid *) palloc(numUniqkeys * sizeof(Oid));
+
+ numUniqkeys = 0;
+ foreach(l, parse->sortClause)
+ {
+ SortGroupClause *sortcl = (SortGroupClause *) lfirst(l);
+ TargetEntry *tle = get_sortgroupclause_tle(sortcl, parse->targetList);
+
+ uniqColIdx[numUniqkeys] = tle->resno;
+ uniqOperators[numUniqkeys] = sortcl->eqop;
+ uniqCollations[numUniqkeys] = exprCollation((Node *) tle->expr);
+ numUniqkeys++;
+ }
+ }
+
plan = make_limit(subplan,
best_path->limitOffset,
- best_path->limitCount);
+ best_path->limitCount,
+ best_path->limitOption,
+ numUniqkeys, uniqColIdx, uniqOperators, uniqCollations);
copy_generic_path_info(&plan->plan, (Path *) best_path);
@@ -6552,7 +6584,8 @@ make_lockrows(Plan *lefttree, List *rowMarks, int epqParam)
* Build a Limit plan node
*/
Limit *
-make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount)
+make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount, LimitOption limitOption,
+ int uniqNumCols, AttrNumber *uniqColIdx, Oid *uniqOperators, Oid *uniqCollations)
{
Limit *node = makeNode(Limit);
Plan *plan = &node->plan;
@@ -6564,6 +6597,11 @@ make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount)
node->limitOffset = limitOffset;
node->limitCount = limitCount;
+ node->limitOption = limitOption;
+ node->uniqNumCols = uniqNumCols;
+ node->uniqColIdx = uniqColIdx;
+ node->uniqOperators = uniqOperators;
+ node->uniqCollations = uniqCollations;
return node;
}
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 7fe11b59a0..a45e05b5a7 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -2314,6 +2314,7 @@ grouping_planner(PlannerInfo *root, bool inheritance_update,
path = (Path *) create_limit_path(root, final_rel, path,
parse->limitOffset,
parse->limitCount,
+ parse->limitOption,
offset_est, count_est);
}
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 60c93ee7c5..1605481ca5 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -3545,6 +3545,7 @@ LimitPath *
create_limit_path(PlannerInfo *root, RelOptInfo *rel,
Path *subpath,
Node *limitOffset, Node *limitCount,
+ LimitOption limitOption,
int64 offset_est, int64 count_est)
{
LimitPath *pathnode = makeNode(LimitPath);
@@ -3566,6 +3567,7 @@ create_limit_path(PlannerInfo *root, RelOptInfo *rel,
pathnode->subpath = subpath;
pathnode->limitOffset = limitOffset;
pathnode->limitCount = limitCount;
+ pathnode->limitOption = limitOption;
/*
* Adjust the output rows count and costs according to the offset/limit.
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 85d7a96406..f0b863f41a 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1292,6 +1292,7 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
EXPR_KIND_OFFSET, "OFFSET");
qry->limitCount = transformLimitClause(pstate, stmt->limitCount,
EXPR_KIND_LIMIT, "LIMIT");
+ qry->limitOption = stmt->limitOption;
/* transform window clauses after we have seen all window functions */
qry->windowClause = transformWindowDefinitions(pstate,
@@ -1540,6 +1541,7 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt)
EXPR_KIND_OFFSET, "OFFSET");
qry->limitCount = transformLimitClause(pstate, stmt->limitCount,
EXPR_KIND_LIMIT, "LIMIT");
+ qry->limitOption = stmt->limitOption;
if (stmt->lockingClause)
ereport(ERROR,
@@ -1774,6 +1776,7 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
EXPR_KIND_OFFSET, "OFFSET");
qry->limitCount = transformLimitClause(pstate, limitCount,
EXPR_KIND_LIMIT, "LIMIT");
+ qry->limitOption = stmt->limitOption;
qry->rtable = pstate->p_rtable;
qry->jointree = makeFromExpr(pstate->p_joinlist, NULL);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c5086846de..1dc45eec62 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -127,6 +127,14 @@ typedef struct ImportQual
List *table_names;
} ImportQual;
+/* Private struct for the result of opt_select_limit production */
+typedef struct SelectLimit
+{
+ Node *limitOffset;
+ Node *limitCount;
+ LimitOption limitOption;
+} SelectLimit;
+
/* ConstraintAttributeSpec yields an integer bitmask of these flags: */
#define CAS_NOT_DEFERRABLE 0x01
#define CAS_DEFERRABLE 0x02
@@ -164,7 +172,7 @@ static List *makeOrderedSetArgs(List *directargs, List *orderedargs,
core_yyscan_t yyscanner);
static void insertSelectOptions(SelectStmt *stmt,
List *sortClause, List *lockingClause,
- Node *limitOffset, Node *limitCount,
+ SelectLimit *limitClause,
WithClause *withClause,
core_yyscan_t yyscanner);
static Node *makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg);
@@ -242,6 +250,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
PartitionSpec *partspec;
PartitionBoundSpec *partboundspec;
RoleSpec *rolespec;
+ struct SelectLimit *selectlimit;
}
%type <node> stmt schema_stmt
@@ -374,6 +383,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <ival> import_qualification_type
%type <importqual> import_qualification
%type <node> vacuum_relation
+%type <selectlimit> opt_select_limit select_limit limit_clause
%type <list> stmtblock stmtmulti
OptTableElementList TableElementList OptInherit definition
@@ -394,8 +404,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
target_list opt_target_list insert_column_list set_target_list
set_clause_list set_clause
def_list operator_def_list indirection opt_indirection
- reloption_list group_clause TriggerFuncArgs select_limit
- opt_select_limit opclass_item_list opclass_drop_list
+ reloption_list group_clause TriggerFuncArgs opclass_item_list opclass_drop_list
opclass_purpose opt_opfamily transaction_mode_list_or_empty
OptTableFuncElementList TableFuncElementList opt_type_modifiers
prep_type_clause
@@ -457,7 +466,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
comment_type_any_name comment_type_name
security_label_type_any_name security_label_type_name
-%type <node> fetch_args limit_clause select_limit_value
+%type <node> fetch_args select_limit_value
offset_clause select_offset_value
select_fetch_first_value I_or_F_const
%type <ival> row_or_rows first_or_next
@@ -11317,14 +11326,14 @@ select_no_parens:
| select_clause sort_clause
{
insertSelectOptions((SelectStmt *) $1, $2, NIL,
- NULL, NULL, NULL,
+ NULL, NULL,
yyscanner);
$$ = $1;
}
| select_clause opt_sort_clause for_locking_clause opt_select_limit
{
insertSelectOptions((SelectStmt *) $1, $2, $3,
- list_nth($4, 0), list_nth($4, 1),
+ $4,
NULL,
yyscanner);
$$ = $1;
@@ -11332,7 +11341,7 @@ select_no_parens:
| select_clause opt_sort_clause select_limit opt_for_locking_clause
{
insertSelectOptions((SelectStmt *) $1, $2, $4,
- list_nth($3, 0), list_nth($3, 1),
+ $3,
NULL,
yyscanner);
$$ = $1;
@@ -11340,7 +11349,7 @@ select_no_parens:
| with_clause select_clause
{
insertSelectOptions((SelectStmt *) $2, NULL, NIL,
- NULL, NULL,
+ NULL,
$1,
yyscanner);
$$ = $2;
@@ -11348,7 +11357,7 @@ select_no_parens:
| with_clause select_clause sort_clause
{
insertSelectOptions((SelectStmt *) $2, $3, NIL,
- NULL, NULL,
+ NULL,
$1,
yyscanner);
$$ = $2;
@@ -11356,7 +11365,7 @@ select_no_parens:
| with_clause select_clause opt_sort_clause for_locking_clause opt_select_limit
{
insertSelectOptions((SelectStmt *) $2, $3, $4,
- list_nth($5, 0), list_nth($5, 1),
+ $5,
$1,
yyscanner);
$$ = $2;
@@ -11364,7 +11373,7 @@ select_no_parens:
| with_clause select_clause opt_sort_clause select_limit opt_for_locking_clause
{
insertSelectOptions((SelectStmt *) $2, $3, $5,
- list_nth($4, 0), list_nth($4, 1),
+ $4,
$1,
yyscanner);
$$ = $2;
@@ -11658,20 +11667,44 @@ sortby: a_expr USING qual_all_Op opt_nulls_order
select_limit:
- limit_clause offset_clause { $$ = list_make2($2, $1); }
- | offset_clause limit_clause { $$ = list_make2($1, $2); }
- | limit_clause { $$ = list_make2(NULL, $1); }
- | offset_clause { $$ = list_make2($1, NULL); }
+ limit_clause offset_clause
+ {
+ $$ = $1;
+ ($$)->limitOffset = $2;
+ }
+ | offset_clause limit_clause
+ {
+ $$ = $2;
+ ($$)->limitOffset = $1;
+ }
+ | limit_clause
+ {
+ $$ = $1;
+ }
+ | offset_clause
+ {
+ SelectLimit *n = (SelectLimit *) palloc(sizeof(SelectLimit));
+ n->limitOffset = $1;
+ n->limitCount = NULL;
+ n->limitOption = LIMIT_OPTION_COUNT;
+ $$ = n;
+ }
;
opt_select_limit:
select_limit { $$ = $1; }
- | /* EMPTY */ { $$ = list_make2(NULL,NULL); }
+ | /* EMPTY */ { $$ = NULL; }
;
limit_clause:
LIMIT select_limit_value
- { $$ = $2; }
+ {
+ SelectLimit *n = (SelectLimit *) palloc(sizeof(SelectLimit));
+ n->limitOffset = NULL;
+ n->limitCount = $2;
+ n->limitOption = LIMIT_OPTION_COUNT;
+ $$ = n;
+ }
| LIMIT select_limit_value ',' select_offset_value
{
/* Disabled because it was too confusing, bjm 2002-02-18 */
@@ -11689,9 +11722,29 @@ limit_clause:
* we can see the ONLY token in the lookahead slot.
*/
| FETCH first_or_next select_fetch_first_value row_or_rows ONLY
- { $$ = $3; }
+ {
+ SelectLimit *n = (SelectLimit *) palloc(sizeof(SelectLimit));
+ n->limitOffset = NULL;
+ n->limitCount = $3;
+ n->limitOption = LIMIT_OPTION_COUNT;
+ $$ = n;
+ }
+ | FETCH first_or_next select_fetch_first_value row_or_rows WITH TIES
+ {
+ SelectLimit *n = (SelectLimit *) palloc(sizeof(SelectLimit));
+ n->limitOffset = NULL;
+ n->limitCount = $3;
+ n->limitOption = LIMIT_OPTION_WITH_TIES;
+ $$ = n;
+ }
| FETCH first_or_next row_or_rows ONLY
- { $$ = makeIntConst(1, -1); }
+ {
+ SelectLimit *n = (SelectLimit *) palloc(sizeof(SelectLimit));
+ n->limitOffset = NULL;
+ n->limitCount = makeIntConst(1, -1);
+ n->limitOption = LIMIT_OPTION_COUNT;
+ $$ = n;
+ }
;
offset_clause:
@@ -15947,7 +16000,7 @@ makeOrderedSetArgs(List *directargs, List *orderedargs,
static void
insertSelectOptions(SelectStmt *stmt,
List *sortClause, List *lockingClause,
- Node *limitOffset, Node *limitCount,
+ SelectLimit *limitClause,
WithClause *withClause,
core_yyscan_t yyscanner)
{
@@ -15968,23 +16021,35 @@ insertSelectOptions(SelectStmt *stmt,
}
/* We can handle multiple locking clauses, though */
stmt->lockingClause = list_concat(stmt->lockingClause, lockingClause);
- if (limitOffset)
+ if (limitClause && limitClause->limitOffset)
{
if (stmt->limitOffset)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("multiple OFFSET clauses not allowed"),
- parser_errposition(exprLocation(limitOffset))));
- stmt->limitOffset = limitOffset;
+ parser_errposition(exprLocation(limitClause->limitOffset))));
+ stmt->limitOffset = limitClause->limitOffset;
}
- if (limitCount)
+ if (limitClause && limitClause->limitCount)
{
if (stmt->limitCount)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("multiple LIMIT clauses not allowed"),
- parser_errposition(exprLocation(limitCount))));
- stmt->limitCount = limitCount;
+ parser_errposition(exprLocation(limitClause->limitCount))));
+ stmt->limitCount = limitClause->limitCount;
+ }
+ if (limitClause && limitClause->limitOption != LIMIT_OPTION_DEFAULT)
+ {
+ if (stmt->limitOption)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("multiple limit options not allowed")));
+ if (!stmt->sortClause && limitClause->limitOption == LIMIT_OPTION_WITH_TIES)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("WITH TIES options can not be specified without ORDER BY clause")));
+ stmt->limitOption = limitClause->limitOption;
}
if (withClause)
{
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 13685a0a0e..82ef187b06 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -5308,7 +5308,15 @@ get_select_query_def(Query *query, deparse_context *context,
-PRETTYINDENT_STD, PRETTYINDENT_STD, 0);
get_rule_expr(query->limitOffset, context, false);
}
- if (query->limitCount != NULL)
+ if (query->limitOption == LIMIT_OPTION_WITH_TIES)
+ {
+ appendContextKeyword(context, " FETCH FIRST ",
+ -PRETTYINDENT_STD, PRETTYINDENT_STD, 0);
+ get_rule_expr(query->limitCount, context, false);
+ appendContextKeyword(context, " ROWS WITH TIES ",
+ -PRETTYINDENT_STD, PRETTYINDENT_STD, 0);
+ }
+ if (query->limitCount != NULL && query->limitOption != LIMIT_OPTION_WITH_TIES)
{
appendContextKeyword(context, " LIMIT ",
-PRETTYINDENT_STD, PRETTYINDENT_STD, 0);
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 6eb647290b..6f2530e613 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -2339,6 +2339,7 @@ typedef enum
LIMIT_RESCAN, /* rescan after recomputing parameters */
LIMIT_EMPTY, /* there are no returnable rows */
LIMIT_INWINDOW, /* have returned a row in the window */
+ LIMIT_WINDOWEND_TIES, /* have returned a tied row*/
LIMIT_SUBPLANEOF, /* at EOF of subplan (within window) */
LIMIT_WINDOWEND, /* stepped off end of window */
LIMIT_WINDOWSTART /* stepped off beginning of window */
@@ -2349,12 +2350,15 @@ typedef struct LimitState
PlanState ps; /* its first field is NodeTag */
ExprState *limitOffset; /* OFFSET parameter, or NULL if none */
ExprState *limitCount; /* COUNT parameter, or NULL if none */
+ LimitOption limitOption; /* limit specification type */
int64 offset; /* current OFFSET value */
int64 count; /* current COUNT, if any */
bool noCount; /* if true, ignore count */
LimitStateCond lstate; /* state machine status, as above */
int64 position; /* 1-based index of last tuple returned */
TupleTableSlot *subSlot; /* tuple last obtained from subplan */
+ ExprState *eqfunction; /* tuple equality qual in case of WITH TIES option */
+ TupleTableSlot *last_slot; /* slot for evaluation of ties */
} LimitState;
#endif /* EXECNODES_H */
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index bce2d59b0d..6bdddf4b3e 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -822,4 +822,17 @@ typedef enum OnConflictAction
ONCONFLICT_UPDATE /* ON CONFLICT ... DO UPDATE */
} OnConflictAction;
+/*
+ * LimitOption -
+ * LIMIT option of query
+ *
+ * This is needed in both parsenodes.h and plannodes.h, so put it here...
+ */
+typedef enum LimitOption
+{
+ LIMIT_OPTION_COUNT, /* FETCH FIRST... ONLY */
+ LIMIT_OPTION_WITH_TIES, /* FETCH FIRST... WITH TIES */
+ LIMIT_OPTION_DEFAULT, /* No limit present */
+} LimitOption;
+
#endif /* NODES_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ff626cbe61..7f318d7330 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -159,6 +159,7 @@ typedef struct Query
Node *limitOffset; /* # of result tuples to skip (int8 expr) */
Node *limitCount; /* # of result tuples to return (int8 expr) */
+ LimitOption limitOption; /* limit type { WITH TIES | ONLY } */
List *rowMarks; /* a list of RowMarkClause's */
@@ -1595,6 +1596,7 @@ typedef struct SelectStmt
List *sortClause; /* sort clause (a list of SortBy's) */
Node *limitOffset; /* # of result tuples to skip */
Node *limitCount; /* # of result tuples to return */
+ LimitOption limitOption; /* limit type */
List *lockingClause; /* FOR UPDATE (list of LockingClause's) */
WithClause *withClause; /* WITH clause */
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 23a06d718e..34c3aa6de5 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -1793,6 +1793,7 @@ typedef struct LimitPath
Path *subpath; /* path representing input source */
Node *limitOffset; /* OFFSET parameter, or NULL if none */
Node *limitCount; /* COUNT parameter, or NULL if none */
+ LimitOption limitOption; /* FETCH FIRST with ties or exact number */
} LimitPath;
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 8e6594e355..30fb10276f 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -967,6 +967,11 @@ typedef struct Limit
Plan plan;
Node *limitOffset; /* OFFSET parameter, or NULL if none */
Node *limitCount; /* COUNT parameter, or NULL if none */
+ LimitOption limitOption; /* fetch first with ties or exact number */
+ int uniqNumCols; /* number of columns to check for similarity */
+ AttrNumber *uniqColIdx; /* their indexes in the target list */
+ Oid *uniqOperators; /* equality operators to compare with */
+ Oid *uniqCollations; /* collations for equality comparisons */
} Limit;
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index a12af54971..ac6f422fa8 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -262,6 +262,7 @@ extern ModifyTablePath *create_modifytable_path(PlannerInfo *root,
extern LimitPath *create_limit_path(PlannerInfo *root, RelOptInfo *rel,
Path *subpath,
Node *limitOffset, Node *limitCount,
+ LimitOption limitOption,
int64 offset_est, int64 count_est);
extern void adjust_limit_rows_costs(double *rows,
Cost *startup_cost, Cost *total_cost,
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index e7aaddd50d..6d4ebdba8d 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -56,7 +56,8 @@ extern Agg *make_agg(List *tlist, List *qual,
int numGroupCols, AttrNumber *grpColIdx, Oid *grpOperators, Oid *grpCollations,
List *groupingSets, List *chain,
double dNumGroups, Plan *lefttree);
-extern Limit *make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount);
+extern Limit *make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount,
+ LimitOption limitOption,int uniqNumCols, AttrNumber *uniqColIdx, Oid *uniqOperators, Oid *uniqCollations);
/*
* prototypes for plan/initsplan.c
diff --git a/src/test/regress/expected/limit.out b/src/test/regress/expected/limit.out
index c18f547cbd..e2699f05da 100644
--- a/src/test/regress/expected/limit.out
+++ b/src/test/regress/expected/limit.out
@@ -286,6 +286,58 @@ fetch all in c4;
----+----
(0 rows)
+declare c5 cursor for select * from int8_tbl order by q1 fetch first 2 rows with ties;
+fetch all in c5;
+ q1 | q2
+-----+------------------
+ 123 | 456
+ 123 | 4567890123456789
+(2 rows)
+
+fetch 1 in c5;
+ q1 | q2
+----+----
+(0 rows)
+
+fetch backward 1 in c5;
+ q1 | q2
+-----+------------------
+ 123 | 4567890123456789
+(1 row)
+
+fetch backward 1 in c5;
+ q1 | q2
+-----+-----
+ 123 | 456
+(1 row)
+
+fetch all in c5;
+ q1 | q2
+-----+------------------
+ 123 | 4567890123456789
+(1 row)
+
+fetch backward all in c5;
+ q1 | q2
+-----+------------------
+ 123 | 4567890123456789
+ 123 | 456
+(2 rows)
+
+fetch all in c5;
+ q1 | q2
+-----+------------------
+ 123 | 456
+ 123 | 4567890123456789
+(2 rows)
+
+fetch backward all in c5;
+ q1 | q2
+-----+------------------
+ 123 | 4567890123456789
+ 123 | 456
+(2 rows)
+
rollback;
-- Stress test for variable LIMIT in conjunction with bounded-heap sorting
SELECT
@@ -503,3 +555,55 @@ select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2
45020 | 45020
(3 rows)
+--
+-- FETCH FIRST
+-- Check the WITH TIES clause
+--
+SELECT thousand
+ FROM onek WHERE thousand < 5
+ ORDER BY thousand FETCH FIRST 2 ROW 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;
+ 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 2 ROW ONLY;
+ thousand
+----------
+ 0
+ 0
+(2 rows)
+
+-- should fail
+SELECT ''::text AS two, unique1, unique2, stringu1
+ FROM onek WHERE unique1 > 50
+ FETCH FIRST 2 ROW WITH TIES;
+ERROR: WITH TIES options can not be specified without ORDER BY clause
diff --git a/src/test/regress/sql/limit.sql b/src/test/regress/sql/limit.sql
index 2a313d80ca..4d129f7794 100644
--- a/src/test/regress/sql/limit.sql
+++ b/src/test/regress/sql/limit.sql
@@ -71,6 +71,16 @@ fetch backward all in c4;
fetch backward 1 in c4;
fetch all in c4;
+declare c5 cursor for select * from int8_tbl order by q1 fetch first 2 rows with ties;
+fetch all in c5;
+fetch 1 in c5;
+fetch backward 1 in c5;
+fetch backward 1 in c5;
+fetch all in c5;
+fetch backward all in c5;
+fetch all in c5;
+fetch backward all in c5;
+
rollback;
-- Stress test for variable LIMIT in conjunction with bounded-heap sorting
@@ -141,3 +151,24 @@ select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2
select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2
from tenk1 group by thousand order by thousand limit 3;
+
+--
+-- FETCH FIRST
+-- Check the WITH TIES clause
+--
+
+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 1 ROW WITH TIES;
+
+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
+ FETCH FIRST 2 ROW WITH TIES;
--
2.17.1
