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

David G. Johnston <david.g.johnston@gmail.com>

From: "David G. Johnston" <david.g.johnston@gmail.com>

To: Tatsuo Ishii <ishii@postgresql.org>

Cc: "ojford@gmail.com" <ojford@gmail.com>, "vik@postgresfriends.org" <vik@postgresfriends.org>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>, "tgl@sss.pgh.pa.us" <tgl@sss.pgh.pa.us>, "andrew@tao11.riddles.org.uk" <andrew@tao11.riddles.org.uk>, "david@fetter.org" <david@fetter.org>, "krasiyan@gmail.com" <krasiyan@gmail.com>

Date: 2024-09-12T02:11:35Z

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

On Wednesday, September 11, 2024, Tatsuo Ishii <ishii@postgresql.org> wrote:

>
> test=# SELECT row_number() IGNORE NULLS OVER w FROM t1 WINDOW w AS (ORDER
> BY i);
>  row_number
> ------------
>           1
>           2
> (2 rows)
>
> The t1 table only contains NULL rows. By using IGNORE NULLS, I think
> it's no wonder that a user expects 0 rows returned, if there's no
> mention in the docs that actually IGNORE NULLS/RESPECT NULLS are just
> ignored in some window functions.
>

My nieve understanding of the nulls treatment is computations are affected,
therefore a zero-argument function is incapable of abiding by this clause
(it should error…).  Your claim that this should somehow produce zero rows
confuses me on two fronts.  One, window function should be incapable of
affecting how many rows are returned.  The query must output two rows
regardless of the result of the window expression (it should at worse
produce the null value).  Two, to produce said null value you have to be
ignoring the row due to the order by clause seeing a null.  But the order
by isn’t part of the computation.

David J.