Re: FETCH FIRST clause WITH TIES option

From: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
To: Surafel Temesgen <surafel3000(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: FETCH FIRST clause WITH TIES option
Date: 2018-10-28 18:20:35
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-hackers

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

* 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;

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.


Tomas Vondra
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment Content-Type Size
0002-review.patch text/x-patch 9.4 KB
0001-rebased-patch.patch text/x-patch 26.7 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2018-10-28 18:27:44 Re: [HACKERS] [PATCH] Incremental sort
Previous Message Andrey Borodin 2018-10-28 17:32:33 Re: GiST VACUUM