Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options

Tatsuo Ishii <ishii@postgresql.org>

From: Tatsuo Ishii <ishii@postgresql.org>

To: krasiyan@gmail.com, ojford@gmail.com, tgl@sss.pgh.pa.us, vik@postgresfriends.org, andrew@tao11.riddles.org.uk, david@fetter.org

Cc: pgsql-hackers@postgresql.org

Date: 2025-07-07T05:37:37Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Fix coding style with "else".
- 20628b62e46e 19 (unreleased) landed
Fix multi WinGetFuncArgInFrame/Partition calls with IGNORE NULLS.
- 2d7b247cb414 19 (unreleased) landed
Fix Coverity issue reported in commit 2273fa32bce.
- dd766a441d69 19 (unreleased) landed
Use ereport rather than elog in WinCheckAndInitializeNullTreatment.
- 5f3808646f67 19 (unreleased) landed
Avoid uninitialized-variable warnings from older compilers.
- 71540dcdcb22 19 (unreleased) cited
Fix Coverity issues reported in commit 25a30bbd423.
- 2273fa32bce7 19 (unreleased) landed
Improve EXPLAIN's display of window functions.
- 8b1b342544b6 18.0 cited
Automatically generate node support functions
- 964d01ae90c3 16.0 cited

Attachments

v16-0001-Add-IGNORE-NULLS-RESPECT-NULLS-option-to-Window-.patch (application/octet-stream) patch v16-0001

Attached is the v16 patch.

In this patch I have changed row_is_in_frame() API from:

static int	row_is_in_frame(WindowAggState *winstate, int64 pos,
							TupleTableSlot *slot);

to:

static int	row_is_in_frame(WindowObject winobj, int64 pos,
							TupleTableSlot *slot, bool fetch_tuple);

The function is used to decide whether a row specified by pos is in a
frame or not. Previously we needed to always pass "slot" parameter
which is the row in question, fetched by window_gettupleslot. If
IGNORE NULLS option is not passed to window functions, this is fine
because they need to return the row anyway.

However if IGNORE NULLS specified, we need to throw away null rows
until we find the non null row requested by the caller. In reality,
not in all window frames it is required to pass a row to
row_is_in_frame: only when specific frame options are specified. For
example RANGE or GROUP options plus CURRENT ROW frame end option. In
previous patch, I explicitly checked these frame options before
calling row_is_in_frame. However I dislike the way because it's a
layer abstraction violation.

So in this patch I added "fetch_tuple" option to row_is_in_frame so
that it fetches row itself when necessary. A caller now don't need to
fetch the row to pass if fetch_tuple is false.

This way, not only we can avoid the layer violation problem, but
performance is enhanced because tuple is fetched only when it's
necessary.

Note that now the first argument of row_is_in_frame has been changed
from WindowAggState to WindowObject so that row_is_in_frame can call
window_gettupleslot inside.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp