8000 Support FETCH FIRST WITH TIES · postgres/postgres@357889e · GitHub
[go: up one dir, main page]
Skip to content

Commit 357889e

Browse files
alvherrealvherre
committed
Support FETCH FIRST WITH TIES
WITH TIES is an option to the FETCH FIRST N ROWS clause (the SQL standard's spelling of LIMIT), where you additionally get rows that compare equal to the last of those N rows by the columns in the mandatory ORDER BY clause. There was a proposal by Andrew Gierth to implement this functionality in a more powerful way that would yield more features, but the other patch had not been finished at this time, so we decided to use this one for now in the spirit of incremental development. Author: Surafel Temesgen <surafel3000@gmail.com> Reviewed-by: Álvaro Herrera <alvherre@alvh.no-ip.org> Reviewed-by: Tomas Vondra <tomas.vondra@2ndquadrant.com> Discussion: https://postgr.es/m/CALAY4q9ky7rD_A4vf=FVQvCGngm3LOes-ky0J6euMrg=_Se+ag@mail.gmail.com Discussion: https://postgr.es/m/87o8wvz253.fsf@news-spur.riddles.org.uk
1 parent 26a944c commit 357889e

File tree

25 files changed

+610
-69
lines changed

25 files changed

+610
-69
lines changed

‎doc/src/sgml/ref/select.sgml

Lines changed: 9 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -44,7 +44,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
4444
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
4545
[ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
4646
[ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
47-
[ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY ]
47+
[ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } { ONLY | WITH TIES } ]
4848
[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
4949

5050
<phrase>where <replaceable class="parameter">from_item</replaceable> can be one of:</phrase>
@@ -1438,7 +1438,7 @@ OFFSET <replaceable class="parameter">start</replaceable>
14381438
which <productname>PostgreSQL</productname> also supports. It is:
14391439
<synopsis>
14401440
OFFSET <replaceable class="parameter">start</replaceable> { ROW | ROWS }
1441-
FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY
1441+
FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } { ONLY | WITH TIES }
14421442
</synopsis>
14431443
In this syntax, the <replaceable class="parameter">start</replaceable>
14441444
or <replaceable class="parameter">count</replaceable> value is required by
@@ -1448,10 +1448,13 @@ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] {
14481448
ambiguity.
14491449
If <replaceable class="parameter">count</replaceable> is
14501450
omitted in a <literal>FETCH</literal> clause, it defaults to 1.
1451-
<literal>ROW</literal>
1452-
and <literal>ROWS</literal> as well as <literal>FIRST</literal>
1453-
and <literal>NEXT</literal> are noise words that don't influence
1454-
the effects of these clauses.
1451+
The <literal>WITH TIES</literal> option is used to return any additional
1452+
rows that tie for the last place in the result set according to
1453+
<literal>ORDER BY</literal> clause; <literal>ORDER BY</literal>
1454+
is mandatory in this case.
1455+
<literal>ROW</literal> and <literal>ROWS</literal> as well as
1456+
<literal>FIRST</literal> and <literal>NEXT</literal> are noise
1457+
words that don't influence the effects of these clauses.
14551458
According to the standard, the <literal>OFFSET</literal> clause must come
14561459
before the <literal>FETCH</literal> clause if both are present; but
14571460
<productname>PostgreSQL</productname> is laxer and allows either order.

‎src/backend/catalog/sql_features.txt

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -345,7 +345,7 @@ F863 Nested <result offset clause> in <query expression> YES
345345
F864 Top-level <result offset clause> in views YES
346346
F865 <offset row count> in <result offset clause> YES
347347
F866 FETCH FIRST clause: PERCENT option NO
348-
F867 FETCH FIRST clause: WITH TIES option NO
348+
F867 FETCH FIRST clause: WITH TIES option YES
349349
R010 Row pattern recognition: FROM clause NO
350350
R020 Row pattern recognition: WINDOW clause NO
351351
R030 Row pattern recognition: full aggregate support NO

‎src/backend/executor/nodeLimit.c

Lines changed: 144 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -41,6 +41,7 @@ static TupleTableSlot * /* return: a tuple or NULL */
4141
ExecLimit(PlanState *pstate)
4242
{
4343
LimitState *node = castNode(LimitState, pstate);
44+
ExprContext *econtext = node->ps.ps_ExprContext;
4445
ScanDirection direction;
4546
TupleTableSlot *slot;
4647
PlanState *outerPlan;
@@ -102,6 +103,16 @@ ExecLimit(PlanState *pstate)
102103
node->lstate = LIMIT_EMPTY;
103104
return NULL;
104105
}
106+
107+
/*
108+
* Tuple at limit is needed for comparation in subsequent
109+
* execution to detect ties.
110+
*/
111+
if (node->limitOption == LIMIT_OPTION_WITH_TIES &&
112+
node->position - node->offset == node->count - 1)
113+
{
114+
ExecCopySlot(node->last_slot, slot);
115+
}
105116
node->subSlot = slot;
106117
if (++node->position > node->offset)
107118
break;
@@ -125,10 +136,13 @@ ExecLimit(PlanState *pstate)
125136
if (ScanDirectionIsForward(direction))
126137
{
127138
/*
128-
* Forwards scan, so check for stepping off end of window. If
129-
* we are at the end of the window, return NULL without
130-
* advancing the subplan or the position variable; but change
131-
* the state machine state to record having done so.
139+
* Forwards scan, so check for stepping off end of window. At
140+
* the end of the window, the behavior depends on whether WITH
141+
* TIES was specified: in that case, we need to change the
142+
* state machine to LIMIT_WINDOWTIES. If not (nothing was
143+
* specified, or ONLY was) return NULL without advancing the
144+
* subplan or the position variable but change the state
145+
* machine to record having done so
132146
*
133147
* Once at the end, ideally, we can shut down parallel
134148
* resources but that would destroy the parallel context which
@@ -139,27 +153,102 @@ ExecLimit(PlanState *pstate)
139153
if (!node->noCount &&
140154
node->position - node->offset >= node->count)
141155
{
142-
node->lstate = LIMIT_WINDOWEND;
156+
if (node->limitOption == LIMIT_OPTION_COUNT)
157+
{
158+
node->lstate = LIMIT_WINDOWEND;
159+
return NULL;
160+
}
161+
else
162+
{
163+
node->lstate = LIMIT_WINDOWEND_TIES;
164+
/* fall-through */
165+
}
166+
}
167+
else
168+
{
169+
/*
170+
* Get next tuple from subplan, if any.
171+
*/
172+
slot = ExecProcNode(outerPlan);
173+
if (TupIsNull(slot))
174+
{
175+
node->lstate = LIMIT_SUBPLANEOF;
176+
return NULL;
177+
}
178+
179+
/*
180+
* Tuple at limit is needed for comparation in subsequent
181+
* execution to detect ties.
182+
*/
183+
if (node->limitOption == LIMIT_OPTION_WITH_TIES &&
184+
node->position - node->offset == node->count - 1)
185+
{
186+
ExecCopySlot(node->last_slot, slot);
187+
}
188+
node->subSlot = slot;
189+
node->position++;
190+
break;
191+
}
192+
}
193+
else
194+
{
195+
/*
196+
* Backwards scan, so check for stepping off start of window.
197+
* As above, change only state-machine status if so.
198+
*/
199+
if (node->position <= node->offset + 1)
200+
{
201+
node->lstate = LIMIT_WINDOWSTART;
143202
return NULL;
144203
}
145204

146205
/*
147-
* Get next tuple from subplan, if any.
206+
* Get previous tuple from subplan; there should be one!
207+
*/
208+
slot = ExecProcNode(outerPlan);
209+
if (TupIsNull(slot))
210+
elog(ERROR, "LIMIT subplan failed to run backwards");
211+
node->subSlot = slot;
212+
node->position--;
213+
break;
214+
}
215+
216+
case LIMIT_WINDOWEND_TIES:
217+
if (ScanDirectionIsForward(direction))
218+
{
219+
/*
220+
* Advance the subplan until we find the first row with
221+
* different ORDER BY pathkeys.
148222
*/
149223
slot = ExecProcNode(outerPlan);
150224
if (TupIsNull(slot))
151225
{
152226
node->lstate = LIMIT_SUBPLANEOF;
153227
return NULL;
154228
}
155-
node->subSlot = slot;
156-
node->position++;
229+
230+
/*
231+
* Test if the new tuple and the last tuple match. If so we
232+
* return the tuple.
233+
*/
234+
econtext->ecxt_innertuple = slot;
235+
econtext->ecxt_outertuple = node->last_slot;
236+
if (ExecQualAndReset(node->eqfunction, econtext))
237+
{
238+
node->subSlot = slot;
239+
node->position++;
240+
}
241+
else
242+
{
243+
node->lstate = LIMIT_WINDOWEND;
244+
return NULL;
245+
}
157246
}
158247
else
159248
{
160249
/*
161250
* Backwards scan, so check for stepping off start of window.
162-
* As above, change only state-machine status if so.
251+
* Change only state-machine status if so.
163252
*/
164253
if (node->position <= node->offset + 1)
165254
{
@@ -168,13 +257,15 @@ ExecLimit(PlanState *pstate)
168257
}
169258

170259
/*
171-
* Get previous tuple from subplan; there should be one!
260+
* Get previous tuple from subplan; there should be one! And
261+
* change state-machine status.
172262
*/
173263
slot = ExecProcNode(outerPlan);
174264
if (TupIsNull(slot))
175265
elog(ERROR, "LIMIT subplan failed to run backwards");
176266
node->subSlot = slot;
177267
node->position--;
268+
node->lstate = LIMIT_INWINDOW;
178269
}
179270
break;
180271

