Hi Surafel,
I've been looking at the patch with the intent to commit it, but once
again I ran into stuff that seems suspicious to me but am not familiar
with enough to say if it's OK. I'm sorry about that :-(
First, a couple of tweaks in the attached v9 of the patch:
1) I've removed the costing changes. As Tom mentions elsewhere in this
thread, that's probably not needed for v1 and it's true those estimates
are probably somewhat sketchy anyway.
2) v8 did this (per my comment):
- ExecSetTupleBound(compute_tuples_needed(node), outerPlanState(node));
+ if(node->limitOption == EXACT_NUMBER)
+ ExecSetTupleBound(compute_tuples_needed(node),
outerPlanState(node));
but I noticed the comment immediately above that says
* Notify child node about limit. Note: think not to "optimize" by
* skipping ExecSetTupleBound if compute_tuples_needed returns < 0. We
* must update the child node anyway, in case this is a rescan and the
* previous time we got a different result.
which applies to WITH_TIES too, no? So I've modified compute_tuples_needed
to return -1, and reverted this change. Not sure, though.
3) I'm a bit confused by the initialization added to ExecInitLimit. It
first gets the tuple descriptor from the limitstate (it should not do so
directly but use ExecGetResultType). But when it creates the extra slot,
it uses ops extracted from the outer plan. That's strange, I guess ...
And then it extracts the descriptor from the outer plan and uses it when
calling execTuplesMatchPrepare. But AFAIK it's going to be compared to
the last_slot, which is using a descriptor from the limitstate.
IMHO all of this should use descriptor/ops from the outer plan, no? It
probably does not change anything because limit does not project, but it
seems confusing.
cheers
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 06d611b64c..e83d309c5b 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>
@@ -1430,7 +1430,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
@@ -1440,7 +1440,10 @@ 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>
+ <literal>ROW</literal> .
+ <literal>WITH TIES</literal> option is used to return two or more rows
+ that tie for the last place in the result 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 baa669abe8..60660e710f 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;
@@ -126,12 +127,16 @@ ExecLimit(PlanState *pstate)
{
/*
* 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.
+ * we are at the end of the window, the
behavior depends whether
+ * ONLY or WITH TIES was specified. In case of
ONLY, we return
+ * NULL without advancing the subplan or the
position variable;
+ * but change the state machine state to record
having done so.
+ * In the WITH TIES mode, we need to advance
the subplan until
+ * we find the first row with different ORDER
BY pathkeys.
*/
if (!node->noCount &&
- node->position - node->offset >=
node->count)
+ node->position - node->offset >=
node->count &&
+ node->limitOption == EXACT_NUMBER)
{
node->lstate = LIMIT_WINDOWEND;
@@ -144,18 +149,69 @@ 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))
+ {
+ 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;
+ }
- /*
- * Get next tuple from subplan, if any.
- */
- slot = ExecProcNode(outerPlan);
- if (TupIsNull(slot))
+ }
+ else
{
- node->lstate = LIMIT_SUBPLANEOF;
- return NULL;
+ /*
+ * 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 == WITH_TIES &&
+ node->position - node->offset
== node->count - 1)
+ {
+ ExecCopySlot(node->last_slot,
slot);
+ }
+ node->subSlot = slot;
+ node->position++;
}
- node->subSlot = slot;
- node->position++;
}
else
{
@@ -321,7 +377,7 @@ recompute_limits(LimitState *node)
static int64
compute_tuples_needed(LimitState *node)
{
- if (node->noCount)
+ if ((node->noCount) || (node->limitOption != EXACT_NUMBER))
return -1;
/* Note: if this overflows, we'll return a negative value, which is OK
*/
return node->count + node->offset;
@@ -374,6 +430,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.
@@ -390,6 +447,26 @@ 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 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 8f51315bee..dd046ed5f7 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -1143,6 +1143,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;
}
@@ -3033,6 +3038,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);
@@ -3117,6 +3123,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 68b51f3de7..02b59aac83 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 82ca6826ab..446b38f75f 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -907,6 +907,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
@@ -2700,6 +2705,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);
@@ -2910,6 +2916,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 3b96492b36..fc76f75665 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);
@@ -2333,6 +2334,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 efe073a3ee..5eb7dcb90e 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -2286,7 +2286,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;
@@ -2593,13 +2595,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 == 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);
@@ -6467,7 +6499,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;
@@ -6479,6 +6512,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 cbd3fb8e0e..f9c20e7be3 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -2306,6 +2306,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 36aee35d46..de1b344122 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -3570,6 +3570,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);
@@ -3591,6 +3592,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 400558b552..1d9bf30e20 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 b51f12dc23..50508c17a4 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -165,6 +165,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);
@@ -393,7 +394,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
@@ -455,7 +456,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
@@ -11222,7 +11223,7 @@ select_no_parens:
| select_clause sort_clause
{
insertSelectOptions((SelectStmt *) $1,
$2, NIL,
-
NULL, NULL, NULL,
+
NULL, NULL, NULL, NULL,
yyscanner);
$$ = $1;
}
@@ -11230,6 +11231,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $1,
$2, $3,
list_nth($4, 0), list_nth($4, 1),
+
(list_nth($4, 2)),
NULL,
yyscanner);
$$ = $1;
@@ -11238,6 +11240,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $1,
$2, $4,
list_nth($3, 0), list_nth($3, 1),
+
(list_nth($3, 2)),
NULL,
yyscanner);
$$ = $1;
@@ -11246,7 +11249,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $2,
NULL, NIL,
NULL, NULL,
-
$1,
+
NULL,$1,
yyscanner);
$$ = $2;
}
@@ -11254,7 +11257,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $2,
$3, NIL,
NULL, NULL,
-
$1,
+
NULL,$1,
yyscanner);
$$ = $2;
}
@@ -11262,6 +11265,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $2,
$3, $4,
list_nth($5, 0), list_nth($5, 1),
+
list_nth($5, 2),
$1,
yyscanner);
$$ = $2;
@@ -11270,6 +11274,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $2,
$3, $5,
list_nth($4, 0), list_nth($4, 1),
+
list_nth($4, 2),
$1,
yyscanner);
$$ = $2;
@@ -11563,20 +11568,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 */
@@ -11594,9 +11599,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("EXACT_NUMBER")); }
+ | 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:
@@ -15853,6 +15860,7 @@ static void
insertSelectOptions(SelectStmt *stmt,
List *sortClause, List *lockingClause,
Node *limitOffset, Node *limitCount,
+ void *limitOption,
WithClause *withClause,
core_yyscan_t yyscanner)
{
@@ -15891,6 +15899,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("WITH TIES options can not be
specified without ORDER BY clause")));
+ if (strcmp(strVal(limitOption), "EXACT_NUMBER") == 0)
+ stmt->limitOption = EXACT_NUMBER;
+ 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 a5e4b7ef2e..7ec18ca42d 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -2301,12 +2301,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 ffb4cd4bcc..f0a7aff679 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -821,4 +821,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
+{
+ EXACT_NUMBER, /* 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 94c0b7a9dd..264b4345df 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 f8429df9e1..133dd319a9 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -1790,6 +1790,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 1cce7621c2..fbebfb458c 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -953,6 +953,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 437250f557..0ef9220477 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -264,6 +264,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 6d10bf3ee8..21e0e7cc01 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..199725ec7f 100644
--- a/src/test/regress/expected/limit.out
+++ b/src/test/regress/expected/limit.out
@@ -503,3 +503,38 @@ 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 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..8009b746cb 100644
--- a/src/test/regress/sql/limit.sql
+++ b/src/test/regress/sql/limit.sql
@@ -141,3 +141,20 @@ 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 2 ROW ONLY;
+-- should fail
+SELECT ''::text AS two, unique1, unique2, stringu1
+ FROM onek WHERE unique1 > 50
+ FETCH FIRST 2 ROW WITH TIES;
