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
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.
Regards
Surafel
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 4db8142afa..a93e856946 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 7c8220cf65..3a753db278 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -1139,6 +1139,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;
}
@@ -3024,6 +3028,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);
@@ -3108,6 +3113,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 378f2facb8..ba74fe9ffc 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 6269f474d2..bd330f250e 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -980,12 +980,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
@@ -2785,6 +2807,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);
@@ -2995,6 +3018,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 3254524223..168fb023a3 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -266,6 +266,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);
@@ -2286,6 +2287,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 ae41c9efa0..20d7a52e4f 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -2139,7 +2139,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;
@@ -2442,9 +2444,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);
@@ -6446,7 +6480,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;
@@ -6458,6 +6493,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 96bf0601a8..7322dfc90f 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -2166,6 +2166,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 c5aaaf5c22..0315e434b6 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -3412,6 +3412,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);
@@ -3433,6 +3434,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 c601b6d40d..7c682c5c11 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1301,6 +1301,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,
@@ -1547,6 +1548,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,
@@ -1782,6 +1784,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 87f5e95827..45a481480e 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
@@ -11218,7 +11219,7 @@ select_no_parens:
| select_clause sort_clause
{
insertSelectOptions((SelectStmt *) $1, $2, NIL,
- NULL, NULL, NULL,
+ NULL, NULL, NULL, NULL,
yyscanner);
$$ = $1;
}
@@ -11226,6 +11227,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $1, $2, $3,
list_nth($4, 0), list_nth($4, 1),
+ (list_nth($4, 2)),
NULL,
yyscanner);
$$ = $1;
@@ -11234,6 +11236,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $1, $2, $4,
list_nth($3, 0), list_nth($3, 1),
+ (list_nth($3, 2)),
NULL,
yyscanner);
$$ = $1;
@@ -11242,7 +11245,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $2, NULL, NIL,
NULL, NULL,
- $1,
+ NULL,$1,
yyscanner);
$$ = $2;
}
@@ -11250,7 +11253,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $2, $3, NIL,
NULL, NULL,
- $1,
+ NULL,$1,
yyscanner);
$$ = $2;
}
@@ -11258,6 +11261,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $2, $3, $4,
list_nth($5, 0), list_nth($5, 1),
+ list_nth($5, 2),
$1,
yyscanner);
$$ = $2;
@@ -11266,6 +11270,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $2, $3, $5,
list_nth($4, 0), list_nth($4, 1),
+ list_nth($4, 2),
$1,
yyscanner);
$$ = $2;
@@ -11552,20 +11557,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 */
@@ -11583,9 +11588,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:
@@ -15836,6 +15843,7 @@ static void
insertSelectOptions(SelectStmt *stmt,
List *sortClause, List *lockingClause,
Node *limitOffset, Node *limitCount,
+ void *limitOption,
WithClause *withClause,
core_yyscan_t yyscanner)
{
@@ -15874,6 +15882,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 41fa2052a2..e0e3d9b4ff 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -2219,12 +2219,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 697d3d7a5f..e6fef6b574 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -811,4 +811,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 07ab1a3dde..58702f8afe 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 */
@@ -1560,6 +1561,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 7c2abbd03a..b0ac73df18 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -952,6 +952,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 41caf873fb..4282d5195f 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -1735,6 +1735,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 7c5ff22650..17b62c7b8c 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