@@ -199,12 +290,28 @@ ExecLimit(PlanState *pstate)
199290
return NULL;
200291

201292
/*
202-
* Backing up from window end: simply re-return the last tuple
203-
* fetched from the subplan.
293+
* We already past one position to detect ties so re-fetch
294+
* previous tuple; there should be one! Note previous tuple must
295+
* be in window.
204296
*/
205-
slot = node->subSlot;
206-
node->lstate = LIMIT_INWINDOW;
207-
/* position does not change 'cause we didn't advance it before */
297+
if (node->limitOption == LIMIT_OPTION_WITH_TIES)
298+
{
299+
slot = ExecProcNode(outerPlan);
300+
if (TupIsNull(slot))
301+
elog(ERROR, "LIMIT subplan failed to run backwards");
302+
node->subSlot = slot;
303+
node->lstate = LIMIT_INWINDOW;
304+
}
305+
else
306+
{
307+
/*
308+
* Backing up from window end: simply re-return the last tuple
309+
* fetched from the subplan.
310+
*/
311+
slot = node->subSlot;
312+
node->lstate = LIMIT_INWINDOW;
313+
/* position does not change 'cause we didn't advance it before */
314+
}
208315
break;
209316

210317
case LIMIT_WINDOWSTART:
@@ -319,7 +426,7 @@ recompute_limits(LimitState *node)
319426
static int64
320427
compute_tuples_needed(LimitState *node)
321428
{
322-
if (node->noCount)
429+
if ((node->noCount) || (node->limitOption == LIMIT_OPTION_WITH_TIES))
323430
return -1;
324431
/* Note: if this overflows, we'll return a negative value, which is OK */
325432
return node->count + node->offset;
@@ -372,6 +479,7 @@ ExecInitLimit(Limit *node, EState *estate, int eflags)
372479
(PlanState *) limitstate);
373480
limitstate->limitCount = ExecInitExpr((Expr *) node->limitCount,
374481
(PlanState *) limitstate);
482+
limitstate->limitOption = node->limitOption;
375483

