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.
