On Sun, Sep 8, 2024 at 2:22 PM Vik Fearing <vik(at)postgresfriends(dot)org> wrote:
>
> On 9/7/24 22:25, Oliver Ford wrote:
> > On Sat, May 6, 2023 at 9:41 AM Oliver Ford <ojford(at)gmail(dot)com> wrote:
> >>
> >>
> >>
> >> On Sat, 6 May 2023, 04:57 Tatsuo Ishii, <ishii(at)sraoss(dot)co(dot)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