376484
/*
377485
* Initialize result type.
@@ -388,6 +496,26 @@ ExecInitLimit(Limit *node, EState *estate, int eflags)
388496
*/
389497
limitstate->ps.ps_ProjInfo = NULL;
390498

499+
/*
500+
* Initialize the equality evaluation, to detect ties.
501+
*/
502+
if (node->limitOption == LIMIT_OPTION_WITH_TIES)
503+
{
504+
TupleDesc desc;
505+
const TupleTableSlotOps *ops;
506+
507+
desc = ExecGetResultType(outerPlanState(limitstate));
508+
ops = ExecGetResultSlotOps(outerPlanState(limitstate), NULL);
509+
510+
limitstate->last_slot = ExecInitExtraTupleSlot(estate, desc, ops);
511+
limitstate->eqfunction = execTuplesMatchPrepare(desc,
512+
node->uniqNumCols,
513+
node->uniqColIdx,
514+
node->uniqOperators,
515+
node->uniqCollations,
516+
&limitstate->ps);
517+
}
518+
391519
return limitstate;
392520
}
393521

‎src/backend/nodes/copyfuncs.c

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1185,6 +1185,11 @@ _copyLimit(const Limit *from)
11851185
*/
11861186
COPY_NODE_FIELD(limitOffset);
11871187
COPY_NODE_FIELD(limitCount);
1188+
COPY_SCALAR_FIELD(limitOption);
1189+
COPY_SCALAR_FIELD(uniqNumCols);
1190+
COPY_POINTER_FIELD(uniqColIdx, from->uniqNumCols * sizeof(AttrNumber));
1191+
COPY_POINTER_FIELD(uniqOperators, from->uniqNumCols * sizeof(Oid));
1192+
COPY_POINTER_FIELD(uniqCollations, from->uniqNumCols * sizeof(Oid));
11881193

