Hello Surafel,
On 10/26/2018 12:28 PM, Surafel Temesgen wrote:
> hello ,
>
> The WITH TIES keyword is sql standard that specifies any peers of
> retained rows to retained in the result set too .which means
> according to ordering key the result set can includes additional rows
> which have ties on the last position, if there are any and It work
> with ORDER BY query.
>
Thanks for the patch. I've looked at it today, and it seems mostly OK,
with a couple of minor issues. Most of it is code formatting and comment
wording issues, so I'm not going to go through them here - see the
attached 0002 patch (0001 is your patch, rebased to current master).
There's one place that I think is incorrect, and that's this bit from
ExecLimit:
}else
/*
* Get next tuple from subplan, if any.
*/
slot = ExecProcNode(outerPlan);
if (TupIsNull(slot))
{
node->lstate = LIMIT_SUBPLANEOF;
return NULL;
}
if (node->limitOption == WITH_TIES)
{
ExecCopySlot(node->last_slot, slot);
}
node->subSlot = slot;
node->position++;
Note that the "else" guards only the very first line, not the whole
block. This seems wrong, i.e. there should be {} around the whole block.
I'm also suggesting to slightly change the create_limit_plan(), to keep
a single make_limit call. IMHO that makes it easier to understand,
although I admit it's fairly subjective.
One question is whether to make this work for LIMIT too, not just for
FETCH FIRST. I'm not sure how difficult would that be (it should be a
grammar-only tweak I guess), or if it's a good idea in general. But I
find it quite confusing that various comments say things like this:
LimitOption limitOption; /* LIMIT with ties or exact number */
while in fact it does not work with LIMIT.
>
> The attach patch is a finished implementation of it except it did not
> have a regression test because am not sure of changing the test data set
> for it and I can’t find fetch first clause regression test too.
>
Well, that's not a very good reason not to have tests for this new
improvement. FWIW there are a couple of places in regression tests where
FETCH FIRST is used, see this:
$ grep -i 'fetch first' src/test/regress/sql/*
src/test/regress/sql/hs_standby_allowed.sql:fetch first from hsc;
src/test/regress/sql/tablesample.sql:FETCH FIRST FROM tablesample_cur;
src/test/regress/sql/tablesample.sql:FETCH FIRST FROM tablesample_cur;
src/test/regress/sql/tidscan.sql:FETCH FIRST FROM c;
But those places don't seem like very good match for testing the new
stuff, because those are primarily testing something else. I suggest we
add the new tests into limit.sql.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 7f5549614c423a1da060da5f7598e9d0836d03a8 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <to...@2ndquadrant.com>
Date: Sun, 28 Oct 2018 18:38:47 +0100
Subject: [PATCH 2/2] review
---
doc/src/sgml/ref/select.sgml | 6 ++--
src/backend/executor/nodeLimit.c | 52 +++++++++++++++++++--------------
src/backend/optimizer/plan/createplan.c | 25 +++++++---------
src/include/nodes/execnodes.h | 4 +--
src/include/nodes/parsenodes.h | 4 +--
src/include/nodes/plannodes.h | 2 +-
src/include/nodes/relation.h | 2 +-
7 files changed, 49 insertions(+), 46 deletions(-)
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index c81ac04108..b649b1ca7a 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -1408,9 +1408,9 @@ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] {
If <replaceable class="parameter">count</replaceable> is
omitted in a <literal>FETCH</literal> clause, it defaults to 1.
<literal>ROW</literal>
- <literal>WITH TIES</literal> option is Used to return two or more rows
- that tie for last place in the limit results set according to <literal>ORDER BY</literal>
- clause (<literal>ORDER BY</literal> clause must be specified in this case).
+ <literal>WITH TIES</literal> option is used to return two or more rows
+ that tie for last place in the limit results set according to <literal>ORDER BY</literal>
+ clause (<literal>ORDER BY</literal> clause must be specified in this case).
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.
diff --git a/src/backend/executor/nodeLimit.c b/src/backend/executor/nodeLimit.c
index 93f8813972..724a8e09c1 100644
--- a/src/backend/executor/nodeLimit.c
+++ b/src/backend/executor/nodeLimit.c
@@ -132,7 +132,8 @@ ExecLimit(PlanState *pstate)
* the state machine state to record having done so.
*/
if (!node->noCount &&
- node->position - node->offset >= node->count && node->limitOption == WITH_ONLY)
+ node->position - node->offset >= node->count &&
+ node->limitOption == WITH_ONLY)
{
node->lstate = LIMIT_WINDOWEND;
@@ -145,23 +146,24 @@ ExecLimit(PlanState *pstate)
return NULL;
}
-
else if (!node->noCount &&
- node->position - node->offset >= node->count && node->limitOption == WITH_TIES)
+ node->position - node->offset >= node->count &&
+ node->limitOption == WITH_TIES)
{
/*
- * Get next tuple from subplan, if any.
- */
+ * Get next tuple from subplan, if any.
+ */
slot = ExecProcNode(outerPlan);
if (TupIsNull(slot))
{
node->lstate = LIMIT_SUBPLANEOF;
return NULL;
}
+
/*
- * Test if the new tuple and the last tuple match.
- * If so we return the tuple.
- */
+ * 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))
@@ -184,22 +186,25 @@ ExecLimit(PlanState *pstate)
return NULL;
}
- }else
- /*
- * Get next tuple from subplan, if any.
- */
- slot = ExecProcNode(outerPlan);
- if (TupIsNull(slot))
- {
- node->lstate = LIMIT_SUBPLANEOF;
- return NULL;
}
- if (node->limitOption == WITH_TIES)
+ else /* XXX So what exactly should be part of this else branch? */
{
- ExecCopySlot(node->last_slot, slot);
+ /*
+ * Get next tuple from subplan, if any.
+ */
+ slot = ExecProcNode(outerPlan);
+ if (TupIsNull(slot))
+ {
+ node->lstate = LIMIT_SUBPLANEOF;
+ return NULL;
+ }
+ if (node->limitOption == WITH_TIES)
+ {
+ ExecCopySlot(node->last_slot, slot);
+ }
+ node->subSlot = slot;
+ node->position++;
}
- node->subSlot = slot;
- node->position++;
}
else
{
@@ -356,7 +361,7 @@ recompute_limits(LimitState *node)
* previous time we got a different result.
*/
if(node->limitOption == WITH_ONLY)
- ExecSetTupleBound(compute_tuples_needed(node), outerPlanState(node));
+ ExecSetTupleBound(compute_tuples_needed(node), outerPlanState(node));
}
/*
@@ -433,6 +438,9 @@ ExecInitLimit(Limit *node, EState *estate, int eflags)
*/
limitstate->ps.ps_ProjInfo = NULL;
+ /*
+ * Initialize the equality evaluation, to detect ties.
+ */
if (node->limitOption == WITH_TIES)
{
TupleDesc scanDesc;
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 7e540849b4..62c05c7feb 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -2435,6 +2435,9 @@ create_limit_plan(PlannerInfo *root, LimitPath *best_path, int flags)
{
Limit *plan;
Plan *subplan;
+ int numsortkeys = 0;
+ AttrNumber *sortColIdx = NULL;
+ Oid *sortOperators = NULL;
/* Limit doesn't project, so tlist requirements pass through */
subplan = create_plan_recurse(root, best_path->subpath, flags);
@@ -2442,9 +2445,6 @@ create_limit_plan(PlannerInfo *root, LimitPath *best_path, int flags)
if (best_path->limitOption == WITH_TIES)
{
Query *parse = root->parse;
- int numsortkeys;
- AttrNumber *sortColIdx;
- Oid *sortOperators;
ListCell *l;
numsortkeys = list_length(parse->sortClause);
@@ -2461,19 +2461,14 @@ create_limit_plan(PlannerInfo *root, LimitPath *best_path, int flags)
sortOperators[numsortkeys] = sortcl->eqop;
numsortkeys++;
}
- plan = make_limit(subplan,
- best_path->limitOffset,
- best_path->limitCount,
- best_path->limitOption,
- numsortkeys, sortColIdx,
- sortOperators);
}
- else
- plan = make_limit(subplan,
- best_path->limitOffset,
- best_path->limitCount,
- best_path->limitOption,
- 0, NULL, NULL);
+
+ plan = make_limit(subplan,
+ best_path->limitOffset,
+ best_path->limitCount,
+ best_path->limitOption,
+ numsortkeys, sortColIdx,
+ sortOperators);
copy_generic_path_info(&plan->plan, (Path *) best_path);
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 212820d886..6f15ce8ea4 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -2242,7 +2242,7 @@ 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 */
+ LimitOption limitOption; /* limit specification type */
int64 offset; /* current OFFSET value */
int64 count; /* current COUNT, if any */
bool noCount; /* if true, ignore count */
@@ -2250,7 +2250,7 @@ typedef struct LimitState
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;
+ TupleTableSlot *last_slot; /* slot for evaluation of ties */
} LimitState;
#endif /* EXECNODES_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ce10b55dcf..ce07a1a02d 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -159,7 +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 */
+ LimitOption limitOption; /* LIMIT type [WITH TIES | WITH ONLY] */
List *rowMarks; /* a list of RowMarkClause's */
@@ -1574,7 +1574,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 */
+ LimitOption limitOption; /* limit type */
List *lockingClause; /* FOR UPDATE (list of LockingClause's) */
WithClause *withClause; /* WITH clause */
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 24b7e51a91..5e961a8f7b 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -945,7 +945,7 @@ typedef struct Limit
Plan plan;
Node *limitOffset; /* OFFSET parameter, or NULL if none */
Node *limitCount; /* COUNT parameter, or NULL if none */
- LimitOption limitOption; /* LIMIT with ties or exact number */
+ LimitOption limitOption; /* LIMIT with ties or exact number */
int numCols; /* number of columns to check for Similarity */
AttrNumber *uniqColIdx; /* their indexes in the target list */
Oid *uniqOperators; /* equality operators to compare with */
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index 2c7f4c71ea..f107d4f773 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -1737,7 +1737,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; /* LIMIT with ties or exact number */
+ LimitOption limitOption; /* LIMIT with ties or exact number */
} LimitPath;
--
2.13.6
>From dd41346971518be2deed85a08d8028d0cd336e6e Mon Sep 17 00:00:00 2001
From: Tomas Vondra <to...@2ndquadrant.com>
Date: Sun, 28 Oct 2018 17:23:36 +0100
Subject: [PATCH 1/2] rebased patch
---
doc/src/sgml/ref/select.sgml | 7 ++--
src/backend/executor/nodeLimit.c | 61 ++++++++++++++++++++++++++++++++-
src/backend/nodes/copyfuncs.c | 6 ++++
src/backend/nodes/equalfuncs.c | 2 ++
src/backend/nodes/outfuncs.c | 24 +++++++++++++
src/backend/nodes/readfuncs.c | 5 +++
src/backend/optimizer/plan/createplan.c | 49 +++++++++++++++++++++++---
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 | 49 +++++++++++++++++++-------
src/include/nodes/execnodes.h | 3 ++
src/include/nodes/nodes.h | 12 +++++++
src/include/nodes/parsenodes.h | 2 ++
src/include/nodes/plannodes.h | 4 +++
src/include/nodes/relation.h | 1 +
src/include/optimizer/pathnode.h | 1 +
src/include/optimizer/planmain.h | 3 +-
18 files changed, 213 insertions(+), 22 deletions(-)
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 4db8142afa..c81ac04108 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>
@@ -1397,7 +1397,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
@@ -1408,6 +1408,9 @@ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] {
If <replaceable class="parameter">count</replaceable> is
omitted in a <literal>FETCH</literal> clause, it defaults to 1.
<literal>ROW</literal>
+ <literal>WITH TIES</literal> option is Used to return two or more rows
+ that tie for last place in the limit results set according to <literal>ORDER BY</literal>
+ clause (<literal>ORDER BY</literal> clause must be specified in this case).
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.
diff --git a/src/backend/executor/nodeLimit.c b/src/backend/executor/nodeLimit.c
index bb28cf7d1d..93f8813972 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;
@@ -131,7 +132,7 @@ ExecLimit(PlanState *pstate)
* the state machine state to record having done so.
*/
if (!node->noCount &&
- node->position - node->offset >= node->count)
+ node->position - node->offset >= node->count && node->limitOption == WITH_ONLY)
{
node->lstate = LIMIT_WINDOWEND;
@@ -145,6 +146,45 @@ ExecLimit(PlanState *pstate)
return NULL;
}
+ else if (!node->noCount &&
+ node->position - node->offset >= node->count && node->limitOption == WITH_TIES)
+ {
+ /*
+ * Get next tuple from subplan, if any.
+ */
+ slot = ExecProcNode(outerPlan);
+ if (TupIsNull(slot))
+ {
+ node->lstate = LIMIT_SUBPLANEOF;
+ return NULL;
+ }
+ /*
+ * 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))
+ {
+ ExecCopySlot(node->last_slot, slot);
+ node->subSlot = slot;
+ node->position++;
+ }
+ else
+ {
+ node->lstate = LIMIT_WINDOWEND;
+
+ /*
+ * If we know we won't need to back up, we can release
+ * resources at this point.
+ */
+ if (!(node->ps.state->es_top_eflags & EXEC_FLAG_BACKWARD))
+ (void) ExecShutdownNode(outerPlan);
+
+ return NULL;
+ }
+
+ }else
/*
* Get next tuple from subplan, if any.
*/
@@ -154,6 +194,10 @@ ExecLimit(PlanState *pstate)
node->lstate = LIMIT_SUBPLANEOF;
return NULL;
}
+ if (node->limitOption == WITH_TIES)
+ {
+ ExecCopySlot(node->last_slot, slot);
+ }
node->subSlot = slot;
node->position++;
}
@@ -311,6 +355,7 @@ recompute_limits(LimitState *node)
* must update the child node anyway, in case this is a rescan and the
* previous time we got a different result.
*/
+ if(node->limitOption == WITH_ONLY)
ExecSetTupleBound(compute_tuples_needed(node), outerPlanState(node));
}
@@ -374,6 +419,7 @@ ExecInitLimit(Limit *node, EState *estate, int eflags)
(PlanState *) limitstate);
limitstate->limitCount = ExecInitExpr((Expr *) node->limitCount,
(PlanState *) limitstate);
+ limitstate->limitOption = node->limitOption;
/*
* Initialize result slot and type. (XXX not actually used, but upper
@@ -387,6 +433,19 @@ ExecInitLimit(Limit *node, EState *estate, int eflags)
*/
limitstate->ps.ps_ProjInfo = NULL;
+ if (node->limitOption == WITH_TIES)
+ {
+ TupleDesc scanDesc;
+ scanDesc = limitstate->ps.ps_ResultTupleSlot->tts_tupleDescriptor;
+ limitstate->last_slot = ExecInitExtraTupleSlot(estate, scanDesc);
+ limitstate->eqfunction =
+ execTuplesMatchPrepare(ExecGetResultType(outerPlanState(limitstate)),
+ node->numCols,
+ node->uniqColIdx,
+ node->uniqOperators,
+ &limitstate->ps);
+ }
+
return limitstate;
}
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index e8ea59e34a..ceed66587f 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -1136,6 +1136,10 @@ _copyLimit(const Limit *from)
*/
COPY_NODE_FIELD(limitOffset);
COPY_NODE_FIELD(limitCount);
+ COPY_SCALAR_FIELD(limitOption);
+ COPY_SCALAR_FIELD(numCols);
+ COPY_POINTER_FIELD(uniqColIdx, from->numCols * sizeof(AttrNumber));
+ COPY_POINTER_FIELD(uniqOperators, from->numCols * sizeof(Oid));
return newnode;
}
@@ -3022,6 +3026,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);
@@ -3106,6 +3111,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 3bb91c9595..6e4d40ce9f 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -973,6 +973,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);
@@ -1047,6 +1048,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 69731ccdea..bacb178159 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -981,12 +981,34 @@ _outLockRows(StringInfo str, const LockRows *node)
static void
_outLimit(StringInfo str, const Limit *node)
{
+ int i;
WRITE_NODE_TYPE("LIMIT");
_outPlanInfo(str, (const Plan *) node);
WRITE_NODE_FIELD(limitOffset);
WRITE_NODE_FIELD(limitCount);
+ WRITE_ENUM_FIELD(limitOption, LimitOption);
+ WRITE_INT_FIELD(numCols);
+ if (node->numCols > 0)
+ {
+ appendStringInfoString(str, " :uniqColIdx");
+ for (i = 0; i < node->numCols; i++)
+ appendStringInfo(str, " %d", node->uniqColIdx[i]);
+
+ appendStringInfoString(str, " :uniqOperators");
+ for (i = 0; i < node->numCols; i++)
+ appendStringInfo(str, " %u", node->uniqOperators[i]);
+ }
+ else
+ {
+ appendStringInfoString(str, " :uniqColIdx");
+ appendStringInfo(str, " NULL");
+
+ appendStringInfoString(str, " :uniqOperators");
+ appendStringInfo(str, " NULL");
+ }
+
}
static void
@@ -2786,6 +2808,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);
@@ -2996,6 +3019,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 e117867de5..ea15ea35a9 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -277,6 +277,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);
@@ -2319,6 +2320,10 @@ _readLimit(void)
READ_NODE_FIELD(limitOffset);
READ_NODE_FIELD(limitCount);
+ READ_ENUM_FIELD(limitOption, LimitOption);
+ READ_INT_FIELD(numCols);
+ READ_ATTRNUMBER_ARRAY(uniqColIdx, local_node->numCols);
+ READ_OID_ARRAY(uniqOperators, local_node->numCols);
READ_DONE();
}
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index ae46b0140e..7e540849b4 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -2134,7 +2134,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);
/* Must apply correct cost/width data to Limit node */
plan->startup_cost = mminfo->path->startup_cost;
@@ -2437,9 +2439,41 @@ create_limit_plan(PlannerInfo *root, LimitPath *best_path, int flags)
/* Limit doesn't project, so tlist requirements pass through */
subplan = create_plan_recurse(root, best_path->subpath, flags);
- plan = make_limit(subplan,
- best_path->limitOffset,
- best_path->limitCount);
+ if (best_path->limitOption == WITH_TIES)
+ {
+ Query *parse = root->parse;
+ int numsortkeys;
+ AttrNumber *sortColIdx;
+ Oid *sortOperators;
+ ListCell *l;
+
+ numsortkeys = list_length(parse->sortClause);
+ sortColIdx = (AttrNumber *) palloc(numsortkeys * sizeof(AttrNumber));
+ sortOperators = (Oid *) palloc(numsortkeys * sizeof(Oid));
+
+ numsortkeys = 0;
+ foreach(l, parse->sortClause)
+ {
+ SortGroupClause *sortcl = (SortGroupClause *) lfirst(l);
+ TargetEntry *tle = get_sortgroupclause_tle(sortcl, parse->targetList);
+
+ sortColIdx[numsortkeys] = tle->resno;
+ sortOperators[numsortkeys] = sortcl->eqop;
+ numsortkeys++;
+ }
+ plan = make_limit(subplan,
+ best_path->limitOffset,
+ best_path->limitCount,
+ best_path->limitOption,
+ numsortkeys, sortColIdx,
+ sortOperators);
+ }
+ else
+ plan = make_limit(subplan,
+ best_path->limitOffset,
+ best_path->limitCount,
+ best_path->limitOption,
+ 0, NULL, NULL);
copy_generic_path_info(&plan->plan, (Path *) best_path);
@@ -6418,7 +6452,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 ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators)
{
Limit *node = makeNode(Limit);
Plan *plan = &node->plan;
@@ -6430,6 +6465,10 @@ make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount)
node->limitOffset = limitOffset;
node->limitCount = limitCount;
+ node->limitOption = limitOption;
+ node->numCols = ordNumCols;
+ node->uniqColIdx = ordColIdx;
+ node->uniqOperators = ordOperators;
return node;
}
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index c729a99f8b..367772a8d6 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -2140,6 +2140,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 d50d86b252..5fc3010538 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -3410,6 +3410,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);
@@ -3431,6 +3432,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 226927b7ab..ac885659f4 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1302,6 +1302,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,
@@ -1548,6 +1549,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,
@@ -1783,6 +1785,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 6d23bfb0b3..5589a5d556 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -164,6 +164,7 @@ static List *makeOrderedSetArgs(List *directargs, List *orderedargs,
static void insertSelectOptions(SelectStmt *stmt,
List *sortClause, List *lockingClause,
Node *limitOffset, Node *limitCount,
+ void *limitOption,
WithClause *withClause,
core_yyscan_t yyscanner);
static Node *makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg);
@@ -387,7 +388,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
+ reloption_list group_clause TriggerFuncArgs select_limit limit_clause
opt_select_limit opclass_item_list opclass_drop_list
opclass_purpose opt_opfamily transaction_mode_list_or_empty
OptTableFuncElementList TableFuncElementList opt_type_modifiers
@@ -449,7 +450,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
@@ -11210,7 +11211,7 @@ select_no_parens:
| select_clause sort_clause
{
insertSelectOptions((SelectStmt *) $1, $2, NIL,
- NULL, NULL, NULL,
+ NULL, NULL, NULL, NULL,
yyscanner);
$$ = $1;
}
@@ -11218,6 +11219,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $1, $2, $3,
list_nth($4, 0), list_nth($4, 1),
+ (list_nth($4, 2)),
NULL,
yyscanner);
$$ = $1;
@@ -11226,6 +11228,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $1, $2, $4,
list_nth($3, 0), list_nth($3, 1),
+ (list_nth($3, 2)),
NULL,
yyscanner);
$$ = $1;
@@ -11234,7 +11237,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $2, NULL, NIL,
NULL, NULL,
- $1,
+ NULL,$1,
yyscanner);
$$ = $2;
}
@@ -11242,7 +11245,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $2, $3, NIL,
NULL, NULL,
- $1,
+ NULL,$1,
yyscanner);
$$ = $2;
}
@@ -11250,6 +11253,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $2, $3, $4,
list_nth($5, 0), list_nth($5, 1),
+ list_nth($5, 2),
$1,
yyscanner);
$$ = $2;
@@ -11258,6 +11262,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $2, $3, $5,
list_nth($4, 0), list_nth($4, 1),
+ list_nth($4, 2),
$1,
yyscanner);
$$ = $2;
@@ -11544,20 +11549,20 @@ 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 { $$ = list_make3($2, list_nth($1, 0), list_nth($1, 1)); }
+ | offset_clause limit_clause { $$ = list_make3($1, list_nth($2, 0), list_nth($2, 1)); }
+ | limit_clause { $$ = list_make3(NULL, list_nth($1, 0), list_nth($1, 1)); }
+ | offset_clause { $$ = list_make3($1, NULL, NULL); }
;
opt_select_limit:
select_limit { $$ = $1; }
- | /* EMPTY */ { $$ = list_make2(NULL,NULL); }
+ | /* EMPTY */ { $$ = list_make3(NULL, NULL, NULL); }
;
limit_clause:
LIMIT select_limit_value
- { $$ = $2; }
+ { $$ = list_make2($2, NULL); }
| LIMIT select_limit_value ',' select_offset_value
{
/* Disabled because it was too confusing, bjm 2002-02-18 */
@@ -11575,9 +11580,11 @@ 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; }
+ { $$ = list_make2($3, makeString("WITH_ONLY")); }
+ | FETCH first_or_next select_fetch_first_value row_or_rows WITH TIES
+ { $$ = list_make2($3, makeString("WITH_TIES")); }
| FETCH first_or_next row_or_rows ONLY
- { $$ = makeIntConst(1, -1); }
+ { $$ = list_make2(makeIntConst(1, -1), NULL); }
;
offset_clause:
@@ -15828,6 +15835,7 @@ static void
insertSelectOptions(SelectStmt *stmt,
List *sortClause, List *lockingClause,
Node *limitOffset, Node *limitCount,
+ void *limitOption,
WithClause *withClause,
core_yyscan_t yyscanner)
{
@@ -15866,6 +15874,21 @@ insertSelectOptions(SelectStmt *stmt,
parser_errposition(exprLocation(limitCount))));
stmt->limitCount = limitCount;
}
+ if (limitOption)
+ {
+ if (stmt->limitOption)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("multiple LIMIT options not allowed")));
+ if (!stmt->sortClause && strcmp(strVal(limitOption), "WITH_TIES") == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("LIMIT options can not be specified without ORDER BY clause")));
+ if (strcmp(strVal(limitOption), "WITH_ONLY") == 0)
+ stmt->limitOption = WITH_ONLY;
+ else
+ stmt->limitOption = WITH_TIES;
+ }
if (withClause)
{
if (stmt->withClause)
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 880a03e4e4..212820d886 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -2242,12 +2242,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;
} LimitState;
#endif /* EXECNODES_H */
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index cac6ff0eda..ec11e3baed 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -814,4 +814,16 @@ 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
+{
+ WITH_ONLY, /* FETCH FIRST... ONLY */
+ WITH_TIES /* FETCH FIRST... WITH TIES */
+} LimitOption;
+
#endif /* NODES_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index aa4a0dba2a..ce10b55dcf 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 */
List *rowMarks; /* a list of RowMarkClause's */
@@ -1573,6 +1574,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/plannodes.h b/src/include/nodes/plannodes.h
index 5e3d4cdc58..24b7e51a91 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -945,6 +945,10 @@ typedef struct Limit
Plan plan;
Node *limitOffset; /* OFFSET parameter, or NULL if none */
Node *limitCount; /* COUNT parameter, or NULL if none */
+ LimitOption limitOption; /* LIMIT with ties or exact number */
+ int numCols; /* number of columns to check for Similarity */
+ AttrNumber *uniqColIdx; /* their indexes in the target list */
+ Oid *uniqOperators; /* equality operators to compare with */
} Limit;
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index 88d37236f7..2c7f4c71ea 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -1737,6 +1737,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; /* LIMIT with ties or exact number */
} LimitPath;
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 81abcf53a8..4fb67dcf96 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -248,6 +248,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 Path *reparameterize_path(PlannerInfo *root, Path *path,
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index c8ab0280d2..7a9e0c60d5 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -64,7 +64,8 @@ extern Agg *make_agg(List *tlist, List *qual,
int numGroupCols, AttrNumber *grpColIdx, Oid *grpOperators,
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 ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators);
/*
* prototypes for plan/initsplan.c
--
2.13.6
