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

Oliver Ford <ojford@gmail.com>

From: Oliver Ford <ojford@gmail.com>

To: Pg Hackers <pgsql-hackers@postgresql.org>

Cc: tgl@sss.pgh.pa.us, vik@postgresfriends.org, andrew@tao11.riddles.org.uk, david@fetter.org, krasiyan@gmail.com, Tatsuo Ishii <ishii@sraoss.co.jp>

Date: 2023-05-01T11:57:38Z

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

0001-initial-window-ignore.patch (application/x-patch) patch 0001
0002-ignore-nulls-tests.patch (application/x-patch) patch 0002

On Sun, Apr 23, 2023 at 4:29 AM Tatsuo Ishii <ishii@sraoss.co.jp> wrote:

> > Vik Fearing <vik@postgresfriends.org> writes:
> >
> >> For me, this is perfectly okay.  Keep them at the lowest level of
> >> reservation as possible.
> >
> > Yeah, keep them unreserved if at all possible.  Any higher reservation
> > level risks breaking existing applications that might be using these
> > words as column or function names.
>
> Agreed.
> <http://www.sraoss.co.jp>

  <http://www.sraoss.co.jp>
Attached is a new version of the code and tests to implement this. There's
now no modification to windowfuncs.c or the catalog,
it's only a bool added to FuncCall which if set to true, ignores nulls. It
adds IGNORE/RESPECT at the Unreserved, As Label level.

The implementation also aims at better performance over previous versions
by not disabling set_mark, and using an array to
track previous non-null positions in SEEK_HEAD or SEEK_CURRENT with Forward
(lead, but not lag). The mark is set if a row
is out of frame and further rows can't be in frame (to ensure it works with
an exclusion clause).

The attached test patch is mostly the same as in the previous patch
set, but it doesn't fail on row_number anymore as the main patch
only rejects aggregate functions. The test patch also adds a test for
EXCLUDE CURRENT ROW and for two contiguous null rows.

I've not yet tested custom window functions with the patch, but I'm happy
to add them to the test patch in v2 if we want to go this way
in implementing this feature.