11891194
return newnode;
11901195
}
@@ -3079,6 +3084,7 @@ _copyQuery(const Query *from)
30793084
COPY_NODE_FIELD(sortClause);
30803085
COPY_NODE_FIELD(limitOffset);
30813086
COPY_NODE_FIELD(limitCount);
3087+
COPY_SCALAR_FIELD(limitOption);
30823088
COPY_NODE_FIELD(rowMarks);
30833089
COPY_NODE_FIELD(setOperations);
30843090
COPY_NODE_FIELD(constraintDeps);
@@ -3163,6 +3169,7 @@ _copySelectStmt(const SelectStmt *from)
31633169
COPY_NODE_FIELD(sortClause);
31643170
COPY_NODE_FIELD(limitOffset);
31653171
COPY_NODE_FIELD(limitCount);
3172+
COPY_SCALAR_FIELD(limitOption);
31663173
COPY_NODE_FIELD(lockingClause);
31673174
COPY_NODE_FIELD(withClause);
31683175
COPY_SCALAR_FIELD(op);

‎src/backend/nodes/equalfuncs.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -981,6 +981,7 @@ _equalQuery(const Query *a, const Query *b)
981981
COMPARE_NODE_FIELD(sortClause);
982982
COMPARE_NODE_FIELD(limitOffset);
983983
COMPARE_NODE_FIELD(limitCount);
984+
COMPARE_SCALAR_FIELD(limitOption);
984985
COMPARE_NODE_FIELD(rowMarks);
985986
COMPARE_NODE_FIELD(setOperations);
986987
COMPARE_NODE_FIELD(constraintDeps);
@@ -1055,6 +1056,7 @@ _equalSelectStmt(const SelectStmt *a, const SelectStmt *b)
10551056
COMPARE_NODE_FIELD(sortClause);
10561057
COMPARE_NODE_FIELD(limitOffset);
10571058
COMPARE_NODE_FIELD(limitCount);
1059+
COMPARE_SCALAR_FIELD(limitOption);
10581060
COMPARE_NODE_FIELD(lockingClause);
10591061
COMPARE_NODE_FIELD(withClause);
10601062
COMPARE_SCALAR_FIELD(op);

‎src/backend/nodes/outfuncs.c

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -931,6 +931,11 @@ _outLimit(StringInfo str, const Limit *node)
931931

932932
WRITE_NODE_FIELD(limitOffset);
933933
WRITE_NODE_FIELD(limitCount);
934+
WRITE_ENUM_FIELD(limitOption, LimitOption);
935+
WRITE_INT_FIELD(uniqNumCols);
936+
WRITE_ATTRNUMBER_ARRAY(uniqColIdx, node->uniqNumCols);
937+
WRITE_OID_ARRAY(uniqOperators, node->uniqNumCols);
938+
WRITE_OID_ARRAY(uniqCollations, node->uniqNumCols);
934939
}
935940

936941
static void
@@ -2741,6 +2746,7 @@ _outSelectStmt(StringInfo str, const SelectStmt *node)
27412746
WRITE_NODE_FIELD(sortClause);
27422747
WRITE_NODE_FIELD(limitOffset);
27432748
WRITE_NODE_FIELD(limitCount);
2749+
WRITE_ENUM_FIELD(limitOption, LimitOption);
27442750
WRITE_NODE_FIELD(lockingClause);
27452751
WRITE_NODE_FIELD(withClause);
27462752
WRITE_ENUM_FIELD(op, SetOperation);
@@ -2952,6 +2958,7 @@ _outQuery(StringInfo str, const Query *node)
29522958
WRITE_NODE_FIELD(sortClause);
29532959
WRITE_NODE_FIELD(limitOffset);
29542960
WRITE_NODE_FIELD(limitCount);
2961+
WRITE_ENUM_FIELD(limitOption, LimitOption);
29552962
WRITE_NODE_FIELD(rowMarks);
29562963
WRITE_NODE_FIELD(setOperations);
29572964
WRITE_NODE_FIELD(constraintDeps);

‎src/backend/nodes/readfuncs.c

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -278,6 +278,7 @@ _readQuery(void)
278278
READ_NODE_FIELD(sortClause);
279279
READ_NODE_FIELD(limitOffset);
280280
READ_NODE_FIELD(limitCount);
281+
READ_ENUM_FIELD(limitOption, LimitOption);
281282
READ_NODE_FIELD(rowMarks);
282283
READ_NODE_FIELD(setOperations);
283284
READ_NODE_FIELD(constraintDeps);
@@ -2398,6 +2399,11 @@ _readLimit(void)
23982399

23992400
READ_NODE_FIELD(limitOffset);
24002401
READ_NODE_FIELD(limitCount);
2402+
READ_ENUM_FIELD(limitOption, LimitOption);
2403+
READ_INT_FIELD(uniqNumCols);
2404+
READ_ATTRNUMBER_ARRAY(uniqColIdx, local_node->uniqNumCols);
2405+
READ_OID_ARRAY(uniqOperators, local_node->uniqNumCols);
2406+
READ_OID_ARRAY(uniqCollations, local_node->uniqNumCols);
24012407

24022408
READ_DONE();
24032409
}

0 commit comments

Comments
 (0)
0