On Sun, Sep 8, 2024 at 2:22 PM Vik Fearing <v...@postgresfriends.org> wrote: > > On 9/7/24 22:25, Oliver Ford wrote: > > On Sat, May 6, 2023 at 9:41 AM Oliver Ford <ojf...@gmail.com> wrote: > >> > >> > >> > >> On Sat, 6 May 2023, 04:57 Tatsuo Ishii, <is...@sraoss.co.jp> wrote: > >>> > >>> Attached is the patch to implement this (on top of your patch). > >>> > >>> test=# SELECT row_number() RESPECT NULLS OVER () FROM (SELECT 1) AS s; > >>> ERROR: window function row_number cannot have RESPECT NULLS or IGNORE > >>> NULLS > >> > >> > >> The last time this was discussed > >> (https://www.postgresql.org/message-id/1037735.1610402426%40sss.pgh.pa.us) > >> it was suggested to make the feature generalizable, beyond what the > >> standard says it should be limited to. > >> > >> With it generalizable, there would need to be extra checks for custom > >> functions, such as if they allow multiple column arguments (which I'll add > >> in v2 of the patch if the design's accepted). > >> > >> So I think we need a consensus on whether to stick to limiting it to > >> several specific functions, or making it generalized yet agreeing the > >> rules to limit it (such as no agg functions, and no functions with > >> multiple column arguments). > > > > Reviving this thread, I've attached a rebased patch with code, docs, > > and tests and added it to November commitfest. > > Excellent! One of these days we'll get this in. :-) > > I have a problem with this test, though: > > SELECT sum(orbit) RESPECT NULLS OVER () FROM planets; -- succeeds > > Why should that succeed? Especially since aggregates such as SUM() will > ignore nulls! The error message on its partner seems to confirm this: > > SELECT sum(orbit) IGNORE NULLS OVER () FROM planets; -- fails > ERROR: aggregate functions do not accept RESPECT/IGNORE NULLS > > I believe they should both fail. > -- > Vik Fearing
Fair enough, here's version 2 where this fails. The ignore_nulls variable is now an int instead of a bool
0002-add-ignore_nulls.patch
Description: Binary data
