> On Wednesday, September 11, 2024, Tatsuo Ishii <is...@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…).
Yes. I actually claimed that row_number() should error out if the clause is provided. > Instead I think it's better that other than lead, lag, first_value, > last_value and nth_value each window function errors out if IGNORE > NULLS/RESPECT NULL are passed to these window functions. > 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. Well I did not claim that. I just gave a possible example what users could misunderstand. Probably my example was not so good. Best reagards, -- Tatsuo Ishii SRA OSS K.K. English: http://www.sraoss.co.jp/index_en/ Japanese:http://www.sraoss.co.jp
