Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix coding style with "else".

  2. Fix multi WinGetFuncArgInFrame/Partition calls with IGNORE NULLS.

  3. Fix Coverity issue reported in commit 2273fa32bce.

  4. Use ereport rather than elog in WinCheckAndInitializeNullTreatment.

  5. Avoid uninitialized-variable warnings from older compilers.

  6. Fix Coverity issues reported in commit 25a30bbd423.

  7. Improve EXPLAIN's display of window functions.

  8. Automatically generate node support functions

  1. Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options

    Oliver Ford <ojford@gmail.com> — 2018-07-13T12:52:00Z

    Adds the options RESPECT/IGNORE NULLS (null treatment clause) and FROM
FIRST/LAST to the non-aggregate window functions.

A previous patch
(https://www.postgresql.org/message-id/CA+=vxNa5_N1q5q5OkxC0aQnNdbo2Ru6GVw+86wk+oNsUNJDLig@mail.gmail.com)
partially implemented this feature. However, that patch worked by
adding the null treatment clause to the window frame's frameOptions
variable, and consequently had the limitation that it wasn't possible
to reuse a window frame definition in a single query where two
functions were called that had different null treatment options. This
meant that the patch was never committed. The attached path takes a
different approach which gets around this limitation.

For example, the following query would not work correctly with the
implementation in the old patch but does with the attached patch:

WITH cte (x) AS (
select null union select 1 union select 2)
SELECT x,
first_value(x) over w as with_default,
first_value(x) respect nulls over w as with_respect,
first_value(x) ignore nulls over w as with_ignore
from cte WINDOW w as (order by x nulls first rows between unbounded
preceding and unbounded following);
 x | with_default | with_respect | with_ignore
---+--------------+--------------+-------------
   |              |              |           1
 1 |              |              |           1
 2 |              |              |           1
(3 rows)


== Implementation ==

The patch adds two types to the pg_type catalog: "ignorenulls" and
"fromlast". These types are of the Boolean category, and work as
wrappers around the bool type. They are used as function arguments to
extra versions of the window functions that take additional boolean
arguments. RESPECT NULLS and FROM FIRST are ignored by the parser, but
IGNORE NULLS and FROM LAST lead to the extra versions being called
with arguments to ignore nulls and order from last.

== Testing ==

Updated documentation and added regression tests. All existing tests
pass. This change will need a catversion bump.
Thanks to Krasiyan Andreev for initially testing this patch.

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

    David Fetter <david@fetter.org> — 2018-07-28T18:59:58Z

    On Fri, Jul 13, 2018 at 01:52:00PM +0100, Oliver Ford wrote:
> Adds the options RESPECT/IGNORE NULLS (null treatment clause) and FROM
> FIRST/LAST to the non-aggregate window functions.

Please find attached an updated version for OID drift.

Best,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

    Krasiyan Andreev <krasiyan@gmail.com> — 2018-09-18T12:05:07Z

    Hi,
Patch applies and compiles, all included tests and building of the docs
pass.
I am using last version from more than two months ago in production
environment with real data and I didn't find any bugs,
so I'm marking this patch as ready for committer in the commitfest app.

На сб, 28.07.2018 г. в 22:00 ч. David Fetter <david@fetter.org> написа:

> On Fri, Jul 13, 2018 at 01:52:00PM +0100, Oliver Ford wrote:
> > Adds the options RESPECT/IGNORE NULLS (null treatment clause) and FROM
> > FIRST/LAST to the non-aggregate window functions.
>
> Please find attached an updated version for OID drift.
>
> Best,
> David.
> --
> David Fetter <david(at)fetter(dot)org> http://fetter.org/
> Phone: +1 415 235 3778
>
> Remember to vote!
> Consider donating to Postgres: http://www.postgresql.org/about/donate
>

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

    Andrew Gierth <andrew@tao11.riddles.org.uk> — 2018-09-22T20:06:32Z

    >>>>> "Krasiyan" == Krasiyan Andreev <krasiyan@gmail.com> writes:

 Krasiyan> Hi,

 Krasiyan> Patch applies and compiles, all included tests and building
 Krasiyan> of the docs pass. I am using last version from more than two
 Krasiyan> months ago in production environment with real data and I
 Krasiyan> didn't find any bugs, so I'm marking this patch as ready for
 Krasiyan> committer in the commitfest app.

Unfortunately, reviewing it from a committer perspective - I can't
possibly commit this as it stands, and anything I did to it would be
basically a rewrite of much of it.

Some of the problems could be fixed. For example the type names could be
given pg_* prefixes (it's clearly not acceptable to create random
special-purpose boolean subtypes in pg_catalog and _not_ give them such
a prefix), and the precedence hackery in gram.y could have comments
added (gram.y is already bad enough; _anything_ fancy with precedence
has to be described in the comments). But I don't like that hack with
the special types at all, and I think that needs a better solution.

Normally I'd push hard to try and get some solution that's sufficiently
generic to allow user-defined functions to make use of the feature. But
I think the SQL spec people have managed to make that literally
impossible in this case, what with the FROM keyword appearing in the
middle of a production and not followed by anything sufficiently
distinctive to even use for extra token lookahead.

Also, as has been pointed out in a number of previous features, we're
starting to accumulate identifiers that are reserved in subtly different
ways from our basic four-category system (which is itself a significant
elaboration compared to the spec's simple reserved/unreserved
distinction). As I recall this objection was specifically raised for
CUBE, but justified there by the existence of the contrib/cube extension
(and the fact that the standard CUBE() construct is used only in very
specific places in the syntax). This patch would make lead / lag /
first_value / last_value / nth_value syntactically "special" while not
actually reserving them (beyond having them in unreserved_keywords); I
think serious consideration should be given to whether they should
instead become col_name_keywords (which would, I believe, make it
unnecessary to mess with precedence).

Anyone have any thoughts or comments on the above?

-- 
Andrew (irc:RhodiumToad)

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2018-09-23T21:13:52Z

    Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> Normally I'd push hard to try and get some solution that's sufficiently
> generic to allow user-defined functions to make use of the feature. But
> I think the SQL spec people have managed to make that literally
> impossible in this case, what with the FROM keyword appearing in the
> middle of a production and not followed by anything sufficiently
> distinctive to even use for extra token lookahead.

Yeah.  Is there any appetite for a "Just Say No" approach?  That is,
refuse to implement the spec's syntax on the grounds that it's too
brain-dead to even consider, and instead provide some less random,
more extensibility-friendly way to accomplish the same thing?

The FROM FIRST/LAST bit seems particularly badly thought through,
because AFAICS it is flat out ambiguous with a normal FROM clause
immediately following the window function call.  The only way to
make it not so would be to make FIRST and LAST be fully reserved,
which is neither a good idea nor spec-compliant.

In short, there's a really good case to be made here that the SQL
committee is completely clueless about syntax design, and so we
shouldn't follow this particular pied piper.

> ... This patch would make lead / lag /
> first_value / last_value / nth_value syntactically "special" while not
> actually reserving them (beyond having them in unreserved_keywords); I
> think serious consideration should be given to whether they should
> instead become col_name_keywords (which would, I believe, make it
> unnecessary to mess with precedence).

I agree that messing with the precedence rules is likely to have
unforeseen and undesirable side-effects.  Generally, if you need
to create a precedence rule, that's because your grammar is
ambiguous.  Precedence fixes that in a well-behaved way only for
cases that actually are very much like operator precedence rules.
Otherwise, you may just be papering over something that isn't
working very well.  See e.g. commits 670a6c7a2 and 12b716457
for past cases where we learned that the hard way.  (The latter
also points out that if you must have a precedence hack, it's
safer to hack individual rules than to stick precedences onto
terminal symbols.)  In the case at hand, since the proposed patch
doesn't make FIRST and LAST be fully reserved, it seems just about
certain that it can be made to misbehave, including failing on
queries that were and should remain legal.

			regards, tom lane

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

    Andrew Gierth <andrew@tao11.riddles.org.uk> — 2018-09-23T22:22:30Z

    >>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

 Tom> The FROM FIRST/LAST bit seems particularly badly thought through,
 Tom> because AFAICS it is flat out ambiguous with a normal FROM clause
 Tom> immediately following the window function call. The only way to
 Tom> make it not so would be to make FIRST and LAST be fully reserved,
 Tom> which is neither a good idea nor spec-compliant.

In the actual spec syntax it's not ambiguous at all because NTH_VALUE is
a reserved word (as are LEAD, LAG, FIRST_VALUE and LAST_VALUE), and OVER
is a mandatory clause in its syntax, so a FROM appearing before the OVER
must be part of a FROM FIRST/LAST and not introducing a FROM-clause.

In our syntax, if we made NTH_VALUE etc. a col_name_keyword (and thus
not legal as a function name outside its own special syntax) it would
also become unambiguous.

i.e. given this token sequence (with . marking the current posision):

  select nth_value(x) . from first ignore 

if we know up front that "nth_value" is a window function and not any
other kind of function, we know that we have to shift the "from" rather
than reducing the select-list because we haven't seen an "over" yet.
(Neither "first" nor "ignore" are reserved, so "select foo(x) from first
ignore;" is a valid and complete query, and without reserving the
function name we'd need at least four tokens of lookahead to decide
otherwise.)

This is why I think the col_name_keyword option needs to be given
serious consideration - it still doesn't reserve the names as strongly
as the spec does, but enough to make the standard syntax work without
needing any dubious hacks.

-- 
Andrew (irc:RhodiumToad)

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2018-09-23T23:10:25Z

    Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
>  Tom> The FROM FIRST/LAST bit seems particularly badly thought through,
>  Tom> because AFAICS it is flat out ambiguous with a normal FROM clause
>  Tom> immediately following the window function call. The only way to
>  Tom> make it not so would be to make FIRST and LAST be fully reserved,
>  Tom> which is neither a good idea nor spec-compliant.

> In the actual spec syntax it's not ambiguous at all because NTH_VALUE is
> a reserved word (as are LEAD, LAG, FIRST_VALUE and LAST_VALUE), and OVER
> is a mandatory clause in its syntax, so a FROM appearing before the OVER
> must be part of a FROM FIRST/LAST and not introducing a FROM-clause.

Hmm ...

> In our syntax, if we made NTH_VALUE etc. a col_name_keyword (and thus
> not legal as a function name outside its own special syntax) it would
> also become unambiguous.
> i.e. given this token sequence (with . marking the current posision):
>   select nth_value(x) . from first ignore 
> if we know up front that "nth_value" is a window function and not any
> other kind of function, we know that we have to shift the "from" rather
> than reducing the select-list because we haven't seen an "over" yet.

I don't really find that to be a desirable solution, because quite aside
from the extensibility problem, it would mean that a lot of errors
become "syntax error" where we formerly gave a more useful message.

This does open up a thought about how to proceed, though.  I'd been
trying to think of a way to solve this using base_yylex's ability to
do some internal lookahead and change token types based on that.
If you just think of recognizing FROM FIRST/LAST, you get nowhere
because that's still legal in other contexts.  But if you were to
look for FROM followed by FIRST/LAST followed by IGNORE/RESPECT/OVER,
I think that could only validly happen in this syntax.  It'd take
some work to extend base_yylex to look ahead 2 tokens not one, but
I'm sure that could be done.  (You'd also need a lookahead rule to
match "IGNORE/RESPECT NULLS OVER", but that seems just as doable.)
Then the relevant productions use FROM_LA, IGNORE_LA, RESPECT_LA
instead of the corresponding bare tokens, and the grammar no longer
has an ambiguity problem.

			regards, tom lane

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

    Andrew Gierth <andrew@tao11.riddles.org.uk> — 2018-09-23T23:46:40Z

    >>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

 Tom> If you just think of recognizing FROM FIRST/LAST, you get nowhere
 Tom> because that's still legal in other contexts. But if you were to
 Tom> look for FROM followed by FIRST/LAST followed by
 Tom> IGNORE/RESPECT/OVER, I think that could only validly happen in
 Tom> this syntax.

No; you need to go four tokens ahead in total, not three. Assuming
nth_value is unreserved, then

  select nth_value(x) from first ignore;

is a valid query that has nth_value(x) as an expression, "first" as a
table name and "ignore" as its alias. Only when you see NULLS after
IGNORE, or OVER after FIRST/LAST, do you know that you're looking at
a window function and not a from clause.

So FROM_LA would have to mean "FROM" followed by any of:

  FIRST IGNORE NULLS
  LAST IGNORE NULLS
  FIRST RESPECT NULLS
  LAST RESPECT NULLS
  FIRST OVER
  LAST OVER

Remember that while OVER is reserved, all of FIRST, LAST, RESPECT and
IGNORE are unreserved.

 Tom> It'd take some work to extend base_yylex to look ahead 2 tokens
 Tom> not one, but I'm sure that could be done. (You'd also need a
 Tom> lookahead rule to match "IGNORE/RESPECT NULLS OVER", but that
 Tom> seems just as doable.) Then the relevant productions use FROM_LA,
 Tom> IGNORE_LA, RESPECT_LA instead of the corresponding bare tokens,
 Tom> and the grammar no longer has an ambiguity problem.

Yeah, but at the cost of having to extend base_yylex to go 3 tokens
ahead (not 2) rather than the current single lookahead slot.

Doable, certainly (probably not much harder to do 3 than 2 actually)

-- 
Andrew (irc:RhodiumToad)

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2018-09-24T00:20:02Z

    Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
>  Tom> If you just think of recognizing FROM FIRST/LAST, you get nowhere
>  Tom> because that's still legal in other contexts. But if you were to
>  Tom> look for FROM followed by FIRST/LAST followed by
>  Tom> IGNORE/RESPECT/OVER, I think that could only validly happen in
>  Tom> this syntax.

> No; you need to go four tokens ahead in total, not three. Assuming
> nth_value is unreserved, then
>   select nth_value(x) from first ignore;
> is a valid query that has nth_value(x) as an expression, "first" as a
> table name and "ignore" as its alias.

No, because once IGNORE is a keyword, even unreserved, it's not legal
as an AS-less alias.  We'd be breaking queries like that no matter what.

(I know there are people around here who'd like to remove that
restriction, but it's not happening anytime soon IMO.)

			regards, tom lane

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

    Andrew Gierth <andrew@tao11.riddles.org.uk> — 2018-09-24T00:26:44Z

    >>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

 >> select nth_value(x) from first ignore;

 Tom> No, because once IGNORE is a keyword, even unreserved, it's not
 Tom> legal as an AS-less alias.

That rule only applies in the select-list, not in the FROM clause; table
aliases in FROM are just ColId, so they can be anything except a fully
reserved or type_func_name keyword.

-- 
Andrew (irc:RhodiumToad)

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

    Andrew Gierth <andrew@tao11.riddles.org.uk> — 2018-09-25T04:16:27Z

    So I've tried to rough out a decision tree for the various options on
how this might be implemented (discarding the "use precedence hacks"
option). Opinions? Additions?

(formatted for emacs outline-mode)

* 1. use lexical lookahead

  +: relatively straightforward parser changes
  +: no new reserved words
  +: has the option of working extensibly with all functions

  -: base_yylex needs extending to 3 lookahead tokens

** 1.1. Allow from/ignore clause on all (or all non-agg) window function calls

  If the clauses are legal on all window functions, what to do about existing
  window functions for which the clauses do not make sense?

*** 1.1.1. Ignore the clause when the function isn't aware of it

  +: simple
  -: somewhat surprising for users perhaps?

*** 1.1.2. Change the behavior of the windowapi in some consistent way

  Not sure if this can work.
  +: fairly simple(maybe?) and predictable
  -: changes the behavior of existing window functions

** 1.2. Allow from/ignore clause on only certain functions

  +: avoids any unexpected behavior
  -: needs some way to control what functions allow it

*** 1.2.1. Check the function name in parse analysis against a fixed list.

  +: simple
  -: not extensible

*** 1.2.2. Provide some option in CREATE FUNCTION

  +: extensible
  -: fairly intrusive, adding stuff to create function and pg_proc

*** 1.2.3. Do something magical with function argument types

  +: doesn't need changes in create function / pg_proc
  -: it's an ugly hack

* 2. reserve nth_value etc. as functions

  +: follows the spec reasonably well
  +: less of a hack than extending base_yylex

  -: new reserved words
  -: more parser rules
  -: not extensible

  (now goto 1.2.1)

* 3. "just say no" to the spec

  e.g. add new functions like lead_ignore_nulls(), or add extra boolean
  args to lead() etc. telling them to skip nulls

  +: simple
  -: doesn't conform to spec
  -: using extra args isn't quite the right semantics

-- 
Andrew (irc:RhodiumToad)

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2018-09-25T14:07:19Z

    Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> So I've tried to rough out a decision tree for the various options on
> how this might be implemented (discarding the "use precedence hacks"
> option). Opinions? Additions?

I think it'd be worth at least drafting an implementation for the
lexical-lookahead fix.  I think it's likely that we'll need to extend
base_yylex to do more lookahead in the future even if we don't do it
for this, given the SQL committee's evident love for COBOL-ish syntax
and lack of regard for what you can do in LALR(1).

The questions of how we interface to the individual window functions
are really independent of how we handle the parsing problem.  My
first inclination is to just pass the flags down to the window functions
(store them in WindowObject and provide some additional inquiry functions
in windowapi.h) and let them deal with it.

>   If the clauses are legal on all window functions, what to do about existing
>   window functions for which the clauses do not make sense?

Option 1: do nothing, document that nothing happens if w.f. doesn't
implement it.

Option 2: record whether the inquiry functions got called.  At end of
query, error out if they weren't and the options were used.

It's also worth wondering if we couldn't just implement the flags in
some generic fashion and not need to involve the window functions at
all.  FROM LAST, for example, could and perhaps should be implemented
by inverting the sort order.  Possibly IGNORE NULLS could be implemented
inside the WinGetFuncArgXXX functions?  These behaviors might or might
not make much sense with other window functions, but that doesn't seem
like it's our problem.

			regards, tom lane

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

    Andrew Gierth <andrew@tao11.riddles.org.uk> — 2018-09-27T04:40:27Z

    >>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

 >> So I've tried to rough out a decision tree for the various options
 >> on how this might be implemented (discarding the "use precedence
 >> hacks" option). Opinions? Additions?

 Tom> I think it'd be worth at least drafting an implementation for the
 Tom> lexical-lookahead fix. I think it's likely that we'll need to
 Tom> extend base_yylex to do more lookahead in the future even if we
 Tom> don't do it for this, given the SQL committee's evident love for
 Tom> COBOL-ish syntax and lack of regard for what you can do in
 Tom> LALR(1).

That's not _quite_ a fair criticism of the SQL committee; they're not
ignoring the capabilities of parsers, they're just not making their
syntax robust in the presence of the kinds of extensions and
generalizations that we want to do.

Without exception that I know of, every time we've run into a problem
that needed ugly precedence rules or extra lookahead it's been caused by
us not reserving something that is reserved in the spec, or by us
allowing constructs that are not in the spec at all (postfix operators,
especially), or by us deliberately generalizing what the spec allows
(e.g. allowing full expressions where the spec only allows column
references, or allowing extra parens around subselects) or where we've
repurposed syntax from the spec in an incompatible way (as in
ANY(array)).

Anyway, for the time being I will mark this patch as "returned with
feedback".

 >> If the clauses are legal on all window functions, what to do about
 >> existing window functions for which the clauses do not make sense?

 Tom> Option 1: do nothing, document that nothing happens if w.f.
 Tom> doesn't implement it.

That was 1.1.1 on my list.

 Tom> Option 2: record whether the inquiry functions got called. At end
 Tom> of query, error out if they weren't and the options were used.

Erroring at the _end_ of the query seems a bit of a potential surprise.

 Tom> It's also worth wondering if we couldn't just implement the flags
 Tom> in some generic fashion and not need to involve the window
 Tom> functions at all.

That was what I meant by option 1.1.2 on my list.

 Tom> FROM LAST, for example, could and perhaps should be implemented by
 Tom> inverting the sort order.

Actually that can't work for reasons brought up in the recent discussion
of optimization of window function sorts: if you change the sort order
you potentially disturb the ordering of peer rows, and the spec requires
that an (nth_value(x,n) from last over w) and (otherfunc(x) over w) for
order-equivalent windows "w" must see the peer rows in the same order.

So FROM LAST really does have to keep the original sort order, and count
backwards from the end of the window.

 Tom> Possibly IGNORE NULLS could be implemented inside the
 Tom> WinGetFuncArgXXX functions? These behaviors might or might not
 Tom> make much sense with other window functions, but that doesn't seem
 Tom> like it's our problem.

That's about what I was thinking for option 1.1.2, yes.

-- 
Andrew (irc:RhodiumToad)

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

    Andrew Gierth <andrew@tao11.riddles.org.uk> — 2018-09-27T04:58:48Z

    >>>>> "Krasiyan" == Krasiyan Andreev <krasiyan@gmail.com> writes:

 Krasiyan> I am using last version from more than two months ago in
 Krasiyan> production environment with real data and I didn't find any
 Krasiyan> bugs, so I'm marking this patch as ready for committer in the
 Krasiyan> commitfest app.

Oliver (or anyone else), do you plan to continue working on this in the
immediate future, in line with the comments from myself and Tom in this
thread? If so I'll bump it to the next CF, otherwise I'll mark it
"returned with feedback".

I'm happy to help out with further work on this patch if needed, time
permitting.

-- 
Andrew (irc:RhodiumToad)

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

    Stephen Frost <sfrost@snowman.net> — 2020-04-30T18:58:55Z

    Greetings,

This seems to have died out, and that's pretty unfortunate because this
is awfully useful SQL standard syntax that people look for and wish we
had.

* Andrew Gierth (andrew@tao11.riddles.org.uk) wrote:
> So I've tried to rough out a decision tree for the various options on
> how this might be implemented (discarding the "use precedence hacks"
> option). Opinions? Additions?
> 
> (formatted for emacs outline-mode)
> 
> * 1. use lexical lookahead
> 
>   +: relatively straightforward parser changes
>   +: no new reserved words
>   +: has the option of working extensibly with all functions
> 
>   -: base_yylex needs extending to 3 lookahead tokens

This sounds awful grotty and challenging to do and get right, and the
alternative (just reserving these, as the spec does) doesn't seem so
draconian as to be that much of an issue.

> * 2. reserve nth_value etc. as functions
> 
>   +: follows the spec reasonably well
>   +: less of a hack than extending base_yylex
> 
>   -: new reserved words
>   -: more parser rules
>   -: not extensible
> 

For my 2c, at least, reserving these strikes me as entirely reasonable.
Yes, it sucks that we have to partially-reserve some additional
keywords, but such is life.  I get that we'll throw syntax errors
sometimes when we might have given a better error, but I think we can
accept that.

>   (now goto 1.2.1)

Hmm, not sure this was right?  but sure, I'll try...

> *** 1.2.1. Check the function name in parse analysis against a fixed list.
> 
>   +: simple
>   -: not extensible

Seems like this is more-or-less required since we'd be reserving them..?

> *** 1.2.2. Provide some option in CREATE FUNCTION
> 
>   +: extensible
>   -: fairly intrusive, adding stuff to create function and pg_proc

How would this work though, if we reserve the functions as keywords..?
Maybe I'm not entirely following, but wouldn't attempts to use other
functions end up with syntax errors in at least some of the cases,
meaning that having other functions support this wouldn't really work?
I don't particularly like the idea that some built-in functions would
always work but others would work but only some of the time.

> *** 1.2.3. Do something magical with function argument types
> 
>   +: doesn't need changes in create function / pg_proc
>   -: it's an ugly hack

Not really a fan of 'ugly hack'.

> * 3. "just say no" to the spec
> 
>   e.g. add new functions like lead_ignore_nulls(), or add extra boolean
>   args to lead() etc. telling them to skip nulls
> 
>   +: simple
>   -: doesn't conform to spec
>   -: using extra args isn't quite the right semantics

Ugh, no thank you.

Thanks!

Stephen

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

    Krasiyan Andreev <krasiyan@gmail.com> — 2020-04-30T19:50:00Z

    Thank you very much for feedback and yes, that is very useful SQL syntax.
Maybe you miss my previous answer, but you are right, that patch is
currently dead,
because some important design questions must be discussed here, before
patch rewriting.

I have dropped support of from first/last for nth_value(),
but also I reimplemented it in a different way,
by using negative number for the position argument, to be able to get the
same frame in exact reverse order.
After that patch becomes much more simple and some concerns about
precedence hack has gone.

I have not renamed special bool type "ignorenulls"
(I know that it is not acceptable way for calling extra version
of window functions, but also it makes things very easy and it can reuse
frames),
but I removed the other special bool type "fromlast".

Attached file was for PostgreSQL 13 (master git branch, last commit fest),
everything was working and patch was at the time in very good shape, all
tests was passed.

I read previous review and suggestions from Tom about special bool type and
unreserved keywords and also,
that IGNORE NULLS could be implemented inside the WinGetFuncArgXXX
functions,
but I am not sure how exactly to proceed (some example will be very
helpful).

На чт, 30.04.2020 г. в 21:58 Stephen Frost <sfrost@snowman.net> написа:

> Greetings,
>
> This seems to have died out, and that's pretty unfortunate because this
> is awfully useful SQL standard syntax that people look for and wish we
> had.
>
> * Andrew Gierth (andrew@tao11.riddles.org.uk) wrote:
> > So I've tried to rough out a decision tree for the various options on
> > how this might be implemented (discarding the "use precedence hacks"
> > option). Opinions? Additions?
> >
> > (formatted for emacs outline-mode)
> >
> > * 1. use lexical lookahead
> >
> >   +: relatively straightforward parser changes
> >   +: no new reserved words
> >   +: has the option of working extensibly with all functions
> >
> >   -: base_yylex needs extending to 3 lookahead tokens
>
> This sounds awful grotty and challenging to do and get right, and the
> alternative (just reserving these, as the spec does) doesn't seem so
> draconian as to be that much of an issue.
>
> > * 2. reserve nth_value etc. as functions
> >
> >   +: follows the spec reasonably well
> >   +: less of a hack than extending base_yylex
> >
> >   -: new reserved words
> >   -: more parser rules
> >   -: not extensible
> >
>
> For my 2c, at least, reserving these strikes me as entirely reasonable.
> Yes, it sucks that we have to partially-reserve some additional
> keywords, but such is life.  I get that we'll throw syntax errors
> sometimes when we might have given a better error, but I think we can
> accept that.
>
> >   (now goto 1.2.1)
>
> Hmm, not sure this was right?  but sure, I'll try...
>
> > *** 1.2.1. Check the function name in parse analysis against a fixed
> list.
> >
> >   +: simple
> >   -: not extensible
>
> Seems like this is more-or-less required since we'd be reserving them..?
>
> > *** 1.2.2. Provide some option in CREATE FUNCTION
> >
> >   +: extensible
> >   -: fairly intrusive, adding stuff to create function and pg_proc
>
> How would this work though, if we reserve the functions as keywords..?
> Maybe I'm not entirely following, but wouldn't attempts to use other
> functions end up with syntax errors in at least some of the cases,
> meaning that having other functions support this wouldn't really work?
> I don't particularly like the idea that some built-in functions would
> always work but others would work but only some of the time.
>
> > *** 1.2.3. Do something magical with function argument types
> >
> >   +: doesn't need changes in create function / pg_proc
> >   -: it's an ugly hack
>
> Not really a fan of 'ugly hack'.
>
> > * 3. "just say no" to the spec
> >
> >   e.g. add new functions like lead_ignore_nulls(), or add extra boolean
> >   args to lead() etc. telling them to skip nulls
> >
> >   +: simple
> >   -: doesn't conform to spec
> >   -: using extra args isn't quite the right semantics
>
> Ugh, no thank you.
>
> Thanks!
>
> Stephen
>

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

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2023-04-22T12:14:43Z

    I revisited the thread:
https://www.postgresql.org/message-id/flat/CAGMVOdsbtRwE_4%2Bv8zjH1d9xfovDeQAGLkP_B6k69_VoFEgX-A%40mail.gmail.com

and came up with attached POC patch (I used some varibale names
appearing in the Krasiyan Andreev's patch). I really love to have
RESPECT/IGNORE NULLS because I believe they are convenient for
users. For FIRST/LAST I am not so excited since there are alternatives
as our document stats, so FIRST/LAST are not included in the patch.

Currently in the patch only nth_value is allowed to use RESPECT/IGNORE
NULLS. I think it's not hard to implement it for others (lead, lag,
first_value and last_value).  No document nor test patches are
included for now.

Note that RESPECT/IGNORE are not registered as reserved keywords in
this patch (but registered as unreserved keywords). I am not sure if
this is acceptable or not.

> The questions of how we interface to the individual window functions
> are really independent of how we handle the parsing problem.  My
> first inclination is to just pass the flags down to the window functions
> (store them in WindowObject and provide some additional inquiry functions
> in windowapi.h) and let them deal with it.

I agree with this.  Also I do not change the prototype of
nth_value. So I pass RESPECT/IGNORE NULLS information from the raw
parser to parse/analysis and finally to WindowObject.

> It's also worth wondering if we couldn't just implement the flags in
> some generic fashion and not need to involve the window functions at
> all.  FROM LAST, for example, could and perhaps should be implemented
> by inverting the sort order.  Possibly IGNORE NULLS could be implemented
> inside the WinGetFuncArgXXX functions?  These behaviors might or might
> not make much sense with other window functions, but that doesn't seem
> like it's our problem.

Yes, probably we could make WinGetFuncArgXXX a little bit smarter in
this direction (not implemented in the patch at this point).

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Oliver Ford <ojford@gmail.com> — 2023-04-22T12:38:50Z

    On Sat, 22 Apr 2023, 13:14 Tatsuo Ishii, <ishii@sraoss.co.jp> wrote:

> I revisited the thread:
>
> https://www.postgresql.org/message-id/flat/CAGMVOdsbtRwE_4%2Bv8zjH1d9xfovDeQAGLkP_B6k69_VoFEgX-A%40mail.gmail.com
>
> and came up with attached POC patch (I used some varibale names
> appearing in the Krasiyan Andreev's patch). I really love to have
> RESPECT/IGNORE NULLS because I believe they are convenient for
> users. For FIRST/LAST I am not so excited since there are alternatives
> as our document stats, so FIRST/LAST are not included in the patch.
>
> Currently in the patch only nth_value is allowed to use RESPECT/IGNORE
> NULLS. I think it's not hard to implement it for others (lead, lag,
> first_value and last_value).  No document nor test patches are
> included for now.
>

I've actually recently been looking at this feature again recently as well.
One thing I wondered, but would need consensus, is to take the
SEEK_HEAD/SEEK_TAIL case statements out of WinGetFuncArgInPartition. This
function is only called by leadlag_common, which uses SEEK_CURRENT, so
those case statements are never reached. Taking them out simplifies the
code as it is but means future features might need it re-added (although
I'm not sure the use case for it, as that function is for window funcs that
ignore the frame options).


> Note that RESPECT/IGNORE are not registered as reserved keywords in
> this patch (but registered as unreserved keywords). I am not sure if
> this is acceptable or not.
>
> > The questions of how we interface to the individual window functions
> > are really independent of how we handle the parsing problem.  My
> > first inclination is to just pass the flags down to the window functions
> > (store them in WindowObject and provide some additional inquiry functions
> > in windowapi.h) and let them deal with it.

I agree with this.  Also I do not change the prototype of
> nth_value. So I pass RESPECT/IGNORE NULLS information from the raw
> parser to parse/analysis and finally to WindowObject.
>

This is a much better option than my older patch which needed to change the
functions.


> > It's also worth wondering if we couldn't just implement the flags in
> > some generic fashion and not need to involve the window functions at
> > all.  FROM LAST, for example, could and perhaps should be implemented
> > by inverting the sort order.  Possibly IGNORE NULLS could be implemented
> > inside the WinGetFuncArgXXX functions?  These behaviors might or might
> > not make much sense with other window functions, but that doesn't seem
> > like it's our problem.
>
> Yes, probably we could make WinGetFuncArgXXX a little bit smarter in
> this direction (not implemented in the patch at this point).
>

+1 for doing it here. Maybe also refactor WinGetFuncArgInFrame, putting the
exclusion checks in a static function as that function is already pretty
big?


> Best reagards,
> --
> Tatsuo Ishii
> SRA OSS LLC
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
>

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

    Vik Fearing <vik@postgresfriends.org> — 2023-04-22T17:27:06Z

    On 4/22/23 14:14, Tatsuo Ishii wrote:
> I revisited the thread:
> https://www.postgresql.org/message-id/flat/CAGMVOdsbtRwE_4%2Bv8zjH1d9xfovDeQAGLkP_B6k69_VoFEgX-A%40mail.gmail.com
> 
> and came up with attached POC patch (I used some varibale names
> appearing in the Krasiyan Andreev's patch). I really love to have
> RESPECT/IGNORE NULLS because I believe they are convenient for
> users.

Excellent.  I was thinking about picking my version of this patch up 
again, but I think this might be better than mine.

I am curious why set_mark is false in the IGNORE version instead of also 
being const_offset.  Surely the nth non-null in the frame will never go 
backwards.

Dealing with marks was the main reason (I think) that my patch was not 
accepted.

> For FIRST/LAST I am not so excited since there are alternatives
> as our document stats,

I disagree with this.  The point of having FROM LAST is to avoid 
calculating a new window and running a new pass over it.

 > so FIRST/LAST are not included in the patch.

I do agree that we can have <null treatment> without <from first or 
last> so let's move forward with this and handle the latter later.

> Currently in the patch only nth_value is allowed to use RESPECT/IGNORE
> NULLS.

This should not be hard coded.  It should be a new field in pg_proc 
(with a sanity check that it is only true for window functions).  That 
way custom window functions can implement it.

> I think it's not hard to implement it for others (lead, lag,
> first_value and last_value).

It doesn't seem like it should be, no.

> No document nor test patches are included for now.

I can volunteer to work on these if you want.

> Note that RESPECT/IGNORE are not registered as reserved keywords in
> this patch (but registered as unreserved keywords). I am not sure if
> this is acceptable or not.

For me, this is perfectly okay.  Keep them at the lowest level of 
reservation as possible.
-- 
Vik Fearing

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-04-22T17:52:55Z

    Vik Fearing <vik@postgresfriends.org> writes:
> On 4/22/23 14:14, Tatsuo Ishii wrote:
>> Note that RESPECT/IGNORE are not registered as reserved keywords in
>> this patch (but registered as unreserved keywords). I am not sure if
>> this is acceptable or not.

> For me, this is perfectly okay.  Keep them at the lowest level of 
> reservation as possible.

Yeah, keep them unreserved if at all possible.  Any higher reservation
level risks breaking existing applications that might be using these
words as column or function names.

			regards, tom lane

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

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2023-04-23T01:24:02Z

    > Excellent.  I was thinking about picking my version of this patch up
> again, but I think this might be better than mine.

Thanks.

> I am curious why set_mark is false in the IGNORE version instead of
> also being const_offset.  Surely the nth non-null in the frame will
> never go backwards.

Initially I thought that too. But when I used const_offset instead of
false. I got an error:

ERROR:  cannot fetch row before WindowObject's mark position

> I do agree that we can have <null treatment> without <from first or
> last> so let's move forward with this and handle the latter later.

Agreed.

>> Currently in the patch only nth_value is allowed to use RESPECT/IGNORE
>> NULLS.
>
> This should not be hard coded.  It should be a new field in pg_proc
> (with a sanity check that it is only true for window functions).  That
> way custom window functions can implement it.

There were some discussions on this in the past.
https://www.postgresql.org/message-id/flat/CAGMVOdsbtRwE_4%2Bv8zjH1d9xfovDeQAGLkP_B6k69_VoFEgX-A%40mail.gmail.com

It seems Tom and Andrew thought that "1.1.2. Change the behavior of
the windowapi in some consistent way" is ambitious. If we follow this
direction, I think each window function should check WindowFunc struct
passed by WinGetWindowFunc (added in my patch) to check whether IGNORE
NULLS can be applied or not in the function. If not, error out. This
way, we don't need to add a new field to pg_proc.

>> No document nor test patches are included for now.
> 
> I can volunteer to work on these if you want.

Thanks! I think you can work on top of the last patch posted by Krasiyan Andreev:
https://www.postgresql.org/message-id/CAN1PwonAnC-KkRyY%2BDtRmxQ8rjdJw%2BgcOsHruLr6EnF7zSMH%3DQ%40mail.gmail.com

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2023-04-23T03:29:24Z

    > Vik Fearing <vik@postgresfriends.org> writes:
>> On 4/22/23 14:14, Tatsuo Ishii wrote:
>>> Note that RESPECT/IGNORE are not registered as reserved keywords in
>>> this patch (but registered as unreserved keywords). I am not sure if
>>> this is acceptable or not.
> 
>> For me, this is perfectly okay.  Keep them at the lowest level of 
>> reservation as possible.
> 
> Yeah, keep them unreserved if at all possible.  Any higher reservation
> level risks breaking existing applications that might be using these
> words as column or function names.

Agreed.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Oliver Ford <ojford@gmail.com> — 2023-05-01T11:57:38Z

    On Sun, Apr 23, 2023 at 4:29 AM Tatsuo Ishii <ishii@sraoss.co.jp> wrote:

> > Vik Fearing <vik@postgresfriends.org> writes:
> >
> >> For me, this is perfectly okay.  Keep them at the lowest level of
> >> reservation as possible.
> >
> > Yeah, keep them unreserved if at all possible.  Any higher reservation
> > level risks breaking existing applications that might be using these
> > words as column or function names.
>
> Agreed.
> <http://www.sraoss.co.jp>

  <http://www.sraoss.co.jp>
Attached is a new version of the code and tests to implement this. There's
now no modification to windowfuncs.c or the catalog,
it's only a bool added to FuncCall which if set to true, ignores nulls. It
adds IGNORE/RESPECT at the Unreserved, As Label level.

The implementation also aims at better performance over previous versions
by not disabling set_mark, and using an array to
track previous non-null positions in SEEK_HEAD or SEEK_CURRENT with Forward
(lead, but not lag). The mark is set if a row
is out of frame and further rows can't be in frame (to ensure it works with
an exclusion clause).

The attached test patch is mostly the same as in the previous patch
set, but it doesn't fail on row_number anymore as the main patch
only rejects aggregate functions. The test patch also adds a test for
EXCLUDE CURRENT ROW and for two contiguous null rows.

I've not yet tested custom window functions with the patch, but I'm happy
to add them to the test patch in v2 if we want to go this way
in implementing this feature.

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

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2023-05-02T01:03:52Z

    > The attached test patch is mostly the same as in the previous patch
> set, but it doesn't fail on row_number anymore as the main patch
> only rejects aggregate functions. The test patch also adds a test for

> +SELECT sum(orbit) RESPECT NULLS OVER () FROM planets; -- succeeds

I think the standard does not allow to specify RESPECT NULLS other
than lead, lag, first_value, last_value and nth_value. Unless we agree
that PostgreSQL violates the standard in this regard, you should not
allow to use RESPECT NULLS for the window functions, expect lead etc.
and aggregates.

See my patch.

> +/*
> + * Window function option clauses
> + */
> +opt_null_treatment:
> +			RESPECT NULLS_P							{ $$ = RESPECT_NULLS; }
> +			| IGNORE_P NULLS_P						{ $$ = IGNORE_NULLS; }
> +			| /*EMPTY*/								{ $$ = NULL_TREATMENT_NOT_SET; }
> +		;

With this, you can check if null treatment clause is used or not in
each window function.

In my previous patch I did the check in parse/analysis but I think
it's better to be checked in each window function. This way,

- need not to add a column to pg_proc.

- allow user defined window functions to decide by themselves whether
  they can accept null treatment option.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2023-05-06T03:57:25Z

    >> The attached test patch is mostly the same as in the previous patch
>> set, but it doesn't fail on row_number anymore as the main patch
>> only rejects aggregate functions. The test patch also adds a test for
> 
>> +SELECT sum(orbit) RESPECT NULLS OVER () FROM planets; -- succeeds
> 
> I think the standard does not allow to specify RESPECT NULLS other
> than lead, lag, first_value, last_value and nth_value. Unless we agree
> that PostgreSQL violates the standard in this regard, you should not
> allow to use RESPECT NULLS for the window functions, expect lead etc.
> and aggregates.
> 
> See my patch.
> 
>> +/*
>> + * Window function option clauses
>> + */
>> +opt_null_treatment:
>> +			RESPECT NULLS_P							{ $$ = RESPECT_NULLS; }
>> +			| IGNORE_P NULLS_P						{ $$ = IGNORE_NULLS; }
>> +			| /*EMPTY*/								{ $$ = NULL_TREATMENT_NOT_SET; }
>> +		;
> 
> With this, you can check if null treatment clause is used or not in
> each window function.
> 
> In my previous patch I did the check in parse/analysis but I think
> it's better to be checked in each window function. This way,
> 
> - need not to add a column to pg_proc.
> 
> - allow user defined window functions to decide by themselves whether
>   they can accept null treatment option.

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

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Oliver Ford <ojford@gmail.com> — 2023-05-06T08:41:49Z

    On Sat, 6 May 2023, 04:57 Tatsuo Ishii, <ishii@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).

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

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2023-05-06T09:35:34Z

    > 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.

I have read the mail. In my understanding nobody said that standard
window functions should all accept the null treatment clause.

Also Tom said:
https://www.postgresql.org/message-id/5567.1537884439%40sss.pgh.pa.us
> The questions of how we interface to the individual window functions
> are really independent of how we handle the parsing problem.  My
> first inclination is to just pass the flags down to the window functions
> (store them in WindowObject and provide some additional inquiry functions
> in windowapi.h) and let them deal with it.

As I said before I totally agree with this. With my patch if a
(custom) window function does not want to accept null treatment
clause, it just calls ErrorOutNullTreatment(). It will raise an error
if IGNORE NULLS or RESPECT NULLS is provided. If it does call the
function, it is up to the function how to deal with the null
treatment. In another word, the infrastructure does not have fixed
rules to allow/disallow null treatment clause for each window
function. It's "delegated" to each window function.

Anyway we can change the rule for other than nth_value etc. later
easily once my patch is brought in.

> 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).

I am not sure if allowing-multiple-column-arguments patch should be
provided with null-treatment patch.

> 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).

Let's see the discussion...

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Oliver Ford <ojford@gmail.com> — 2024-09-07T20:25:28Z

    On Sat, May 6, 2023 at 9:41 AM Oliver Ford <ojford@gmail.com> wrote:
>
>
>
> On Sat, 6 May 2023, 04:57 Tatsuo Ishii, <ishii@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.

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

    Vik Fearing <vik@postgresfriends.org> — 2024-09-08T13:22:13Z

    On 9/7/24 22:25, Oliver Ford wrote:
> On Sat, May 6, 2023 at 9:41 AM Oliver Ford <ojford@gmail.com> wrote:
>>
>>
>>
>> On Sat, 6 May 2023, 04:57 Tatsuo Ishii, <ishii@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

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

    Oliver Ford <ojford@gmail.com> — 2024-09-09T09:49:11Z

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

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

    Tatsuo Ishii <ishii@postgresql.org> — 2024-09-12T01:41:07Z

    >> >> On Sat, 6 May 2023, 04:57 Tatsuo Ishii, <ishii@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).

It seems you allow to use IGNORE NULLS for all window functions. If
the case, you should explicitely stat that in the docs. Otherwise
users will be confused because;

1) The SQL standard says IGNORE NULLS only for lead, lag, first_value,
last_value and nth_value.

2) Some window function returns same rows with IGNORE NULLS/RESPECT
NULLS. Consider following case.

test=# create table t1(i int);
CREATE TABLE
test=# insert into t1 values(NULL),(NULL);
INSERT 0 2
test=# select * from t1;
 i 
---
  
  
(2 rows)

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.

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.

I take a look at the patch and noticed that following functions have
no comments on what they are doing and what are the arguments. Please
look into other functions in nodeWindowAgg.c and add appropriate
comments to those functions.

+static void increment_notnulls(WindowObject winobj, int64 pos)

+static Datum ignorenulls_getfuncarginpartition(WindowObject winobj, int argno,
+						int relpos, int seektype, bool set_mark, bool *isnull, bool *isout) {

+static Datum ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
+						int relpos, int seektype, bool set_mark, bool *isnull, bool *isout) {

Also the coding style does not fit into our coding standard. They should be written something like:

static void
increment_notnulls(WindowObject winobj, int64 pos)

static Datum
ignorenulls_getfuncarginpartition(WindowObject winobj, int argno,
						int relpos, int seektype, bool set_mark, bool *isnull, bool *isout)
{

static Datum
ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
						int relpos, int seektype, bool set_mark, bool *isnull, bool *isout)
{

See also:
https://www.postgresql.org/docs/current/source-format.html

+	int			ignore_nulls;	/* ignore nulls */

You should add more comment here. I.e. what values are possible for
ignore_nulls.

I also notice that you have an array in memory which records non-null
row positions in a partition. The position is represented in int64,
which means 1 entry consumes 8 bytes. If my understanding is correct,
the array continues to grow up to the partition size. Also the array
is created for each window function (is it really necessary?). I worry
about this because it might consume excessive memory for big
partitions.

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    David G. Johnston <david.g.johnston@gmail.com> — 2024-09-12T02:11:35Z

    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.

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

    Tatsuo Ishii <ishii@postgresql.org> — 2024-09-12T02:30:48Z

    > 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…).

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

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

    Oliver Ford <ojford@gmail.com> — 2025-01-17T16:43:29Z

    On Thu, Sep 12, 2024 at 2:41 AM Tatsuo Ishii <ishii@postgresql.org> wrote:
>
> It seems you allow to use IGNORE NULLS for all window functions. If
> the case, you should explicitely stat that in the docs. Otherwise
> users will be confused because;

The latest version restricts it to lag, lead, first_value, last_value,
and nth_value. We can extend it in a subsequent patch if there's
demand?

> I take a look at the patch and noticed that following functions have
> no comments on what they are doing and what are the arguments. Please
> look into other functions in nodeWindowAgg.c and add appropriate
> comments to those functions.

Latest version has more comments and should be in the standard coding style.

> I also notice that you have an array in memory which records non-null
> row positions in a partition. The position is represented in int64,
> which means 1 entry consumes 8 bytes. If my understanding is correct,
> the array continues to grow up to the partition size. Also the array
> is created for each window function (is it really necessary?). I worry
> about this because it might consume excessive memory for big
> partitions.

It's an int64 because it stores the abs_pos/mark_pos which are int64.
Keeping an array for each function is needed for the mark optimization
to work correctly.

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-01-19T23:55:17Z

    Thanks for updating the patch.

>> It seems you allow to use IGNORE NULLS for all window functions. If
>> the case, you should explicitely stat that in the docs. Otherwise
>> users will be confused because;
> 
> The latest version restricts it to lag, lead, first_value, last_value,
> and nth_value. We can extend it in a subsequent patch if there's
> demand?

The restriction is required by the SQL standard. So I don't think we
need to extend to other window functions.

>> I take a look at the patch and noticed that following functions have
>> no comments on what they are doing and what are the arguments. Please
>> look into other functions in nodeWindowAgg.c and add appropriate
>> comments to those functions.
> 
> Latest version has more comments and should be in the standard coding style.

Still I see non standard coding stiles and indentations. See attached
patch for nodeWindowAgg.c, which is fixed by pgindent, for
example. (Other files may need fixing too).

>> I also notice that you have an array in memory which records non-null
>> row positions in a partition. The position is represented in int64,
>> which means 1 entry consumes 8 bytes. If my understanding is correct,
>> the array continues to grow up to the partition size. Also the array
>> is created for each window function (is it really necessary?). I worry
>> about this because it might consume excessive memory for big
>> partitions.
> 
> It's an int64 because it stores the abs_pos/mark_pos which are int64.
> Keeping an array for each function is needed for the mark optimization
> to work correctly.

Ok.

Here are some comments regarding the patch:

(1) I noticed that ignorenulls_getfuncarginframe() does not take
account EXCLUSION frame options. The code path is in
WinGetFuncArgInFrame():

			/*
			 * Account for exclusion option if one is active, but advance only
			 * abs_pos not mark_pos.  This prevents changes of the current
			 * row's peer group from resulting in trying to fetch a row before
			 * some previous mark position.
:
:

I guess ignorenulls_getfuncarginframe() was created by modifying
WinGetFuncArgInFrame() so I don't see the reason why
ignorenulls_getfuncarginframe() does not take account EXCLUSION frame
options.

(2) New member ignore_nulls are added to some structs. Its value is 0,
1 or -1. It's better to use a DEFINE for the value of ignore_nulls,
rather than 0, 1, or -1.

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-01-20T00:03:29Z

    Tatsuo Ishii <ishii@postgresql.org> writes:
>> The latest version restricts it to lag, lead, first_value, last_value,
>> and nth_value. We can extend it in a subsequent patch if there's
>> demand?

> The restriction is required by the SQL standard. So I don't think we
> need to extend to other window functions.

The SQL spec does not believe that user-defined window functions are a
thing.  So its opinion on this point is useless.  I would think that
IGNORE NULLS is potentially useful for user-defined window functions,
and we should not be building anything that restricts the feature to
specific functions.

			regards, tom lane

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-01-20T00:18:25Z

    > Tatsuo Ishii <ishii@postgresql.org> writes:
>>> The latest version restricts it to lag, lead, first_value, last_value,
>>> and nth_value. We can extend it in a subsequent patch if there's
>>> demand?
> 
>> The restriction is required by the SQL standard. So I don't think we
>> need to extend to other window functions.
> 
> The SQL spec does not believe that user-defined window functions are a
> thing.  So its opinion on this point is useless.

Of course the standard does not mention anything about the user
defined window functions and the restriction is not apply to the user
defined window functions.

> I would think that
> IGNORE NULLS is potentially useful for user-defined window functions,
> and we should not be building anything that restricts the feature to
> specific functions.

So you want to allow to use IGNORE NULLS to other built-in window
functions?
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-01-20T00:31:34Z

    Tatsuo Ishii <ishii@postgresql.org> writes:
>> I would think that
>> IGNORE NULLS is potentially useful for user-defined window functions,
>> and we should not be building anything that restricts the feature to
>> specific functions.

> So you want to allow to use IGNORE NULLS to other built-in window
> functions?

No, there needs to be a way for the individual window function to
throw error if that's specified for a function that can't handle it.
I'm just saying I don't want that to be hard-wired in some centralized
spot.

			regards, tom lane

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-01-20T00:36:44Z

    >> So you want to allow to use IGNORE NULLS to other built-in window
>> functions?
> 
> No, there needs to be a way for the individual window function to
> throw error if that's specified for a function that can't handle it.
> I'm just saying I don't want that to be hard-wired in some centralized
> spot.

I agree. That's the right direction.
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Oliver Ford <ojford@gmail.com> — 2025-01-20T19:09:38Z

    On Mon, Jan 20, 2025 at 12:31 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Tatsuo Ishii <ishii@postgresql.org> writes:
> >> I would think that
> >> IGNORE NULLS is potentially useful for user-defined window functions,
> >> and we should not be building anything that restricts the feature to
> >> specific functions.
>
> > So you want to allow to use IGNORE NULLS to other built-in window
> > functions?
>
> No, there needs to be a way for the individual window function to
> throw error if that's specified for a function that can't handle it.
> I'm just saying I don't want that to be hard-wired in some centralized
> spot.

Would it be acceptable to add a bool column to pg_proc, say
"pronulltreatment"? It would default to false, and an error would be
thrown if the null clause is specified for a function where it's set
to false?

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-01-21T01:20:53Z

    >> No, there needs to be a way for the individual window function to
>> throw error if that's specified for a function that can't handle it.
>> I'm just saying I don't want that to be hard-wired in some centralized
>> spot.
> 
> Would it be acceptable to add a bool column to pg_proc, say
> "pronulltreatment"? It would default to false, and an error would be
> thrown if the null clause is specified for a function where it's set
> to false?

It needs lots of work including modifying CREATE FUNCTION
command. Instead you could add an API to WinObject access functions to
export ignore_nulls value. Then let each window function check it. If
the window function should not take IGNORE/RESPECT NULLS option, throw
an error.

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-01-21T01:44:07Z

    Tatsuo Ishii <ishii@postgresql.org> writes:
> It needs lots of work including modifying CREATE FUNCTION
> command. Instead you could add an API to WinObject access functions to
> export ignore_nulls value. Then let each window function check it. If
> the window function should not take IGNORE/RESPECT NULLS option, throw
> an error.

Yeah, that would be my first thought too.  The only question is
whether a function that fails to check that could crash.  If it
merely gives surprising answers, I think this way is fine.

			regards, tom lane

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

    Oliver Ford <ojford@gmail.com> — 2025-01-21T21:14:55Z

    On Tue, Jan 21, 2025 at 1:21 AM Tatsuo Ishii <ishii@postgresql.org> wrote:
> It needs lots of work including modifying CREATE FUNCTION
> command. Instead you could add an API to WinObject access functions to
> export ignore_nulls value. Then let each window function check it. If
> the window function should not take IGNORE/RESPECT NULLS option, throw
> an error.

Attached version moves the setting of IGNORE_NULLS to the window
function itself, with the functions that don't allow it erroring out.
This is done with a new api: WinCheckAndInitializeNullTreatment.

Custom functions that don't call this will simply not have the
IGNORE_NULLS option set as this api initializes the option and the
array. As per the previous discussion, it should have correct
formatting and handle the Exclusion clauses correctly.

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-01-22T11:58:18Z

    > Attached version moves the setting of IGNORE_NULLS to the window
> function itself, with the functions that don't allow it erroring out.
> This is done with a new api: WinCheckAndInitializeNullTreatment.
> 
> Custom functions that don't call this will simply not have the
> IGNORE_NULLS option set as this api initializes the option and the
> array. As per the previous discussion, it should have correct
> formatting and handle the Exclusion clauses correctly.

I played with the v4 patch. It seems lead() produces incorrect result:

test=# SELECT x,y,lead(y) IGNORE NULLS OVER (ORDER BY x) FROM (VALUES(1,NULL),(2,2),(3,NULL)) AS v(x,y);
 x | y | lead 
---+---+------
 1 |   |    2
 2 | 2 |    2
 3 |   |    2
(3 rows)

I think correct result of "lead" column is 2, NULL, NULL.

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-01-23T04:25:02Z

    > Hello,
> I also played with the v4 patch and it produces correct result:
> test=# SELECT x,y,lead(y) IGNORE NULLS OVER (ORDER BY x) FROM
> (VALUES(1,NULL),(2,2),(3,NULL)) AS v(x,y);
>  x | y | lead
> ---+---+------
>  1 |   |    2
>  2 | 2 |
>  3 |   |
> (3 rows)
> 
> test=#
> It is from today's git, clean compile and install with only v4 patch
> applied, make check also passes without errors.

I guess you are just lucky. In my case I enabled --enable-cassert to
build PostgreSQL and it automatically turn on CLOBBER_FREED_MEMORY and
freed memory area is scrambled. If I look the patch closer, I found a
problem:

+void
+WinCheckAndInitializeNullTreatment(WindowObject winobj,
:
:
+		winobj->win_nonnulls = palloc_array(int64, 16);

WinCheckAndInitializeNullTreatment is called in each built-in window
function. Window functions are called in the per tuple memory context,
which means win_nonnulls disappears when next tuple is supplied to the
window function. If my understanding is correct, winobj->win_nonnulls
needs to survive across processing tuples.

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Krasiyan Andreev <krasiyan@gmail.com> — 2025-01-23T06:08:39Z

    Hi,
I was able to reproduce exactly the problem, with clean compile
and --enable-cassert:
test=# SELECT x,y,lead(y) IGNORE NULLS OVER (ORDER BY x) FROM
(VALUES(1,NULL),(2,2),(3,NULL)) AS v(x,y);
 x | y | lead
---+---+------
 1 |   |    2
 2 | 2 |    2
 3 |   |    2
(3 rows)

test=#
Also, make check errors out at window test (without --enable-cassert it was
passed in previous compile):
krasiyan@fedora:~/pgsql-src/postgresql$ cat
/home/krasiyan/pgsql-src/postgresql/src/test/regress/regression.diffs
diff -U3
/home/krasiyan/pgsql-src/postgresql/src/test/regress/expected/window.out
/home/krasiyan/pgsql-src/postgresql/src/test/regress/results/window.out
---
/home/krasiyan/pgsql-src/postgresql/src/test/regress/expected/window.out
2025-01-22 21:25:47.114508215 +0200
+++ /home/krasiyan/pgsql-src/postgresql/src/test/regress/results/window.out
2025-01-23 07:58:26.784659592 +0200
@@ -5477,12 +5477,12 @@
   name   | orbit | lead  | lead_respect | lead_ignore
 ---------+-------+-------+--------------+-------------
  earth   |       |  4332 |         4332 |        4332
- jupiter |  4332 |       |              |          88
+ jupiter |  4332 |       |              |
  mars    |       |    88 |           88 |          88
  mercury |    88 | 60182 |        60182 |       60182
  neptune | 60182 | 90560 |        90560 |       90560
  pluto   | 90560 | 24491 |        24491 |       24491
- saturn  | 24491 |       |              |         224
+ saturn  | 24491 |       |              |
  uranus  |       |   224 |          224 |         224
  venus   |   224 |       |              |
  xyzzy   |       |       |              |
@@ -5577,13 +5577,13 @@
   name   | orbit | first_value | last_value | nth_value | lead_ignore |
lag_ignore
 ---------+-------+-------------+------------+-----------+-------------+------------
  earth   |       |        4332 |       4332 |           |        4332 |

- jupiter |  4332 |          88 |         88 |           |          88 |

- mars    |       |        4332 |      60182 |        88 |          88 |
    4332
- mercury |    88 |        4332 |      90560 |     60182 |       60182 |
    4332
+ jupiter |  4332 |          88 |         88 |           |       60182 |

+ mars    |       |          88 |      60182 |     60182 |       60182 |
    4332
+ mercury |    88 |        4332 |      90560 |     90560 |       90560 |
    4332
  neptune | 60182 |          88 |      24491 |     90560 |       90560 |
      88
- pluto   | 90560 |          88 |      24491 |     60182 |       24491 |
   60182
- saturn  | 24491 |       60182 |        224 |     90560 |         224 |
   90560
- uranus  |       |       90560 |        224 |     24491 |         224 |
   24491
+ pluto   | 90560 |          88 |      24491 |     60182 |       60182 |
   60182
+ saturn  | 24491 |       60182 |        224 |     90560 |       90560 |
   90560
+ uranus  |       |       90560 |        224 |     24491 |       24491 |
   24491
  venus   |   224 |       24491 |      24491 |           |             |
   24491
  xyzzy   |       |         224 |        224 |           |             |
     224
 (10 rows)
@@ -5646,14 +5646,14 @@
   name   | orbit | first_value | last_value | nth_value | lead_ignore |
lag_ignore
 ---------+-------+-------------+------------+-----------+-------------+------------
  earth   |       |             |            |           |          88 |

- jupiter |       |          88 |         88 |           |          88 |

- mars    |       |          88 |      60182 |     60182 |          88 |

+ jupiter |       |          88 |         88 |           |       60182 |

+ mars    |       |          88 |      60182 |     60182 |       60182 |

  mercury |    88 |          88 |      90560 |     60182 |       60182 |

- neptune | 60182 |          88 |      24491 |     60182 |       90560 |
      88
- pluto   | 90560 |          88 |      24491 |     60182 |       24491 |
   60182
- saturn  | 24491 |       60182 |        224 |     90560 |         224 |
   90560
- uranus  |       |       90560 |        224 |     24491 |         224 |
   24491
- venus   |   224 |       24491 |        224 |       224 |             |
   24491
+ neptune | 60182 |          88 |      24491 |     60182 |       60182 |
      88
+ pluto   | 90560 |          88 |      24491 |     60182 |       60182 |
   60182
+ saturn  | 24491 |       60182 |        224 |     90560 |       90560 |
   90560
+ uranus  |       |       90560 |        224 |     24491 |       24491 |
   24491
+ venus   |   224 |       24491 |        224 |       224 |         224 |
   24491
  xyzzy   |       |         224 |        224 |           |             |
     224
 (10 rows)


На чт, 23.01.2025 г. в 6:25 Tatsuo Ishii <ishii@postgresql.org> написа:

> > Hello,
> > I also played with the v4 patch and it produces correct result:
> > test=# SELECT x,y,lead(y) IGNORE NULLS OVER (ORDER BY x) FROM
> > (VALUES(1,NULL),(2,2),(3,NULL)) AS v(x,y);
> >  x | y | lead
> > ---+---+------
> >  1 |   |    2
> >  2 | 2 |
> >  3 |   |
> > (3 rows)
> >
> > test=#
> > It is from today's git, clean compile and install with only v4 patch
> > applied, make check also passes without errors.
>
> I guess you are just lucky. In my case I enabled --enable-cassert to
> build PostgreSQL and it automatically turn on CLOBBER_FREED_MEMORY and
> freed memory area is scrambled. If I look the patch closer, I found a
> problem:
>
> +void
> +WinCheckAndInitializeNullTreatment(WindowObject winobj,
> :
> :
> +               winobj->win_nonnulls = palloc_array(int64, 16);
>
> WinCheckAndInitializeNullTreatment is called in each built-in window
> function. Window functions are called in the per tuple memory context,
> which means win_nonnulls disappears when next tuple is supplied to the
> window function. If my understanding is correct, winobj->win_nonnulls
> needs to survive across processing tuples.
>
> Best reagards,
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
>

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-01-23T06:27:41Z

    > Hi,
> I was able to reproduce exactly the problem, with clean compile
> and --enable-cassert:
> test=# SELECT x,y,lead(y) IGNORE NULLS OVER (ORDER BY x) FROM
> (VALUES(1,NULL),(2,2),(3,NULL)) AS v(x,y);
>  x | y | lead
> ---+---+------
>  1 |   |    2
>  2 | 2 |    2
>  3 |   |    2
> (3 rows)
> 
> test=#
> Also, make check errors out at window test (without --enable-cassert it was
> passed in previous compile):

Yeah, same here.

Another possible problem is, probably the code does not work well if
there are multiple partitions. Since win_nonnulls stores currentpos in
a partition, when the partition ends, win_nonnulls needs to be
reset. Otherwise, it mistakenly represents currentpos in the previous
partition.

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Oliver Ford <ojford@gmail.com> — 2025-01-24T08:27:09Z

    On Thu, Jan 23, 2025 at 6:27 AM Tatsuo Ishii <ishii@postgresql.org> wrote:
>
> Another possible problem is, probably the code does not work well if
> there are multiple partitions. Since win_nonnulls stores currentpos in
> a partition, when the partition ends, win_nonnulls needs to be
> reset. Otherwise, it mistakenly represents currentpos in the previous
> partition.
>
The attached patch should fix both of these. I've added extra tests
with a PARTITION BY in the window clause to test for multiple
partitions.

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-01-27T11:51:15Z

    > The attached patch should fix both of these. I've added extra tests
> with a PARTITION BY in the window clause to test for multiple
> partitions.

I have looked through the v5 patch. Here are review comments.

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

    Oliver Ford <ojford@gmail.com> — 2025-01-27T16:19:30Z

    On Mon, Jan 27, 2025 at 11:51 AM Tatsuo Ishii <ishii@postgresql.org> wrote:
> I have looked through the v5 patch. Here are review comments.

New version attached.

> @@ -69,6 +69,10 @@ typedef struct WindowObjectData
>         int                     readptr;                /* tuplestore read pointer for this fn */
>         int64           markpos;                /* row that markptr is positioned on */
>         int64           seekpos;                /* row that readptr is positioned on */
> +       int                     ignore_nulls;   /* ignore nulls */
> +       int64      *win_nonnulls;       /* tracks non-nulls in ignore nulls mode */
>
> After ignore_nulls, there will be a 4-byte hole because win_nonnulls
> is an 8-byte variable. It would be better to swap them.

Done.

> @@ -1263,6 +1268,15 @@ begin_partition(WindowAggState *winstate)
>
>                         winobj->markpos = -1;
>                         winobj->seekpos = -1;
> +
> +
> +                       /* reallocate null check */
> +                       if (perfuncstate->winobj->ignore_nulls == IGNORE_NULLS)
> +                       {
> +                               perfuncstate->winobj->win_nonnulls = palloc_array(int64, 16);
> +                               perfuncstate->winobj->nonnulls_size = 16;
>
> Those 2 lines above are not necessary. Since win_nonnulls are
> allocated in ExecInitWindowAgg() in the per query query context, it
> survives across partitions. You only need initialize nonnulls_len to
> 0.

Done.

> @@ -1383,7 +1397,9 @@ release_partition(WindowAggState *winstate)
>
>                 /* Release any partition-local state of this window function */
>                 if (perfuncstate->winobj)
> +               {
>                         perfuncstate->winobj->localmem = NULL;
> +               }
>
> You accidentally added unnecessary curly braces.

Removed.

> @@ -2679,6 +2698,13 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
>                         winobj->argstates = wfuncstate->args;
>                         winobj->localmem = NULL;
>                         perfuncstate->winobj = winobj;
> +                       winobj->ignore_nulls = wfunc->ignore_nulls;
> +                       if (winobj->ignore_nulls == PARSER_IGNORE_NULLS)
> +                       {
> +                               winobj->win_nonnulls = palloc_array(int64, 16);
> +                               winobj->nonnulls_size = 16;
> +                               winobj->nonnulls_len = 0;
> +                       }
>
> I don't like to see two "16" here. It would better to use #define or
> something.
>
> It will be better to declare the prototype of increment_nonnulls,
> ignorenulls_getfuncarginpartition, and ignorenulls_getfuncarginframe
> in the begging of the file as other static functions already do.

Made a new define and added declarations.

> +/*
> + * ignorenulls_getfuncarginframe
> + * For IGNORE NULLS, get the next nonnull value in the frame, moving forward or backward
> + * until we find a value or reach the frame's end.
> + */
> +static Datum
> +ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
>
> Do you assume that win_nonnulls is sorted by pos? I think it's
> necessarily true that pos in win_nonnulls array is sorted. Is that ok?

Yes it must be sorted on my understanding of the code.

> +       /*
> +        * Store previous rows. Only possible in SEEK_HEAD mode
> +        */
> +       for (i = 0; i < winobj->nonnulls_len; ++i)
> +       {
> +               int                     inframe;
> +
> +               if (winobj->win_nonnulls[i] < winobj->markpos)
>
> There are too many "winobj->win_nonnulls[i]". You could assign to a
> variable "winobj->win_nonnulls[i]" and use the variable.

Done.

> +                       continue;
> +               if (!window_gettupleslot(winobj, winobj->win_nonnulls[i], slot))
> +                       continue;
> +
> +               inframe = row_is_in_frame(winstate, winobj->win_nonnulls[i], slot);
> +               if (inframe <= 0)
> +               {
> +                       if (inframe == -1 && set_mark)
> +                               WinSetMarkPosition(winobj, winobj->win_nonnulls[i]);
>
> I think in most cases inframe returns 0 and WinSetMarkPosition is not
> called. What use case do you have in your mind when inframe is -1?

Removed.

> +check_frame:
> +       do
> +       {
> +               int                     inframe;
> +
> +               if (!window_gettupleslot(winobj, abs_pos, slot))
> +                       goto out_of_frame;
> +
> +               inframe = row_is_in_frame(winstate, abs_pos, slot);
> +               if (inframe == -1)
> +                       goto out_of_frame;
> +               else if (inframe == 0)
> +                       goto advance;
> +
> +               gottuple = window_gettupleslot(winobj, abs_pos, slot);
>
> Do you really need to call window_gettupleslot here? It's already
> called above.

Removed.

> --- a/src/include/nodes/primnodes.h
> +++ b/src/include/nodes/primnodes.h
> @@ -576,6 +576,18 @@ typedef struct GroupingFunc
>   * Collation information is irrelevant for the query jumbling, as is the
>   * internal state information of the node like "winstar" and "winagg".
>   */
> +
> +/*
> + * Null Treatment options. If specified, initially set to PARSER_IGNORE
> + * or PARSER_RESPECT. PARSER_IGNORE_NULLS is then converted to IGNORE_NULLS
> + * if the window function allows the null treatment clause.
> + */
> +#define IGNORE_NULLS 4
> +#define RESPECT_NULLS 3
> +#define PARSER_IGNORE_NULLS 2
> +#define PARSER_RESPECT_NULLS 1
> +#define NO_NULLTREATMENT 0
>
> This looks strange to me. Why do you start the define value from 4
> down to 0? Also there is no place to use RESPECT_NULLS. Do we need it?

Removed RESPECT_NULLS and started from 0.

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-01-28T09:02:32Z

    >> +/*
>> + * ignorenulls_getfuncarginframe
>> + * For IGNORE NULLS, get the next nonnull value in the frame, moving forward or backward
>> + * until we find a value or reach the frame's end.
>> + */
>> +static Datum
>> +ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
>>
>> Do you assume that win_nonnulls is sorted by pos? I think it's
>> necessarily true that pos in win_nonnulls array is sorted. Is that ok?
> 
> Yes it must be sorted on my understanding of the code.

Then the patch has a problem. I ran a query below and examined
win_nonnulls.  It seems it was not sorted out.

SELECT
  x,y,
  nth_value(y,1) IGNORE NULLS OVER w
FROM (VALUES (1,1), (2,2), (3,NULL), (4,4), (5,NULL), (6,6), (7,7)) AS t(x,y)
WINDOW w AS (ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW);


(gdb) p *winobj->win_nonnulls @ winobj->nonnulls_len
$8 = {1, 0, 3, 6, 5}

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Oliver Ford <ojford@gmail.com> — 2025-01-30T12:02:19Z

    On Tue, Jan 28, 2025 at 9:02 AM Tatsuo Ishii <ishii@postgresql.org> wrote:
>
> >> +/*
> >> + * ignorenulls_getfuncarginframe
> >> + * For IGNORE NULLS, get the next nonnull value in the frame, moving forward or backward
> >> + * until we find a value or reach the frame's end.
> >> + */
> >> +static Datum
> >> +ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
> >>
> >> Do you assume that win_nonnulls is sorted by pos? I think it's
> >> necessarily true that pos in win_nonnulls array is sorted. Is that ok?
> >
> > Yes it must be sorted on my understanding of the code.
>
> Then the patch has a problem. I ran a query below and examined
> win_nonnulls.  It seems it was not sorted out.
>
> SELECT
>   x,y,
>   nth_value(y,1) IGNORE NULLS OVER w
> FROM (VALUES (1,1), (2,2), (3,NULL), (4,4), (5,NULL), (6,6), (7,7)) AS t(x,y)
> WINDOW w AS (ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW);
>
>
> (gdb) p *winobj->win_nonnulls @ winobj->nonnulls_len
> $8 = {1, 0, 3, 6, 5}
>

I've looked at it again and I think the code is correct, but I
miswrote that the array needs to be sorted. The above query returns:
 x | y | nth_value
---+---+-----------
 1 | 1 |         2
 2 | 2 |         1
 3 |   |         2
 4 | 4 |
 5 |   |         4
 6 | 6 |         7
 7 | 7 |         6
(7 rows)

This is correct, for values of x:

1: The first non-null value of y is at position 0, however we have
EXCLUDE CURRENT ROW so it picks the next non-null value at position 1
and stores it in the array, returning 2.
2: We can now take the first non-null value of y at position 0 and
store it in the array, returning 1.
3. We take 1 preceding, using the position stored in the array, returning 2.
4. 1 preceding and 1 following are both null, and we exclude the
current row, so returning null.
5. 1 preceding is at position 3, store it in the array, returning 4.
6. 1 preceding is null and we exclude the current row, so store
position 6 in the array, returning 7.
7. 1 preceding is at position 5, store it in the array and return 6.

It will be unordered when the EXCLUDE clause is used but the code
should handle this correctly.

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-01-31T02:25:57Z

    > I've looked at it again and I think the code is correct,

Good news! I will look into your explanation.

> but I
> miswrote that the array needs to be sorted. The above query returns:
>  x | y | nth_value
> ---+---+-----------
>  1 | 1 |         2
>  2 | 2 |         1
>  3 |   |         2
>  4 | 4 |
>  5 |   |         4
>  6 | 6 |         7
>  7 | 7 |         6
> (7 rows)
> 
> This is correct, for values of x:
> 
> 1: The first non-null value of y is at position 0, however we have
> EXCLUDE CURRENT ROW so it picks the next non-null value at position 1
> and stores it in the array, returning 2.
> 2: We can now take the first non-null value of y at position 0 and
> store it in the array, returning 1.
> 3. We take 1 preceding, using the position stored in the array, returning 2.
> 4. 1 preceding and 1 following are both null, and we exclude the
> current row, so returning null.
> 5. 1 preceding is at position 3, store it in the array, returning 4.
> 6. 1 preceding is null and we exclude the current row, so store
> position 6 in the array, returning 7.
> 7. 1 preceding is at position 5, store it in the array and return 6.
> 
> It will be unordered when the EXCLUDE clause is used but the code
> should handle this correctly.

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-02-03T11:45:55Z

    > I've looked at it again and I think the code is correct, but I
> miswrote that the array needs to be sorted. The above query returns:
>  x | y | nth_value
> ---+---+-----------
>  1 | 1 |         2
>  2 | 2 |         1
>  3 |   |         2
>  4 | 4 |
>  5 |   |         4
>  6 | 6 |         7
>  7 | 7 |         6
> (7 rows)
> 
> This is correct, for values of x:
> 
> 1: The first non-null value of y is at position 0, however we have
> EXCLUDE CURRENT ROW so it picks the next non-null value at position 1
> and stores it in the array, returning 2.
> 2: We can now take the first non-null value of y at position 0 and
> store it in the array, returning 1.
> 3. We take 1 preceding, using the position stored in the array, returning 2.
> 4. 1 preceding and 1 following are both null, and we exclude the
> current row, so returning null.
> 5. 1 preceding is at position 3, store it in the array, returning 4.
> 6. 1 preceding is null and we exclude the current row, so store
> position 6 in the array, returning 7.
> 7. 1 preceding is at position 5, store it in the array and return 6.
> 
> It will be unordered when the EXCLUDE clause is used but the code
> should handle this correctly.

I ran this query (not using IGNORE NULLS) and get a result.

SELECT
  x,
  nth_value(x,2) OVER w
FROM generate_series(1,5) g(x)
WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW);
 x | nth_value 
---+-----------
 1 |         3
 2 |         3
 3 |         2
 4 |         3
 5 |         4
(5 rows)

Since there's no NULL in x column, I expected the same result using
IGNORE NULLS, but it was not:

SELECT
  x,
nth_value(x,2) IGNORE NULLS OVER w
FROM generate_series(1,5) g(x)
WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW);
 x | nth_value 
---+-----------
 1 |         3
 2 |         4
 3 |         4
 4 |         3
 5 |         4
(5 rows)

I suspect the difference is in the code path of
ignorenulls_getfuncarginframe and the code path in
WinGetFuncArgInFrame, which takes care of EXCLUDE like this.

				case FRAMEOPTION_EXCLUDE_CURRENT_ROW:
					if (abs_pos >= winstate->currentpos &&
						winstate->currentpos >= winstate->frameheadpos)
						abs_pos++;

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Oliver Ford <ojford@gmail.com> — 2025-02-13T15:02:57Z

    On Mon, Feb 3, 2025 at 11:46 AM Tatsuo Ishii <ishii@postgresql.org> wrote:
>
> > I've looked at it again and I think the code is correct, but I
> > miswrote that the array needs to be sorted. The above query returns:
> >  x | y | nth_value
> > ---+---+-----------
> >  1 | 1 |         2
> >  2 | 2 |         1
> >  3 |   |         2
> >  4 | 4 |
> >  5 |   |         4
> >  6 | 6 |         7
> >  7 | 7 |         6
> > (7 rows)
> >
> > This is correct, for values of x:
> >
> > 1: The first non-null value of y is at position 0, however we have
> > EXCLUDE CURRENT ROW so it picks the next non-null value at position 1
> > and stores it in the array, returning 2.
> > 2: We can now take the first non-null value of y at position 0 and
> > store it in the array, returning 1.
> > 3. We take 1 preceding, using the position stored in the array, returning 2.
> > 4. 1 preceding and 1 following are both null, and we exclude the
> > current row, so returning null.
> > 5. 1 preceding is at position 3, store it in the array, returning 4.
> > 6. 1 preceding is null and we exclude the current row, so store
> > position 6 in the array, returning 7.
> > 7. 1 preceding is at position 5, store it in the array and return 6.
> >
> > It will be unordered when the EXCLUDE clause is used but the code
> > should handle this correctly.
>
> I ran this query (not using IGNORE NULLS) and get a result.
>
> SELECT
>   x,
>   nth_value(x,2) OVER w
> FROM generate_series(1,5) g(x)
> WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW);
>  x | nth_value
> ---+-----------
>  1 |         3
>  2 |         3
>  3 |         2
>  4 |         3
>  5 |         4
> (5 rows)
>
> Since there's no NULL in x column, I expected the same result using
> IGNORE NULLS, but it was not:
>
> SELECT
>   x,
> nth_value(x,2) IGNORE NULLS OVER w
> FROM generate_series(1,5) g(x)
> WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW);
>  x | nth_value
> ---+-----------
>  1 |         3
>  2 |         4
>  3 |         4
>  4 |         3
>  5 |         4
> (5 rows)
>
> I suspect the difference is in the code path of
> ignorenulls_getfuncarginframe and the code path in
> WinGetFuncArgInFrame, which takes care of EXCLUDE like this.
>
>                                 case FRAMEOPTION_EXCLUDE_CURRENT_ROW:
>                                         if (abs_pos >= winstate->currentpos &&
>                                                 winstate->currentpos >= winstate->frameheadpos)
>                                                 abs_pos++;

Attached version doesn't use the nonnulls array if an Exclude is
specified, as I think it's not going to work with exclusions (as it's
only an optimization, this is ok and can be taken out entirely if you
prefer). I've also added your tests above to the tests.

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-02-18T04:19:28Z

    > Attached version doesn't use the nonnulls array if an Exclude is
> specified, as I think it's not going to work with exclusions (as it's
> only an optimization, this is ok and can be taken out entirely if you
> prefer). I've also added your tests above to the tests.

I applied the v7 patch and ran regression and tap test. There was no
errors. Great!

BTW, I noticed that in the code path where
ignorenulls_getfuncarginframe() is called, WinSetMarkPosition() is
never called?

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Oliver Ford <ojford@gmail.com> — 2025-02-18T16:50:30Z

    On Tue, Feb 18, 2025 at 4:19 AM Tatsuo Ishii <ishii@postgresql.org> wrote:

> > Attached version doesn't use the nonnulls array if an Exclude is
> > specified, as I think it's not going to work with exclusions (as it's
> > only an optimization, this is ok and can be taken out entirely if you
> > prefer). I've also added your tests above to the tests.
>
> I applied the v7 patch and ran regression and tap test. There was no
> errors. Great!
>
> BTW, I noticed that in the code path where
> ignorenulls_getfuncarginframe() is called, WinSetMarkPosition() is
> never called?
>
> Attached version uses the mark_pos at the end.

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-02-28T11:48:51Z

    >> BTW, I noticed that in the code path where
>> ignorenulls_getfuncarginframe() is called, WinSetMarkPosition() is
>> never called?
>>
>> Attached version uses the mark_pos at the end.

I did simple performance test against v8.

EXPLAIN ANALYZE
SELECT
  x,
nth_value(x,2) IGNORE NULLS OVER w
FROM generate_series(1,$i) g(x)
WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING);

I changed $i = 1k, 2k, 3k, 4k, 5k... 10k and got this:

Number	Time (ms)
of rows	
----------------
1000	28.977
2000	96.556
3000	212.019
4000	383.615
5000	587.05
6000	843.23
7000	1196.177
8000	1508.52
9000	1920.593
10000	2514.069

As you can see, when the number of rows = 1k, it took 28 ms. For 10k
rows, it took 2514 ms, which is 86 times slower than the 1k case. Can
we enhance this?

Graph attached.

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Oliver Ford <ojford@gmail.com> — 2025-03-06T09:57:30Z

    On Fri, Feb 28, 2025 at 11:49 AM Tatsuo Ishii <ishii@postgresql.org> wrote:

> >> BTW, I noticed that in the code path where
> >> ignorenulls_getfuncarginframe() is called, WinSetMarkPosition() is
> >> never called?
> >>
> >> Attached version uses the mark_pos at the end.
>
> I did simple performance test against v8.
>
> EXPLAIN ANALYZE
> SELECT
>   x,
> nth_value(x,2) IGNORE NULLS OVER w
> FROM generate_series(1,$i) g(x)
> WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING);
>
> I changed $i = 1k, 2k, 3k, 4k, 5k... 10k and got this:
>
> Number  Time (ms)
> of rows
> ----------------
> 1000    28.977
> 2000    96.556
> 3000    212.019
> 4000    383.615
> 5000    587.05
> 6000    843.23
> 7000    1196.177
> 8000    1508.52
> 9000    1920.593
> 10000   2514.069
>
> As you can see, when the number of rows = 1k, it took 28 ms. For 10k
> rows, it took 2514 ms, which is 86 times slower than the 1k case. Can
> we enhance this?
>
>
Attached version removes the non-nulls array. That seems to speed
everything up.  Running the above query with 1 million rows averages 450ms,
similar when using lead/lag.

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-03-09T06:39:57Z

    > Attached version removes the non-nulls array. That seems to speed
> everything up.  Running the above query with 1 million rows averages 450ms,
> similar when using lead/lag.

Great. However, CFbot complains about the patch:

https://cirrus-ci.com/task/6364194477441024

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Oliver Ford <ojford@gmail.com> — 2025-03-09T20:07:33Z

    On Sun, Mar 9, 2025 at 6:40 AM Tatsuo Ishii <ishii@postgresql.org> wrote:

> > Attached version removes the non-nulls array. That seems to speed
> > everything up.  Running the above query with 1 million rows averages
> 450ms,
> > similar when using lead/lag.
>
> Great. However, CFbot complains about the patch:
>
> https://cirrus-ci.com/task/6364194477441024
>
>
Attached fixes the headerscheck locally.

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

    Oliver Ford <ojford@gmail.com> — 2025-03-13T07:49:03Z

    On Sun, 9 Mar 2025, 20:07 Oliver Ford, <ojford@gmail.com> wrote:

> On Sun, Mar 9, 2025 at 6:40 AM Tatsuo Ishii <ishii@postgresql.org> wrote:
>
>> > Attached version removes the non-nulls array. That seems to speed
>> > everything up.  Running the above query with 1 million rows averages
>> 450ms,
>> > similar when using lead/lag.
>>
>> Great. However, CFbot complains about the patch:
>>
>> https://cirrus-ci.com/task/6364194477441024
>>
>>
> Attached fixes the headerscheck locally.
>

v11 attached because the previous version was broken by commit 8b1b342

>

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

    Krasiyan Andreev <krasiyan@gmail.com> — 2025-03-29T07:51:55Z

    Hi,
Patch applies and compiles, all included tests passed and after the latest
fixes for non-nulls array, performance is near to lead/lag without support
of "ignore nulls".
I have been using the last version for more than one month in a production
environment with real data and didn't find any bugs, so It is ready for
committer status.

На чт, 13.03.2025 г. в 9:49 Oliver Ford <ojford@gmail.com> написа:

>
>
> On Sun, 9 Mar 2025, 20:07 Oliver Ford, <ojford@gmail.com> wrote:
>
>> On Sun, Mar 9, 2025 at 6:40 AM Tatsuo Ishii <ishii@postgresql.org> wrote:
>>
>>> > Attached version removes the non-nulls array. That seems to speed
>>> > everything up.  Running the above query with 1 million rows averages
>>> 450ms,
>>> > similar when using lead/lag.
>>>
>>> Great. However, CFbot complains about the patch:
>>>
>>> https://cirrus-ci.com/task/6364194477441024
>>>
>>>
>> Attached fixes the headerscheck locally.
>>
>
> v11 attached because the previous version was broken by commit 8b1b342
>
>>

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-03-29T11:18:09Z

    > Hi,
> Patch applies and compiles, all included tests passed and after the latest
> fixes for non-nulls array, performance is near to lead/lag without support
> of "ignore nulls".
> I have been using the last version for more than one month in a production
> environment with real data and didn't find any bugs, so It is ready for
> committer status.

One thing I worry about the patch is, now the non-nulls array
optimization was removed.  Since then I have been thinking about if
there could be other way to optimize searching for non null rows.

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-06-11T22:52:35Z

    > One thing I worry about the patch is, now the non-nulls array
> optimization was removed.  Since then I have been thinking about if
> there could be other way to optimize searching for non null rows.

Here is the v12 patch to implement the optimization on top of Oliver's
v11 patch. Only src/backend/executor/nodeWindowAgg.c was modified
(especially ignorenulls_getfuncarginframe). In the patch I created
2-bit not null information array, representing following status for
each row:

UNKNOWN: the row is not determined whether it's NULL or NOT yet.
	 This is the initial value.
NULL: the row has been determined to be NULL.
NOT NULL: the row has been determined to be NOT NULL.

In ignorenulls_getfuncarginframe:

For the first time window function visits a row in a frame, the row is
fetched using window_gettupleslot() and it is checked whether it is in
the frame using row_is_in_frame(). If it's in the frame and the
information in the array is UNKNOWN, ExecEvalExpr() is executed to
find out if the expression on the function argument is NULL or
not. And the result (NULL or NOT NULL) is stored in the array.

If the information in the array is not UNKNOWN, we can skip calling
ExecEvalExpr() because the information is already in the array.

Note that I do not skip calling window_gettupleslot() and
row_is_in_frame(), skip only calling ExecEvalExpr(), because whether a
row is in a frame or not could be changing as the current row position
moves while processing window functions.

With this technique I observed around 40% speed up in my environment
using the script attached, comparing with Oliver's v11 patch.

v11:
rows	duration (msec)
1000	41.019 
2000	148.957 
3000	248.291 
4000	442.478 
5000	687.395

v12:
rows	duration (msec)
1000	27.515 
2000	78.913 
3000	174.737 
4000	311.412 
5000	482.156 

The patch is now generated using the standard git format-patch.  Also
I have slightly adjusted the coding style so that it aligns with the
one used in nodeWindowAgg.c, and ran pgindent.

Note that I have not modified ignorenulls_getfuncarginpartition yet. I
think we could optimize it using the not null info infrastructure as
well. Will come up with it.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-06-19T06:21:11Z

    > Here is the v12 patch to implement the optimization on top of Oliver's
> v11 patch. Only src/backend/executor/nodeWindowAgg.c was modified
> (especially ignorenulls_getfuncarginframe). In the patch I created
> 2-bit not null information array, representing following status for
> each row:
> 
> UNKNOWN: the row is not determined whether it's NULL or NOT yet.
> 	 This is the initial value.
> NULL: the row has been determined to be NULL.
> NOT NULL: the row has been determined to be NOT NULL.
> 
> In ignorenulls_getfuncarginframe:
> 
> For the first time window function visits a row in a frame, the row is
> fetched using window_gettupleslot() and it is checked whether it is in
> the frame using row_is_in_frame(). If it's in the frame and the
> information in the array is UNKNOWN, ExecEvalExpr() is executed to
> find out if the expression on the function argument is NULL or
> not. And the result (NULL or NOT NULL) is stored in the array.
> 
> If the information in the array is not UNKNOWN, we can skip calling
> ExecEvalExpr() because the information is already in the array.
> 
> Note that I do not skip calling window_gettupleslot() and
> row_is_in_frame(), skip only calling ExecEvalExpr(), because whether a
> row is in a frame or not could be changing as the current row position
> moves while processing window functions.
> 
> With this technique I observed around 40% speed up in my environment
> using the script attached, comparing with Oliver's v11 patch.
> 
> v11:
> rows	duration (msec)
> 1000	41.019 
> 2000	148.957 
> 3000	248.291 
> 4000	442.478 
> 5000	687.395
> 
> v12:
> rows	duration (msec)
> 1000	27.515 
> 2000	78.913 
> 3000	174.737 
> 4000	311.412 
> 5000	482.156 
> 
> The patch is now generated using the standard git format-patch.  Also
> I have slightly adjusted the coding style so that it aligns with the
> one used in nodeWindowAgg.c, and ran pgindent.
> 
> Note that I have not modified ignorenulls_getfuncarginpartition yet. I
> think we could optimize it using the not null info infrastructure as
> well. Will come up with it.

Attached is the v13 patch to address this: i.e. optimize window
functions (lead/lag) working in a partition. In summary I get 40x
speed up comparing with v12 patch. Here is the test script.

EXPLAIN ANALYZE
SELECT lead(x, 5000) IGNORE NULLS OVER ()
              FROM generate_series(1,10000) g(x);

This looks for the 5k th row in a partition including 10k rows.
The average duration of 3 trials are:

v12: 2563.665 ms
v13: 126.259 ms

So I got 40x speed up with the v13 patch. In v12, we needed to scan
10k row partition over and over again. In v13 I used the same NULL/NOT
NULL cache infrastructure created in v12, and we need to scan the
partition only once. This is the reason for the speed up.

Also I removed ignorenulls_getfuncarginpartition(), which was the work
horse for null treatment for window functions working for partitions
in v12 patch. Basically it was a copy and modified version of
WinGetFuncArgInPartition(), thus had quite a few code duplication. In
v13, instead I modified WinGetFuncArgInPartition() so that it can
handle directly null treatment procedures.

BTW I am still not satisfied by the performance improvement for window
functions for frames, that was only 40%. I will study the code to look
for more optimization.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-06-25T07:19:03Z

    > BTW I am still not satisfied by the performance improvement for window
> functions for frames, that was only 40%. I will study the code to look
> for more optimization.

So I come up with more optimization for window functions working on
frames (i.e. first_value, last_value and nth_value). Attached v14
patch does it.

There are 3 major functions used here.

1) window_gettupleslot (get a row)
2) row_is_in_frame (check whether row is in frame or not)
3) ExecEvalExpr (evaluate arg on the row)

In v12 (and v13), we eliminate #3 in some cases but the saving was
only 40%. In v14, I found some cases where we don't need to call
#1. row_is_in_frame requires a row ("tuple" argument), which is
provided by #1. However row_is_in_frame actually uses the row argument
only when frame clause is "RANGE" or "GROUPS" and frame end is
"CURRENT ROW". In other cases it does not use "tuple" argument at
all. So I check the frame clause and the frame end, and if they are
not the case, I can omit #1. Plus if the not null cache for the row
has been already created, we can omit #3 as well. The optimization
contributes to the performance. I observe 2.7x (1k rows case) to 5.2x
(3k rows case) speed up when I compare the performance of v13 patch
and v14 patch using the same script (see attached).

v13:
rows	duration (msec)
1000	34.740
2000	91.169
3000	205.847
4000	356.142
5000	557.063 

v14:
rows	duration (msec)
1000	12.807
2000	21.782
3000	39.248
4000 	69.123
5000 	101.220

I am not sure how the case where frame clause is "RANGE" or "GROUPS"
and frame end is "CURRENT ROW" is majority of window function use
cases. If it's majority, the optimization in v14 does not help much
because v14 does not optimize the case.  However if it's not, the v14
patch is close to commitable form, I think. Comments are welcome.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-06-30T05:25:56Z

    Attached is the v15 patch to fix CFbot complains.
Other than that, nothing has been changed since v14.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Krasiyan Andreev <krasiyan@gmail.com> — 2025-06-30T08:08:26Z

    Hi,
Patch applies and compiles, all included tests passed and performance gain
is really impressive. I have been using the latest versions for months with
real data and didn't find any bugs, so It is definitely ready for committer
status.

На пн, 30.06.2025 г. в 8:26 Tatsuo Ishii <ishii@postgresql.org> написа:

> Attached is the v15 patch to fix CFbot complains.
> Other than that, nothing has been changed since v14.
>
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
>

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-07-01T00:19:06Z

    Krasiyan,

> Hi,
> Patch applies and compiles, all included tests passed and performance gain
> is really impressive. I have been using the latest versions for months with
> real data and didn't find any bugs, so It is definitely ready for committer
> status.

Thanks for testing the patch. The CF status has been already set to
"ready for committer". I just changed the target version from 18 to
19.

> I am not sure how the case where frame clause is "RANGE" or "GROUPS"
> and frame end is "CURRENT ROW" is majority of window function use
> cases. If it's majority, the optimization in v14 does not help much
> because v14 does not optimize the case.  However if it's not, the v14
> patch is close to commitable form, I think. Comments are welcome.

Have you tested cases where the frame option is "RANGE" or "GROUPS"
and the frame end is "CURRENT ROW"? I am asking because in these cases
the optimization in the v14 (and v15) patches do not apply and you may
not be satisfied by the performance.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-07-07T05:37:37Z

    Attached is the v16 patch.

In this patch I have changed row_is_in_frame() API from:

static int	row_is_in_frame(WindowAggState *winstate, int64 pos,
							TupleTableSlot *slot);

to:

static int	row_is_in_frame(WindowObject winobj, int64 pos,
							TupleTableSlot *slot, bool fetch_tuple);

The function is used to decide whether a row specified by pos is in a
frame or not. Previously we needed to always pass "slot" parameter
which is the row in question, fetched by window_gettupleslot. If
IGNORE NULLS option is not passed to window functions, this is fine
because they need to return the row anyway.

However if IGNORE NULLS specified, we need to throw away null rows
until we find the non null row requested by the caller. In reality,
not in all window frames it is required to pass a row to
row_is_in_frame: only when specific frame options are specified. For
example RANGE or GROUP options plus CURRENT ROW frame end option. In
previous patch, I explicitly checked these frame options before
calling row_is_in_frame. However I dislike the way because it's a
layer abstraction violation.

So in this patch I added "fetch_tuple" option to row_is_in_frame so
that it fetches row itself when necessary. A caller now don't need to
fetch the row to pass if fetch_tuple is false.

This way, not only we can avoid the layer violation problem, but
performance is enhanced because tuple is fetched only when it's
necessary.

Note that now the first argument of row_is_in_frame has been changed
from WindowAggState to WindowObject so that row_is_in_frame can call
window_gettupleslot inside.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-07-16T04:44:07Z

    Currently the patch set include some regression test additions. I
wanted to expand the test coverage and ended up an idea: generate new
test cases from the existing window function regression test
(window.sql).

Attached "insert_ignore_nulls.sh" script reads window.sql and inserts
"ignore nulls" before "over" clause of each window functions that
accept IGNORE NULLS option. This way, although the generated test
cases do not cover the case where NULL is included, at least covers
all non NULL cases, which is better than nothing, I think.

I replaced the existing window.sql with the modified one, and ran the
regression test. Indeed the test failed because expected file is for
non IGNORE NULLS options. However, the differences should be just for
SQL statements, not the output of the SQL statements since the data
set used does not include NULLs. I did an eyeball check the diff and
the result was what I expected.

For those who are interested this test, I attached some files.

insert_ignore_nulls.sh: shell script to insert "ignore nulls"
window.sql: modified regression script by insert_ignore_nulls.sh
window.diff: diff of original window.out and modified window.out

Question is, how could we put this kind of test into core if it worth
the effort? The simplest idea is just adding the modified window.sql
to the end of existing window.sql and update window.out. Thoughts?

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-07-25T07:49:14Z

    Attached are the v17 patches for adding RESPECT/IGNORE NULLS options
defined in the standard to some window functions. FROM FIRST/LAST
options are not considered in the patch (yet).

This time I split the patch into 6
patches for reviewer's convenience. Also each patch has a short commit
message to explain the patch.

0001: parse and analysis
0002: rewriter
0003: planner
0004: executor
0005: documents
0006: tests

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-08-16T09:33:29Z

    Attached are the v18 patches for adding RESPECT/IGNORE NULLS options
to some window functions. Recent changes to doc/src/sgml/func.sgml
required v17 to be rebased. Other than that, nothing has been changed.

Oliver, do you have any comments on the patches?

> Attached are the v17 patches for adding RESPECT/IGNORE NULLS options
> defined in the standard to some window functions. FROM FIRST/LAST
> options are not considered in the patch (yet).
> 
> This time I split the patch into 6
> patches for reviewer's convenience. Also each patch has a short commit
> message to explain the patch.
> 
> 0001: parse and analysis
> 0002: rewriter
> 0003: planner
> 0004: executor
> 0005: documents
> 0006: tests

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Oliver Ford <ojford@gmail.com> — 2025-08-16T19:19:54Z

    On Sat, Aug 16, 2025 at 10:33 AM Tatsuo Ishii <ishii@postgresql.org> wrote:

> Attached are the v18 patches for adding RESPECT/IGNORE NULLS options
> to some window functions. Recent changes to doc/src/sgml/func.sgml
> required v17 to be rebased. Other than that, nothing has been changed.
>
> Oliver, do you have any comments on the patches?
>

Looks good, tried it on the nth_value test script from a bit ago - I added
a 1 million rows test and it takes an average of 12 seconds on my i7.

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-08-16T22:34:19Z

    >> Attached are the v18 patches for adding RESPECT/IGNORE NULLS options
>> to some window functions. Recent changes to doc/src/sgml/func.sgml
>> required v17 to be rebased. Other than that, nothing has been changed.
>>
>> Oliver, do you have any comments on the patches?
>>
> 
> Looks good, tried it on the nth_value test script from a bit ago - I added
> a 1 million rows test and it takes an average of 12 seconds on my i7.

Thanks.

I have moved the CF entry from PG19-1 to PG19-2 as PG19-1 has been
already closed on July 31. Hope this help CF bot to catch the v18
patches.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-09-12T09:53:16Z

    >> Attached are the v18 patches for adding RESPECT/IGNORE NULLS options
>> to some window functions. Recent changes to doc/src/sgml/func.sgml
>> required v17 to be rebased. Other than that, nothing has been changed.
>>
>> Oliver, do you have any comments on the patches?
>>
> 
> Looks good, tried it on the nth_value test script from a bit ago - I added
> a 1 million rows test and it takes an average of 12 seconds on my i7.

I would like to push the patch by the end of this month or early in
October if there's no objection.

Comments/suggestions are welcome.
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Chao Li <li.evan.chao@gmail.com> — 2025-09-15T08:08:43Z

    Overall LGTM. Just a few small comments:

> On Sep 12, 2025, at 17:53, Tatsuo Ishii <ishii@postgresql.org> wrote:
> 
> 
> Comments/suggestions are welcome.
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
> 


1 - 0001
```
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -98,6 +98,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
 	bool		agg_star = (fn ? fn->agg_star : false);
 	bool		agg_distinct = (fn ? fn->agg_distinct : false);
 	bool		func_variadic = (fn ? fn->func_variadic : false);
+	int			ignore_nulls = (fn ? fn->ignore_nulls : 0);
```

Should we use the constant NO_NULLTREATMENT here for 0?

2 - 0001
```
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -579,6 +579,17 @@ typedef struct GroupingFunc
  * Collation information is irrelevant for the query jumbling, as is the
  * internal state information of the node like "winstar" and "winagg".
  */
+
+/*
+ * Null Treatment options. If specified, initially set to PARSER_IGNORE_NULLS
+ * which is then converted to IGNORE_NULLS if the window function allows the
+ * null treatment clause.
+ */
+#define NO_NULLTREATMENT 0
+#define PARSER_IGNORE_NULLS 1
+#define PARSER_RESPECT_NULLS 2
+#define IGNORE_NULLS 3
+
 typedef struct WindowFunc
 {
 	Expr		xpr;
@@ -602,6 +613,8 @@ typedef struct WindowFunc
 	bool		winstar pg_node_attr(query_jumble_ignore);
 	/* is function a simple aggregate? */
 	bool		winagg pg_node_attr(query_jumble_ignore);
+	/* ignore nulls. One of the Null Treatment options */
+	int			ignore_nulls;
```

Maybe we can use “uint8” type for “ignore_nulls”. Because the previous two are both of type “bool”, an uint8 will just fit to the padding bytes, so that new field won’t add extra memory to the structure.

3 - 0004
```
 			winobj->markpos = -1;
 			winobj->seekpos = -1;
+
+			/* reset null map */
+			if (perfuncstate->winobj->ignore_nulls == IGNORE_NULLS)
+				memset(perfuncstate->winobj->notnull_info, 0,
+					   NN_POS_TO_BYTES(perfuncstate->winobj->num_notnull_info));
 		}
```
Where in “if” and “memset()”, we can just use “winobj”.

4 - 0004
```
+		if (!HeapTupleIsValid(proctup))
+			elog(ERROR, "cache lookup failed for function %u", funcid);
+		procform = (Form_pg_proc) GETSTRUCT(proctup);
+		elog(ERROR, "function %s does not allow RESPECT/IGNORE NULLS",
+			 NameStr(procform->proname));
```

“Procform” is assigned but not used.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-09-17T09:18:51Z

    > Overall LGTM. Just a few small comments:

> 1 - 0001
> ```
> --- a/src/backend/parser/parse_func.c
> +++ b/src/backend/parser/parse_func.c
> @@ -98,6 +98,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
>  	bool		agg_star = (fn ? fn->agg_star : false);
>  	bool		agg_distinct = (fn ? fn->agg_distinct : false);
>  	bool		func_variadic = (fn ? fn->func_variadic : false);
> +	int			ignore_nulls = (fn ? fn->ignore_nulls : 0);
> ```
> 
> Should we use the constant NO_NULLTREATMENT here for 0?

Good suggestion. Will fix.

> 2 - 0001
> ```
> --- a/src/include/nodes/primnodes.h
> +++ b/src/include/nodes/primnodes.h
> @@ -579,6 +579,17 @@ typedef struct GroupingFunc
>   * Collation information is irrelevant for the query jumbling, as is the
>   * internal state information of the node like "winstar" and "winagg".
>   */
> +
> +/*
> + * Null Treatment options. If specified, initially set to PARSER_IGNORE_NULLS
> + * which is then converted to IGNORE_NULLS if the window function allows the
> + * null treatment clause.
> + */
> +#define NO_NULLTREATMENT 0
> +#define PARSER_IGNORE_NULLS 1
> +#define PARSER_RESPECT_NULLS 2
> +#define IGNORE_NULLS 3
> +
>  typedef struct WindowFunc
>  {
>  	Expr		xpr;
> @@ -602,6 +613,8 @@ typedef struct WindowFunc
>  	bool		winstar pg_node_attr(query_jumble_ignore);
>  	/* is function a simple aggregate? */
>  	bool		winagg pg_node_attr(query_jumble_ignore);
> +	/* ignore nulls. One of the Null Treatment options */
> +	int			ignore_nulls;
> ```
> 
> Maybe we can use “uint8” type for “ignore_nulls”. Because the previous two are both of type “bool”, an uint8 will just fit to the padding bytes, so that new field won’t add extra memory to the structure.

If we change the data type for ignore_nulls in WindowFunc, we may also
want to change it elsewhere (FuncCall, WindowObjectData,
WindowStatePerFuncData) for consistency?

> 3 - 0004
> ```
>  			winobj->markpos = -1;
>  			winobj->seekpos = -1;
> +
> +			/* reset null map */
> +			if (perfuncstate->winobj->ignore_nulls == IGNORE_NULLS)
> +				memset(perfuncstate->winobj->notnull_info, 0,
> +					   NN_POS_TO_BYTES(perfuncstate->winobj->num_notnull_info));
>  		}
> ```
> Where in “if” and “memset()”, we can just use “winobj”.

Good catch. Will fix.

> 4 - 0004
> ```
> +		if (!HeapTupleIsValid(proctup))
> +			elog(ERROR, "cache lookup failed for function %u", funcid);
> +		procform = (Form_pg_proc) GETSTRUCT(proctup);
> +		elog(ERROR, "function %s does not allow RESPECT/IGNORE NULLS",
> +			 NameStr(procform->proname));
> ```
> 
> “Procform” is assigned but not used.

I think procform is used in the following elog(ERROR, ...).

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-09-24T05:39:00Z

    Attached is the updated v19 patches. Mostly applied changes suggested
by Chao.

>> Overall LGTM. Just a few small comments:
> 
>> 1 - 0001
>> ```
>> --- a/src/backend/parser/parse_func.c
>> +++ b/src/backend/parser/parse_func.c
>> @@ -98,6 +98,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
>>  	bool		agg_star = (fn ? fn->agg_star : false);
>>  	bool		agg_distinct = (fn ? fn->agg_distinct : false);
>>  	bool		func_variadic = (fn ? fn->func_variadic : false);
>> +	int			ignore_nulls = (fn ? fn->ignore_nulls : 0);
>> ```
>> 
>> Should we use the constant NO_NULLTREATMENT here for 0?
> 
> Good suggestion. Will fix.

Done.

>> 2 - 0001
>> ```
>> --- a/src/include/nodes/primnodes.h
>> +++ b/src/include/nodes/primnodes.h
>> @@ -579,6 +579,17 @@ typedef struct GroupingFunc
>>   * Collation information is irrelevant for the query jumbling, as is the
>>   * internal state information of the node like "winstar" and "winagg".
>>   */
>> +
>> +/*
>> + * Null Treatment options. If specified, initially set to PARSER_IGNORE_NULLS
>> + * which is then converted to IGNORE_NULLS if the window function allows the
>> + * null treatment clause.
>> + */
>> +#define NO_NULLTREATMENT 0
>> +#define PARSER_IGNORE_NULLS 1
>> +#define PARSER_RESPECT_NULLS 2
>> +#define IGNORE_NULLS 3
>> +
>>  typedef struct WindowFunc
>>  {
>>  	Expr		xpr;
>> @@ -602,6 +613,8 @@ typedef struct WindowFunc
>>  	bool		winstar pg_node_attr(query_jumble_ignore);
>>  	/* is function a simple aggregate? */
>>  	bool		winagg pg_node_attr(query_jumble_ignore);
>> +	/* ignore nulls. One of the Null Treatment options */
>> +	int			ignore_nulls;
>> ```
>> 
>> Maybe we can use “uint8” type for “ignore_nulls”. Because the previous two are both of type “bool”, an uint8 will just fit to the padding bytes, so that new field won’t add extra memory to the structure.
> 
> If we change the data type for ignore_nulls in WindowFunc, we may also
> want to change it elsewhere (FuncCall, WindowObjectData,
> WindowStatePerFuncData) for consistency?

I tried to change all "int ignore_nulls;" to "uint8 ignore_nulls;" but
gen_node_support.pl dislikes it and complains like:

  could not handle type "uint8" in struct "FuncCall" field "ignore_nulls"

>> 3 - 0004
>> ```
>>  			winobj->markpos = -1;
>>  			winobj->seekpos = -1;
>> +
>> +			/* reset null map */
>> +			if (perfuncstate->winobj->ignore_nulls == IGNORE_NULLS)
>> +				memset(perfuncstate->winobj->notnull_info, 0,
>> +					   NN_POS_TO_BYTES(perfuncstate->winobj->num_notnull_info));
>>  		}
>> ```
>> Where in “if” and “memset()”, we can just use “winobj”.
> 
> Good catch. Will fix.

Done.

>> 4 - 0004
>> ```
>> +		if (!HeapTupleIsValid(proctup))
>> +			elog(ERROR, "cache lookup failed for function %u", funcid);
>> +		procform = (Form_pg_proc) GETSTRUCT(proctup);
>> +		elog(ERROR, "function %s does not allow RESPECT/IGNORE NULLS",
>> +			 NameStr(procform->proname));
>> ```
>> 
>> “Procform” is assigned but not used.
> 
> I think procform is used in the following elog(ERROR, ...).

I added more tests for functions (rank(), dense_rank(),
percent_rank(), cume_dist() and ntile()) that do not support
RESPECT/IGNORE NULLS options to confirm that they throw errors if the
options are given. Previously there was only test cases for
row_number().

Also I have made small cosmetic changes to executor/nodeWindowAgg.c to
make too long lines shorter.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Oliver Ford <ojford@gmail.com> — 2025-09-24T12:24:02Z

    On Wed, Sep 24, 2025 at 6:39 AM Tatsuo Ishii <ishii@postgresql.org> wrote:

> I tried to change all "int ignore_nulls;" to "uint8 ignore_nulls;" but
> gen_node_support.pl dislikes it and complains like:
>
>   could not handle type "uint8" in struct "FuncCall" field "ignore_nulls"
>
>
uint8 was missing in one place in that perl script. The attached patch
silences it for uint8/uint16.

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-09-24T13:18:42Z

    Hi Oliver,
> On Wed, Sep 24, 2025 at 6:39 AM Tatsuo Ishii <ishii@postgresql.org> wrote:
> 
>> I tried to change all "int ignore_nulls;" to "uint8 ignore_nulls;" but
>> gen_node_support.pl dislikes it and complains like:
>>
>>   could not handle type "uint8" in struct "FuncCall" field "ignore_nulls"
>>
>>
> uint8 was missing in one place in that perl script. The attached patch
> silences it for uint8/uint16.

Thank you for the patch. (I noticed int8 is also missing).

I have looked into the commit 964d01ae90c3 which was made by Peter.  I
have quick read through the discussion to know why uint8/uint16 (and
int8) are missing in gen_node_support.pl. Unfortunately I have no
clear idea why these data types are missing in the script.

Peter,
Maybe you wanted to limit the data types that are actually used at
that point? If so, probably we should only add uint8 support this time
(uint8 is only needed to implement $Subject for now).  What do you
think?

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-02T12:15:50Z

    > Thank you for the patch. (I noticed int8 is also missing).
> 
> I have looked into the commit 964d01ae90c3 which was made by Peter.  I
> have quick read through the discussion to know why uint8/uint16 (and
> int8) are missing in gen_node_support.pl. Unfortunately I have no
> clear idea why these data types are missing in the script.
> 
> Peter,
> Maybe you wanted to limit the data types that are actually used at
> that point? If so, probably we should only add uint8 support this time
> (uint8 is only needed to implement $Subject for now).  What do you
> think?

I decided not to include the fix to gen_node_support.pl for now and
commit the patch without it. We could revisit it later on.

So here is the commit message I would like to propose.

For the technical part please look at the message.

Non technical part:
First of all the author is Oliver (no doubt).  I would like to be
listed as a co-author since I wrote the not null cache part.

Next is reviewers. Actually the first effor to implement null
treatment clause was back to 9.3 era (2013) at least. After that
multiple trials to implemnt the feature happend but they had faded
away. I think we don't need to include all of those who joined the old
discussions as reviewers. So I started to check from the discussion:
https://postgr.es/m/flat/CAGMVOdsbtRwE_4+v8zjH1d9xfovDeQAGLkP_B6k69_VoFEgX-A@mail.gmail.com
because it's refered to by the commit fest entry.

Oliver and others, I love to hear your comment!

BTW, Oliver's last patch made the CF bot to misunderstand the patch
contents, which was not actually the main patch. So I attach the same
patch as v20.

----------------------------------------------------------------------
Add IGNORE NULLS/RESPECT NULLS option to Window functions.

Add IGNORE NULLS/RESPECT NULLS option (null treatment clause) to lead,
lag, first_value, last_value and nth_value window functions.  If
unspecified, the default is RESPECT NULLS which includes NULL values
in any result calculation. IGNORE NULLS ignores NULL values.

Built-in window functions are modified to call new API
WinCheckAndInitializeNullTreatment() to indicate whether they accept
IGNORE NULLS/RESPECT NULLS option or not (the API can be called by
user defined window functions as well).  If WinGetFuncArgInPartition's
allowNullTreatment argument is true and IGNORE NULLS option is given,
WinGetFuncArgInPartition() or WinGetFuncArgInFrame() will return
evaluated function's argument expression on specified non NULL row (if
it exists) in the partition or the frame.

When IGNORE NULLS option is given, window functions need to visit and
evaluate same rows over and over again to look for non null rows. To
mitigate the issue, 2-bit not null information array is created while
executing window functions to remember whether the row has been
already evaluated to NULL or NOT NULL. If already evaluated, we could
skip some the evaluation work, thus we could get better performance.

Author: Oliver Ford <ojford@gmail.com>
Co-authored-by: Tatsuo Ishii <ishii@postgresql.org>
Reviewed-by: Andrew Gierth <andrew@tao11.riddles.org.uk>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Reviewed-by: David Fetter <david@fetter.org>
Reviewed-by: Vik Fearing <vik@postgresfriends.org>
Reviewed-by: "David G. Johnston" <david.g.johnston@gmail.com>
Reviewed-by: Krasiyan Andreev <krasiyan@gmail.com>
Reviewed-by: Chao Li <lic@highgo.com>
Discussion: https://postgr.es/m/flat/CAGMVOdsbtRwE_4+v8zjH1d9xfovDeQAGLkP_B6k69_VoFEgX-A@mail.gmail.com
----------------------------------------------------------------------

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Oliver Ford <ojford@gmail.com> — 2025-10-02T15:36:26Z

    On Thu, 2 Oct 2025, 13:16 Tatsuo Ishii, <ishii@postgresql.org> wrote:

> > Thank you for the patch. (I noticed int8 is also missing).
> >
> > I have looked into the commit 964d01ae90c3 which was made by Peter.  I
> > have quick read through the discussion to know why uint8/uint16 (and
> > int8) are missing in gen_node_support.pl. Unfortunately I have no
> > clear idea why these data types are missing in the script.
> >
> > Peter,
> > Maybe you wanted to limit the data types that are actually used at
> > that point? If so, probably we should only add uint8 support this time
> > (uint8 is only needed to implement $Subject for now).  What do you
> > think?
>
> I decided not to include the fix to gen_node_support.pl for now and
> commit the patch without it. We could revisit it later on.
>
> So here is the commit message I would like to propose.
>
> For the technical part please look at the message.
>
> Non technical part:
> First of all the author is Oliver (no doubt).  I would like to be
> listed as a co-author since I wrote the not null cache part.
>
> Next is reviewers. Actually the first effor to implement null
> treatment clause was back to 9.3 era (2013) at least. After that
> multiple trials to implemnt the feature happend but they had faded
> away. I think we don't need to include all of those who joined the old
> discussions as reviewers. So I started to check from the discussion:
>
> https://postgr.es/m/flat/CAGMVOdsbtRwE_4+v8zjH1d9xfovDeQAGLkP_B6k69_VoFEgX-A@mail.gmail.com
> because it's refered to by the commit fest entry.
>
> Oliver and others, I love to hear your comment!


Looks great, so glad this is finally going in.

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-03T01:06:06Z

    > On Thu, 2 Oct 2025, 13:16 Tatsuo Ishii, <ishii@postgresql.org> wrote:
> 
>> > Thank you for the patch. (I noticed int8 is also missing).
>> >
>> > I have looked into the commit 964d01ae90c3 which was made by Peter.  I
>> > have quick read through the discussion to know why uint8/uint16 (and
>> > int8) are missing in gen_node_support.pl. Unfortunately I have no
>> > clear idea why these data types are missing in the script.
>> >
>> > Peter,
>> > Maybe you wanted to limit the data types that are actually used at
>> > that point? If so, probably we should only add uint8 support this time
>> > (uint8 is only needed to implement $Subject for now).  What do you
>> > think?
>>
>> I decided not to include the fix to gen_node_support.pl for now and
>> commit the patch without it. We could revisit it later on.
>>
>> So here is the commit message I would like to propose.
>>
>> For the technical part please look at the message.
>>
>> Non technical part:
>> First of all the author is Oliver (no doubt).  I would like to be
>> listed as a co-author since I wrote the not null cache part.
>>
>> Next is reviewers. Actually the first effor to implement null
>> treatment clause was back to 9.3 era (2013) at least. After that
>> multiple trials to implemnt the feature happend but they had faded
>> away. I think we don't need to include all of those who joined the old
>> discussions as reviewers. So I started to check from the discussion:
>>
>> https://postgr.es/m/flat/CAGMVOdsbtRwE_4+v8zjH1d9xfovDeQAGLkP_B6k69_VoFEgX-A@mail.gmail.com
>> because it's refered to by the commit fest entry.
>>
>> Oliver and others, I love to hear your comment!
> 
> 
> Looks great, so glad this is finally going in.

I have just pushed the patch (plus patches for syntax.sgml and
sql_features.txt. They were missued after I splitted the patch).
Thank you for your effort!
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-10-05T15:59:17Z

    Tatsuo Ishii <ishii@postgresql.org> writes:
> I have just pushed the patch (plus patches for syntax.sgml and
> sql_features.txt. They were missued after I splitted the patch).

Coverity is not very happy with this patch.
It's complaining that the result of window_gettupleslot
is not checked, which seems valid:

1503     			{
1504     				if (fetch_tuple)
>>>     CID 1666587:         Error handling issues  (CHECKED_RETURN)
>>>     Calling "window_gettupleslot" without checking return value (as is done elsewhere 8 out of 9 times).
1505     					window_gettupleslot(winobj, pos, slot);
1506     				if (!are_peers(winstate, slot, winstate->ss.ss_ScanTupleSlot))
1507     					return -1;

and also that WinGetFuncArgInPartition is dereferencing
a possibly-null "isout" pointer at several places, including

>>>     Dereferencing null pointer "isout".
3806     				if (*isout)		/* out of partition? */

>>>     Dereferencing null pointer "isout".
3817     	if (!*isout && set_mark)
3818     		WinSetMarkPosition(winobj, abs_pos);

>>>     Dereferencing null pointer "isout".
3817     	if (!*isout && set_mark)
3818     		WinSetMarkPosition(winobj, abs_pos);

The latter complaints seem to be because some places in
WinGetFuncArgInPartition check for nullness of that pointer
and some do not.  That looks like at least a latent bug
to me.  If it isn't, the function's comment needs to be
expanded to say when it's legal to pass isout == NULL.

			regards, tom lane

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

    Álvaro Herrera <alvherre@kurilemu.de> — 2025-10-05T16:20:30Z

    On 2025-Oct-03, Tatsuo Ishii wrote:

> I have just pushed the patch (plus patches for syntax.sgml and
> sql_features.txt. They were missued after I splitted the patch).
> Thank you for your effort!

I just noticed this compiler warning in a CI run,

[16:06:29.920] ../src/backend/executor/nodeWindowAgg.c:3820:16: warning: ‘datum’ may be used uninitialized [-Wmaybe-uninitialized]
[16:06:29.920]  3820 |         return datum;
[16:06:29.920]       |                ^~~~~
[16:06:29.920] ../src/backend/executor/nodeWindowAgg.c:3719:25: note: ‘datum’ was declared here
[16:06:29.920]  3719 |         Datum           datum;
[16:06:29.920]       |                         ^~~~~

The logic in this function looks somewhat wicked.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-10-05T16:35:46Z

    I wrote:
> ... also that WinGetFuncArgInPartition is dereferencing
> a possibly-null "isout" pointer at several places

Looking around, there is only one in-core caller of
WinGetFuncArgInPartition, and it does pass a valid "isout" pointer,
explaining why this inconsistency wasn't obvious in testing.
There are outside callers though according to Debian Code Search,
and at least PostGIS is one that passes a null pointer.

As Alvaro notes nearby, this function is ridiculously complicated
already.  I'm tempted to remove the API allowance for isout == NULL,
and thereby simplify the code slightly, rather than complicate it more
by continuing to allow that.  We'd have to warn the PostGIS people
about the API change though.

			regards, tom lane

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-10-05T16:51:45Z

    =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de> writes:
> I just noticed this compiler warning in a CI run,

> [16:06:29.920] ../src/backend/executor/nodeWindowAgg.c:3820:16: warning: ‘datum’ may be used uninitialized [-Wmaybe-uninitialized]
> [16:06:29.920]  3820 |         return datum;
> [16:06:29.920]       |                ^~~~~

Yeah, I can easily believe that a compiler running at relatively low
optimization level wouldn't make the connection that the NN_NOTNULL
case must perform the "prepare to exit this loop" bit if the loop
will be exited this time.  But there's another thing that is
confusing: the NN_NULL case certainly looks like it's expecting
to exit the loop, but that "break" will only get out of the switch
not the loop.  Moreover, the NN_NULL case looks like it'd fail
to notice end-of-frame.  And it's not entirely clear what the
default case thinks it's doing either.

In short, this loop is impossible to understand, and the lack of
comments doesn't help.  Even if it's not actually buggy, it
needs to be rewritten in a way that helps readers and compilers
see that it's not buggy.  I think it might help to separate the
detection of null-ness and fetching of the datum value (if required)
from the loop control logic.

			regards, tom lane

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-05T23:51:33Z

    Thank you for the report!

> Coverity is not very happy with this patch.
> It's complaining that the result of window_gettupleslot
> is not checked, which seems valid:
> 
> 1503     			{
> 1504     				if (fetch_tuple)
>>>>     CID 1666587:         Error handling issues  (CHECKED_RETURN)
>>>>     Calling "window_gettupleslot" without checking return value (as is done elsewhere 8 out of 9 times).
> 1505     					window_gettupleslot(winobj, pos, slot);
> 1506     				if (!are_peers(winstate, slot, winstate->ss.ss_ScanTupleSlot))
> 1507     					return -1;

Yes, I forgot to check the return value of window_gettupleslot.

> and also that WinGetFuncArgInPartition is dereferencing
> a possibly-null "isout" pointer at several places, including
> 
>>>>     Dereferencing null pointer "isout".
> 3806     				if (*isout)		/* out of partition? */
> 
>>>>     Dereferencing null pointer "isout".
> 3817     	if (!*isout && set_mark)
> 3818     		WinSetMarkPosition(winobj, abs_pos);
> 
>>>>     Dereferencing null pointer "isout".
> 3817     	if (!*isout && set_mark)
> 3818     		WinSetMarkPosition(winobj, abs_pos);
> 
> The latter complaints seem to be because some places in
> WinGetFuncArgInPartition check for nullness of that pointer
> and some do not.  That looks like at least a latent bug
> to me.

Agreed.

Attached is a patch to fix the issue.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-06T00:09:01Z

    > will be exited this time.  But there's another thing that is
> confusing: the NN_NULL case certainly looks like it's expecting
> to exit the loop, but that "break" will only get out of the switch
> not the loop.

You mean "NN_NOTNULL" case?  if (notnull_offset >= notnull_relpos),
then following "while (notnull_offset < notnull_relpos)" does not
satisfy the continuous condition of the while loop and exits the loop.
I can add "goto" to explicitly exit the loop if we want.

> Moreover, the NN_NULL case looks like it'd fail
> to notice end-of-frame.  And it's not entirely clear what the
> default case thinks it's doing either.

WinGetFuncArgInPartition() does not care about frame, no?

> In short, this loop is impossible to understand, and the lack of
> comments doesn't help.  Even if it's not actually buggy, it
> needs to be rewritten in a way that helps readers and compilers
> see that it's not buggy.  I think it might help to separate the
> detection of null-ness and fetching of the datum value (if required)
> from the loop control logic.

Thanks for the idea.  Let me think if I could change the loop to be
easier to understand.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-06T00:28:13Z

    > Looking around, there is only one in-core caller of
> WinGetFuncArgInPartition, and it does pass a valid "isout" pointer,
> explaining why this inconsistency wasn't obvious in testing.
> There are outside callers though according to Debian Code Search,
> and at least PostGIS is one that passes a null pointer.
> 
> As Alvaro notes nearby, this function is ridiculously complicated
> already.  I'm tempted to remove the API allowance for isout == NULL,
> and thereby simplify the code slightly, rather than complicate it more
> by continuing to allow that.  We'd have to warn the PostGIS people
> about the API change though.

It think it's a good idea.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-06T09:34:39Z

    > Thank you for the report!
> 
>> Coverity is not very happy with this patch.
>> It's complaining that the result of window_gettupleslot
>> is not checked, which seems valid:
>> 
>> 1503     			{
>> 1504     				if (fetch_tuple)
>>>>>     CID 1666587:         Error handling issues  (CHECKED_RETURN)
>>>>>     Calling "window_gettupleslot" without checking return value (as is done elsewhere 8 out of 9 times).
>> 1505     					window_gettupleslot(winobj, pos, slot);
>> 1506     				if (!are_peers(winstate, slot, winstate->ss.ss_ScanTupleSlot))
>> 1507     					return -1;
> 
> Yes, I forgot to check the return value of window_gettupleslot.
> 
>> and also that WinGetFuncArgInPartition is dereferencing
>> a possibly-null "isout" pointer at several places, including
>> 
>>>>>     Dereferencing null pointer "isout".
>> 3806     				if (*isout)		/* out of partition? */
>> 
>>>>>     Dereferencing null pointer "isout".
>> 3817     	if (!*isout && set_mark)
>> 3818     		WinSetMarkPosition(winobj, abs_pos);
>> 
>>>>>     Dereferencing null pointer "isout".
>> 3817     	if (!*isout && set_mark)
>> 3818     		WinSetMarkPosition(winobj, abs_pos);
>> 
>> The latter complaints seem to be because some places in
>> WinGetFuncArgInPartition check for nullness of that pointer
>> and some do not.  That looks like at least a latent bug
>> to me.
> 
> Agreed.
> 
> Attached is a patch to fix the issue.

Please disregard the v1 patch. It includes a bug: If
WinGetFuncArgInPartition() is called with set_mark == true and isout
== NULL, WinSetMarkPosition() is not called by
WinGetFuncArgInPartition().

I will post v2 patch.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-07T02:28:32Z

    >> Thank you for the report!
>> 
>>> Coverity is not very happy with this patch.
>>> It's complaining that the result of window_gettupleslot
>>> is not checked, which seems valid:
>>> 
>>> 1503     			{
>>> 1504     				if (fetch_tuple)
>>>>>>     CID 1666587:         Error handling issues  (CHECKED_RETURN)
>>>>>>     Calling "window_gettupleslot" without checking return value (as is done elsewhere 8 out of 9 times).
>>> 1505     					window_gettupleslot(winobj, pos, slot);
>>> 1506     				if (!are_peers(winstate, slot, winstate->ss.ss_ScanTupleSlot))
>>> 1507     					return -1;
>> 
>> Yes, I forgot to check the return value of window_gettupleslot.
>> 
>>> and also that WinGetFuncArgInPartition is dereferencing
>>> a possibly-null "isout" pointer at several places, including
>>> 
>>>>>>     Dereferencing null pointer "isout".
>>> 3806     				if (*isout)		/* out of partition? */
>>> 
>>>>>>     Dereferencing null pointer "isout".
>>> 3817     	if (!*isout && set_mark)
>>> 3818     		WinSetMarkPosition(winobj, abs_pos);
>>> 
>>>>>>     Dereferencing null pointer "isout".
>>> 3817     	if (!*isout && set_mark)
>>> 3818     		WinSetMarkPosition(winobj, abs_pos);
>>> 
>>> The latter complaints seem to be because some places in
>>> WinGetFuncArgInPartition check for nullness of that pointer
>>> and some do not.  That looks like at least a latent bug
>>> to me.
>> 
>> Agreed.
>> 
>> Attached is a patch to fix the issue.
> 
> Please disregard the v1 patch. It includes a bug: If
> WinGetFuncArgInPartition() is called with set_mark == true and isout
> == NULL, WinSetMarkPosition() is not called by
> WinGetFuncArgInPartition().
> 
> I will post v2 patch.

Attached is the v2 patch.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Paul Ramsey <pramsey@cleverelephant.ca> — 2025-10-07T20:14:27Z

    On Mon, Oct 6, 2025 at 7:29 PM Tatsuo Ishii <ishii@postgresql.org> wrote:

>
> > Please disregard the v1 patch. It includes a bug: If
> > WinGetFuncArgInPartition() is called with set_mark == true and isout
> > == NULL, WinSetMarkPosition() is not called by
> > WinGetFuncArgInPartition().
> >
> > I will post v2 patch.
>
> Attached is the v2 patch.
>

Thanks! This passes regression, and reads right to my eye and (most
important to me) allows PostGIS to run under Pg19 again.

Thanks,
P

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-07T23:50:16Z

    > I just noticed this compiler warning in a CI run,
> 
> [16:06:29.920] ../src/backend/executor/nodeWindowAgg.c:3820:16: warning: ‘datum’ may be used uninitialized [-Wmaybe-uninitialized]
> [16:06:29.920]  3820 |         return datum;
> [16:06:29.920]       |                ^~~~~
> [16:06:29.920] ../src/backend/executor/nodeWindowAgg.c:3719:25: note: ‘datum’ was declared here
> [16:06:29.920]  3719 |         Datum           datum;
> [16:06:29.920]       |                         ^~~~~
> 
> The logic in this function looks somewhat wicked.

Thanks for the report. I believe the warning is eliminated in the v2
patch[1].

Best regards,

[1] https://www.postgresql.org/message-id/20251007.112832.740065769089328041.ishii%40postgresql.org
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-08T00:31:17Z

    >> Attached is the v2 patch.
>>
> 
> Thanks! This passes regression, and reads right to my eye and (most
> important to me) allows PostGIS to run under Pg19 again.

Thank you for the review! I have just pushed the v2 patch.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-10-09T15:19:14Z

    Tatsuo Ishii <ishii@postgresql.org> writes:
> Thank you for the review! I have just pushed the v2 patch.

While I'd paid basically zero attention to this patch (the claim
in the commit message that I reviewed it is a flight of fancy),
I've been forced to look through it as a consequence of the mop-up
that's been happening to silence compiler warnings.  There are a
couple of points that I think were not well done:

1. WinCheckAndInitializeNullTreatment really needs a rethink.
You cannot realistically assume that existing user-defined window
functions will be fixed to call that.  I think it should be set up
so that if the window function fails to call that, then something in
mainline execution of nodeWindowAgg.c throws an error when there had
been a RESPECT/IGNORE NULLS option.  With that idea, you could drop
the allowNullTreatment argument and just have the window functions
that support this syntax call something named along the lines of
WinAllowNullTreatmentOption.  Also the error is certainly user-facing,
so using elog() was quite inappropriate.  It should be ereport with an
errcode of (probably) ERRCODE_FEATURE_NOT_SUPPORTED.  Rolling your
own implementation of get_func_name() wasn't great either.

Alternatively, you could just drop the entire concept of throwing an
error for that.  What's the point?  The implementation is entirely
within nodeWindowAgg.c and does not depend in any way on the
cooperation of the window function.  I do not in any case like the
documentation's wording

+   This option is only allowed for the following functions: <function>lag</function>,
+   <function>lead</function>, <function>first_value</function>, <function>last_value</function>,
+   <function>nth_value</function>.

as this fails to account for the possibility of user-defined window
functions.  IMO we could drop the error check altogether and rewrite
the docs along the lines of "Not all window functions pay attention
to this option.  Of the built-in window functions, only blah blah
and blah do."

2. AFAICS there is only one notnull_info array, which amounts to
assuming that the window function will have only one argument position
that it calls WinGetFuncArgInFrame or WinGetFuncArgInPartition for.
That may be true for the built-in functions but it seems mighty
restrictive for extensions.  Worse yet, there's no check, so that
you'd just get silently wrong answers if two or more arguments are
evaluated.  I think there ought to be a separate array for each argno;
of course only created if the window function actually asks for
evaluations of a particular argno.

			regards, tom lane

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-11T00:07:31Z

    > While I'd paid basically zero attention to this patch (the claim
> in the commit message that I reviewed it is a flight of fancy),

Sorry, I added you as a reviewer because you had joined past
discussions regarding this feature. Next time I will add reviewers
only those who actually looked into a patch.

> I've been forced to look through it as a consequence of the mop-up
> that's been happening to silence compiler warnings.  There are a
> couple of points that I think were not well done:
> 
> 1. WinCheckAndInitializeNullTreatment really needs a rethink.
> You cannot realistically assume that existing user-defined window
> functions will be fixed to call that.

Currently if WinCheckAndInitializeNullTreatment is not called,
RESPECT/IGNORE NULLS option is disregarded and WinGetFuncArgInFrame or
WinGetFuncArgInPartition works as if RESPECT/IGNORE NULLS option is
not given. So I thought it's safe even if existing user-defined window
functions are not fixed.

> I think it should be set up
> so that if the window function fails to call that, then something in
> mainline execution of nodeWindowAgg.c throws an error when there had
> been a RESPECT/IGNORE NULLS option.  With that idea, you could drop
> the allowNullTreatment argument and just have the window functions
> that support this syntax call something named along the lines of
> WinAllowNullTreatmentOption.

Does that mean all user defined window functions start to fail after
upgrading to PostgreSQL 19?  I am not sure if it's acceptable for
extension developers and their users.

> Also the error is certainly user-facing,
> so using elog() was quite inappropriate.  It should be ereport with an
> errcode of (probably) ERRCODE_FEATURE_NOT_SUPPORTED.  Rolling your
> own implementation of get_func_name() wasn't great either.

I overlooked the elog() call and "own implementation of
get_func_name()". Will fix.

> Alternatively, you could just drop the entire concept of throwing an
> error for that.  What's the point?

If we do that, extensions would need to be re-tested against IGNORE
NULLS option case. I might be wrong but I guess some of (or many of)
extension developers do not plan (or have no time to work on it for
now) to utilize IGNORE NULLS option for their extensions.

For buil-in window functions. I don't want to create test cases how
built-in window functions, that are not allowed IGNORE NULLS option,
behave against IGNORE NULLS option. Instead I prefer to throw an error
as it is done today.

> The implementation is entirely
> within nodeWindowAgg.c and does not depend in any way on the
> cooperation of the window function.  I do not in any case like the
> documentation's wording
> 
> +   This option is only allowed for the following functions: <function>lag</function>,
> +   <function>lead</function>, <function>first_value</function>, <function>last_value</function>,
> +   <function>nth_value</function>.

> as this fails to account for the possibility of user-defined window
> functions.

The page explains only built-in window functions. Thus for me it's not
that strange that it does not say anything about user defined window
functions.

> IMO we could drop the error check altogether and rewrite
> the docs along the lines of "Not all window functions pay attention
> to this option.  Of the built-in window functions, only blah blah
> and blah do."
> 
> 2. AFAICS there is only one notnull_info array, which amounts to
> assuming that the window function will have only one argument position
> that it calls WinGetFuncArgInFrame or WinGetFuncArgInPartition for.
> That may be true for the built-in functions but it seems mighty
> restrictive for extensions.  Worse yet, there's no check, so that
> you'd just get silently wrong answers if two or more arguments are
> evaluated.  I think there ought to be a separate array for each argno;
> of course only created if the window function actually asks for
> evaluations of a particular argno.

I missed that. Thank you for pointed it out. I agree it would be
better allow to use multiple argument positions that calls
WinGetFuncArgInFrame or WinGetFuncArgInPartition in
extensions. Attached is a PoC patch for that.

Currently there's an issue with the patch, however.

SELECT x, y, mywindowfunc2(x, y, 2) IGNORE NULLS OVER w FROM g
WINDOW w AS (ORDER BY y);
psql:test2.sql:9: ERROR:  cannot fetch row before WindowObject's mark position

mywindowfunc2 is a user defined window function, taking 3 arguments. x
and y are expected to be evaluated to integer. The third argument is
relative offset to current row. In the query above x and y are
retrieved using two WinGetFuncArgInPartition() calls. The data set
(table "g") looks like below.

 x  | y 
----+---
    | 1
    | 2
 10 | 3
 20 | 4
(4 rows)

I think the cause of the error is:

(1) WinGetFuncArgInPartition keep on fetching column x until it's
evalued to not null and placed in the second row (in this case that's
x==20). In WinGetFuncArgInPartition WinSetMarkPosition is called at
abs_pos==3.

(2) WinGetFuncArgInPartition tries to fetch column y at row 0. Since
the mark was set to at row 3, the error occurred.

To avoid the error, we could call WinGetFuncArgInPartition with
set_mark = false (and call WinSetMarkPosition separately) but I am not
sure if it's an acceptable solution.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-11T05:42:18Z

    >> Also the error is certainly user-facing,
>> so using elog() was quite inappropriate.  It should be ereport with an
>> errcode of (probably) ERRCODE_FEATURE_NOT_SUPPORTED.  Rolling your
>> own implementation of get_func_name() wasn't great either.
> 
> I overlooked the elog() call and "own implementation of
> get_func_name()". Will fix.

Attached is a trivial patch to fix that. I am going to push it if
there's no objection.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Chao Li <li.evan.chao@gmail.com> — 2025-10-11T05:57:58Z

    
> On Oct 11, 2025, at 13:42, Tatsuo Ishii <ishii@postgresql.org> wrote:
> 
>>> Also the error is certainly user-facing,
>>> so using elog() was quite inappropriate.  It should be ereport with an
>>> errcode of (probably) ERRCODE_FEATURE_NOT_SUPPORTED.  Rolling your
>>> own implementation of get_func_name() wasn't great either.
>> 
>> I overlooked the elog() call and "own implementation of
>> get_func_name()". Will fix.
> 
> Attached is a trivial patch to fix that. I am going to push it if
> there's no objection.
> 
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
> <v1-0001-Use-ereport-rather-than-elog-in-WinCheckAndInitia.patch>


I just take a quick look at the patch, a tiny comment is:

```
+		char	   *funcname = get_func_name(fcinfo->flinfo->fn_oid);
```

This can be a “const char *”.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-11T09:11:03Z

    >> I think it should be set up
>> so that if the window function fails to call that, then something in
>> mainline execution of nodeWindowAgg.c throws an error when there had
>> been a RESPECT/IGNORE NULLS option.  With that idea, you could drop
>> the allowNullTreatment argument and just have the window functions
>> that support this syntax call something named along the lines of
>> WinAllowNullTreatmentOption.
> 
> Does that mean all user defined window functions start to fail after
> upgrading to PostgreSQL 19?  I am not sure if it's acceptable for
> extension developers and their users.

Probably I misunderstood what you said. Now I realize what you are
suggesting was, throwing an error *only* when a RESPECT/IGNORE NULLS
option is given and the function did not call
WinAllowNullTreatmentOption. If the option is not given, no error is
thrown even if WinAllowNullTreatmentOption is not called. I am okay
with this direction. I will post a patch for this.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-12T08:39:59Z

    > Probably I misunderstood what you said. Now I realize what you are
> suggesting was, throwing an error *only* when a RESPECT/IGNORE NULLS
> option is given and the function did not call
> WinAllowNullTreatmentOption. If the option is not given, no error is
> thrown even if WinAllowNullTreatmentOption is not called. I am okay
> with this direction. I will post a patch for this.

While I was implementing this, I realized that in order to check if
WinAllowNullTreatmentOption had been called or not, it's necessary to
call the window function at least once in mainline execution of
nodeWindowAgg.c (I supposed in eval_windowfunction). I don't like this
because I expected to throw an error *before* calling the window
function.

So I studied your idea:
> Alternatively, you could just drop the entire concept of throwing an
> error for that.

Attached is a patch to implement this. Previously window functions
should call WinCheckAndInitializeNullTreatment with
allowNullTreatment==true if they accept a null treatment
clause. Otherwise, they are called as if null treatment clause is not
specified. With the patch, window functions accept a null treatment
clause as specified without calling
WinCheckAndInitializeNullTreatment.

There's one thing which might be different from what you suggested
is, I want to give window functions a method to stat that they do not
want accept a null treatment clause. For this purpose
WinCheckAndInitializeNullTreatment (with allowNullTreatment==false)
can be called.(Alternatively we could eliminate allowNullTreatment
argument and rename it something like WinDisallowNullTreatmentOption).

Some of built-in window functions that do not accept a null treatment
clause call this in the patch. This way, we do not need to test the
case when the functions are given a null treatment option except just
they throw an error. User defined functions would call the function
for the same purpose as built-in window functions.

> The implementation is entirely
> within nodeWindowAgg.c and does not depend in any way on the
> cooperation of the window function.

I am not sure. For example built-in lead function's behavior (with
IGNORE NULLS option) is defined by the standard. Unlike RESPECT NULLS
case, the expected behavior may not be obvious. According the
standard:

1. If lead's "offset" option is 0, the argument evaluated on current
   row is returned regardless the value is NULL or NOT.

2. Otherwise, returns the value evaluated on a row which is nth NOT NULL.

For me, 2 is obvious but 1 was not so obvious because I thought that
lead() returns only non NULL value (except there's no non null values
or specified offset is out of partition).

Thus lead() calls WinGetFuncArgInPartition with
seektype==WINDOW_SEEK_CURRENT. Thus WinGetFuncArgInPartition with
WINDOW_SEEK_CURRENT is implemented in a way to satisfy the lead()
semantics above. This means if someone tries to implement a new window
function calling WinGetFuncArgInPartition with WINDOW_SEEK_CURRENT,
the function must has the same semantics as lead(). I think there's a
cooperation between nodeWindowAgg.c and window functions.

> +   This option is only allowed for the following functions: <function>lag</function>,
> +   <function>lead</function>, <function>first_value</function>, <function>last_value</function>,
> +   <function>nth_value</function>.
> 
> as this fails to account for the possibility of user-defined window
> functions.  IMO we could drop the error check altogether and rewrite
> the docs along the lines of "Not all window functions pay attention
> to this option.  Of the built-in window functions, only blah blah
> and blah do."

Fixing docs are not included in the patch (yet).

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-12T12:05:30Z

    Hi Tom,

> While I'd paid basically zero attention to this patch (the claim
> in the commit message that I reviewed it is a flight of fancy),
> I've been forced to look through it as a consequence of the mop-up
> that's been happening to silence compiler warnings.

Sorry for taking up your time to fix the compiler warnings. I haven't
noticed your commit 71540dcdcb2 until today. Next time I will try to
fix warnings found by buildfarm.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-13T04:43:32Z

    >> 2. AFAICS there is only one notnull_info array, which amounts to
>> assuming that the window function will have only one argument position
>> that it calls WinGetFuncArgInFrame or WinGetFuncArgInPartition for.
>> That may be true for the built-in functions but it seems mighty
>> restrictive for extensions.  Worse yet, there's no check, so that
>> you'd just get silently wrong answers if two or more arguments are
>> evaluated.  I think there ought to be a separate array for each argno;
>> of course only created if the window function actually asks for
>> evaluations of a particular argno.
> 
> I missed that. Thank you for pointed it out. I agree it would be
> better allow to use multiple argument positions that calls
> WinGetFuncArgInFrame or WinGetFuncArgInPartition in
> extensions. Attached is a PoC patch for that.
> 
> Currently there's an issue with the patch, however.
> 
> SELECT x, y, mywindowfunc2(x, y, 2) IGNORE NULLS OVER w FROM g
> WINDOW w AS (ORDER BY y);
> psql:test2.sql:9: ERROR:  cannot fetch row before WindowObject's mark position
> 
> mywindowfunc2 is a user defined window function, taking 3 arguments. x
> and y are expected to be evaluated to integer. The third argument is
> relative offset to current row. In the query above x and y are
> retrieved using two WinGetFuncArgInPartition() calls. The data set
> (table "g") looks like below.
> 
>  x  | y 
> ----+---
>     | 1
>     | 2
>  10 | 3
>  20 | 4
> (4 rows)
> 
> I think the cause of the error is:
> 
> (1) WinGetFuncArgInPartition keep on fetching column x until it's
> evalued to not null and placed in the second row (in this case that's
> x==20). In WinGetFuncArgInPartition WinSetMarkPosition is called at
> abs_pos==3.
> 
> (2) WinGetFuncArgInPartition tries to fetch column y at row 0. Since
> the mark was set to at row 3, the error occurred.
> 
> To avoid the error, we could call WinGetFuncArgInPartition with
> set_mark = false (and call WinSetMarkPosition separately) but I am not
> sure if it's an acceptable solution.

Attached is a v2 patch to fix the "cannot fetch row before
WindowObject's mark position" error, by tweaking the logic to
calculate the set mark position in WinGetFuncArgInPartition.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-13T04:49:48Z

    >> 2. AFAICS there is only one notnull_info array, which amounts to
>> assuming that the window function will have only one argument position
>> that it calls WinGetFuncArgInFrame or WinGetFuncArgInPartition for.
>> That may be true for the built-in functions but it seems mighty
>> restrictive for extensions.  Worse yet, there's no check, so that
>> you'd just get silently wrong answers if two or more arguments are
>> evaluated.  I think there ought to be a separate array for each argno;
>> of course only created if the window function actually asks for
>> evaluations of a particular argno.
> 
> I missed that. Thank you for pointed it out. I agree it would be
> better allow to use multiple argument positions that calls
> WinGetFuncArgInFrame or WinGetFuncArgInPartition in
> extensions. Attached is a PoC patch for that.
> 
> Currently there's an issue with the patch, however.
> 
> SELECT x, y, mywindowfunc2(x, y, 2) IGNORE NULLS OVER w FROM g
> WINDOW w AS (ORDER BY y);
> psql:test2.sql:9: ERROR:  cannot fetch row before WindowObject's mark position
> 
> mywindowfunc2 is a user defined window function, taking 3 arguments. x
> and y are expected to be evaluated to integer. The third argument is
> relative offset to current row. In the query above x and y are
> retrieved using two WinGetFuncArgInPartition() calls. The data set
> (table "g") looks like below.
> 
>  x  | y 
> ----+---
>     | 1
>     | 2
>  10 | 3
>  20 | 4
> (4 rows)
> 
> I think the cause of the error is:
> 
> (1) WinGetFuncArgInPartition keep on fetching column x until it's
> evalued to not null and placed in the second row (in this case that's
> x==20). In WinGetFuncArgInPartition WinSetMarkPosition is called at
> abs_pos==3.
> 
> (2) WinGetFuncArgInPartition tries to fetch column y at row 0. Since
> the mark was set to at row 3, the error occurred.
> 
> To avoid the error, we could call WinGetFuncArgInPartition with
> set_mark = false (and call WinSetMarkPosition separately) but I am not
> sure if it's an acceptable solution.

Attached is a v2 patch to fix the "cannot fetch row before
WindowObject's mark position" error, by tweaking the logic to
calculate the set mark position in WinGetFuncArgInPartition.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-13T05:39:41Z

    >>>> Also the error is certainly user-facing,
>>>> so using elog() was quite inappropriate.  It should be ereport with an
>>>> errcode of (probably) ERRCODE_FEATURE_NOT_SUPPORTED.  Rolling your
>>>> own implementation of get_func_name() wasn't great either.
>>> 
>>> I overlooked the elog() call and "own implementation of
>>> get_func_name()". Will fix.
>> 
>> Attached is a trivial patch to fix that. I am going to push it if
>> there's no objection.
>> 
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS K.K.
>> English: http://www.sraoss.co.jp/index_en/
>> Japanese:http://www.sraoss.co.jp
>> <v1-0001-Use-ereport-rather-than-elog-in-WinCheckAndInitia.patch>
> 
> 
> I just take a quick look at the patch, a tiny comment is:
> 
> ```
> +		char	   *funcname = get_func_name(fcinfo->flinfo->fn_oid);
> ```
> 
> This can be a “const char *”.

Thanks for the review. In addition to the point, I added an assertion
which is called by all other window function API. Also added check to
the return value of get_func_name() because it could return NULL. V2
patch attached.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Álvaro Herrera <alvherre@kurilemu.de> — 2025-10-13T11:22:18Z

    On 2025-Oct-13, Tatsuo Ishii wrote:

> Thanks for the review. In addition to the point, I added an assertion
> which is called by all other window function API. Also added check to
> the return value of get_func_name() because it could return NULL. V2
> patch attached.

Hmm, this change made me realize that all or almost all the calls to
get_func_name() would crash if it were to return a NULL value.  I found
no caller that checks the return value for nullness.  I wonder why do we
allow it to return NULL at all ... it might be better to just
elog(ERROR) if the cache entry is not found.  

I think it was already wrong as introduced by 31c775adeb22.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"La espina, desde que nace, ya pincha" (Proverbio africano)

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-14T00:01:00Z

    > Hmm, this change made me realize that all or almost all the calls to
> get_func_name() would crash if it were to return a NULL value.  I found
> no caller that checks the return value for nullness.  I wonder why do we
> allow it to return NULL at all ... it might be better to just
> elog(ERROR) if the cache entry is not found.  

I agree it's better but what about user defined functions? Some of
them might already check the return value to emit their own error
messages, I don't know. If so, modifying get_func_name() could break
them. Maybe invent something like get_func_name_with_error(calling
elog(ERROR)) and gradually update our code?

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-14T10:21:13Z

    >>>> I overlooked the elog() call and "own implementation of
>>>> get_func_name()". Will fix.
>>> 
>>> Attached is a trivial patch to fix that. I am going to push it if
>>> there's no objection.
>>> 
>>> Best regards,
>>> --
>>> Tatsuo Ishii
>>> SRA OSS K.K.
>>> English: http://www.sraoss.co.jp/index_en/
>>> Japanese:http://www.sraoss.co.jp
>>> <v1-0001-Use-ereport-rather-than-elog-in-WinCheckAndInitia.patch>
>> 
>> 
>> I just take a quick look at the patch, a tiny comment is:
>> 
>> ```
>> +		char	   *funcname = get_func_name(fcinfo->flinfo->fn_oid);
>> ```
>> 
>> This can be a “const char *”.
> 
> Thanks for the review. In addition to the point, I added an assertion
> which is called by all other window function API. Also added check to
> the return value of get_func_name() because it could return NULL. V2
> patch attached.

V2 patch pushed. Thanks.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Michael Paquier <michael@paquier.xyz> — 2025-10-16T06:19:21Z

    On Tue, Oct 14, 2025 at 07:21:13PM +0900, Tatsuo Ishii wrote:
> V2 patch pushed. Thanks.

Coverity thinks that this code has still some incorrect bits, and I
think that it is right to think so even on today's HEAD at
02c171f63fca.

In WinGetFuncArgInPartition()@nodeWindowAgg.c, we have the following
loop (keeping only the relevant parts:
    do
    {
        [...]
        else                    /* need to check NULL or not */
        {
            /* get tuple and evaluate in partition */
            datum = gettuple_eval_partition(winobj, argno,
                                            abs_pos, isnull, &myisout);
            if (myisout)        /* out of partition? */
                break;
            if (!*isnull)
                notnull_offset++;
            /* record the row status */
            put_notnull_info(winobj, abs_pos, *isnull);
        }
    } while (notnull_offset < notnull_relpos);

    /* get tuple and evaluate in partition */
    datum = gettuple_eval_partition(winobj, argno,
                                    abs_pos, isnull, &myisout);

And Coverity is telling that there is no point in setting a datum in
this else condition to just override its value when we exit the while
loop.  To me, it's a sigh that this code's logic could be simplified.

In passing, gettuple_eval_partition() is under-documented for me.  Its
name refers to the fact that it gets a tuple and evaluates a
partition.  Its top comment tells the same thing as the name of the
function, so it's a bit hard to say why it is useful with the code
written this way, and how others many benefit when attempting to reuse
it, or if it even makes sense to reuse it for other purposes.
--
Michael

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-16T10:17:06Z

    Thanks for the report.

> Coverity thinks that this code has still some incorrect bits, and I
> think that it is right to think so even on today's HEAD at
> 02c171f63fca.
> 
> In WinGetFuncArgInPartition()@nodeWindowAgg.c, we have the following
> loop (keeping only the relevant parts:
>     do
>     {
>         [...]
>         else                    /* need to check NULL or not */
>         {
>             /* get tuple and evaluate in partition */
>             datum = gettuple_eval_partition(winobj, argno,
>                                             abs_pos, isnull, &myisout);
>             if (myisout)        /* out of partition? */
>                 break;
>             if (!*isnull)
>                 notnull_offset++;
>             /* record the row status */
>             put_notnull_info(winobj, abs_pos, *isnull);
>         }
>     } while (notnull_offset < notnull_relpos);
> 
>     /* get tuple and evaluate in partition */
>     datum = gettuple_eval_partition(winobj, argno,
>                                     abs_pos, isnull, &myisout);
> 
> And Coverity is telling that there is no point in setting a datum in
> this else condition to just override its value when we exit the while
> loop.  To me, it's a sigh that this code's logic could be simplified.

To fix the issue, I think we can change:

>     datum = gettuple_eval_partition(winobj, argno,
>                                     abs_pos, isnull, &myisout);

to:

     (void) gettuple_eval_partition(winobj, argno,
                                           abs_pos, isnull, &myisout);

This explicitely stats that we ignore the return value from
gettuple_eval_partition. I hope coverity understands this.

> In passing, gettuple_eval_partition() is under-documented for me.  Its
> name refers to the fact that it gets a tuple and evaluates a
> partition.  Its top comment tells the same thing as the name of the
> function, so it's a bit hard to say why it is useful with the code
> written this way, and how others many benefit when attempting to reuse
> it, or if it even makes sense to reuse it for other purposes.

What about changing the comment this way?

/* gettuple_eval_partition
 * get tuple in a patition and evaluate the window function's argument
 * expression on it.
 */

Attached is the patch for above.
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Chao Li <li.evan.chao@gmail.com> — 2025-10-16T11:50:06Z

    
> On Oct 16, 2025, at 18:17, Tatsuo Ishii <ishii@postgresql.org> wrote:
> 
> Thanks for the report.
> 
>> Coverity thinks that this code has still some incorrect bits, and I
>> think that it is right to think so even on today's HEAD at
>> 02c171f63fca.
>> 
>> In WinGetFuncArgInPartition()@nodeWindowAgg.c, we have the following
>> loop (keeping only the relevant parts:
>>    do
>>    {
>>        [...]
>>        else                    /* need to check NULL or not */
>>        {
>>            /* get tuple and evaluate in partition */
>>            datum = gettuple_eval_partition(winobj, argno,
>>                                            abs_pos, isnull, &myisout);
>>            if (myisout)        /* out of partition? */
>>                break;
>>            if (!*isnull)
>>                notnull_offset++;
>>            /* record the row status */
>>            put_notnull_info(winobj, abs_pos, *isnull);
>>        }
>>    } while (notnull_offset < notnull_relpos);
>> 
>>    /* get tuple and evaluate in partition */
>>    datum = gettuple_eval_partition(winobj, argno,
>>                                    abs_pos, isnull, &myisout);
>> 
>> And Coverity is telling that there is no point in setting a datum in
>> this else condition to just override its value when we exit the while
>> loop.  To me, it's a sigh that this code's logic could be simplified.
> 
> To fix the issue, I think we can change:
> 
>>    datum = gettuple_eval_partition(winobj, argno,
>>                                    abs_pos, isnull, &myisout);
> 
> to:
> 
>     (void) gettuple_eval_partition(winobj, argno,
>                                           abs_pos, isnull, &myisout);
> 
> This explicitely stats that we ignore the return value from
> gettuple_eval_partition. I hope coverity understands this.
> 
>> 

I think Coverity is complaining about the redundant call to gettuple_eval_partition().

In the “else” clause, the function is called, then when “if (myisout)” is satisfied, it will break out the while loop. After that, the function is immediately called again, so “datum” is overwritten. But I haven’t spent time thinking about how to fix.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-19T00:38:31Z

    > Thanks for the report.
> 
>> Coverity thinks that this code has still some incorrect bits, and I
>> think that it is right to think so even on today's HEAD at
>> 02c171f63fca.
>> 
>> In WinGetFuncArgInPartition()@nodeWindowAgg.c, we have the following
>> loop (keeping only the relevant parts:
>>     do
>>     {
>>         [...]
>>         else                    /* need to check NULL or not */
>>         {
>>             /* get tuple and evaluate in partition */
>>             datum = gettuple_eval_partition(winobj, argno,
>>                                             abs_pos, isnull, &myisout);
>>             if (myisout)        /* out of partition? */
>>                 break;
>>             if (!*isnull)
>>                 notnull_offset++;
>>             /* record the row status */
>>             put_notnull_info(winobj, abs_pos, *isnull);
>>         }
>>     } while (notnull_offset < notnull_relpos);
>> 
>>     /* get tuple and evaluate in partition */
>>     datum = gettuple_eval_partition(winobj, argno,
>>                                     abs_pos, isnull, &myisout);
>> 
>> And Coverity is telling that there is no point in setting a datum in
>> this else condition to just override its value when we exit the while
>> loop.  To me, it's a sigh that this code's logic could be simplified.
> 
> To fix the issue, I think we can change:
> 
>>     datum = gettuple_eval_partition(winobj, argno,
>>                                     abs_pos, isnull, &myisout);
> 
> to:
> 
>      (void) gettuple_eval_partition(winobj, argno,
>                                            abs_pos, isnull, &myisout);
> 
> This explicitely stats that we ignore the return value from
> gettuple_eval_partition. I hope coverity understands this.
> 
>> In passing, gettuple_eval_partition() is under-documented for me.  Its
>> name refers to the fact that it gets a tuple and evaluates a
>> partition.  Its top comment tells the same thing as the name of the
>> function, so it's a bit hard to say why it is useful with the code
>> written this way, and how others many benefit when attempting to reuse
>> it, or if it even makes sense to reuse it for other purposes.
> 
> What about changing the comment this way?
> 
> /* gettuple_eval_partition
>  * get tuple in a patition and evaluate the window function's argument
>  * expression on it.
>  */
> 
> Attached is the patch for above.

Patch pushed with minor comment tweaks.
Thanks.
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-19T01:02:58Z

    >> On Oct 16, 2025, at 18:17, Tatsuo Ishii <ishii@postgresql.org> wrote:
>> 
>> Thanks for the report.
>> 
>>> Coverity thinks that this code has still some incorrect bits, and I
>>> think that it is right to think so even on today's HEAD at
>>> 02c171f63fca.
>>> 
>>> In WinGetFuncArgInPartition()@nodeWindowAgg.c, we have the following
>>> loop (keeping only the relevant parts:
>>>    do
>>>    {
>>>        [...]
>>>        else                    /* need to check NULL or not */
>>>        {
>>>            /* get tuple and evaluate in partition */
>>>            datum = gettuple_eval_partition(winobj, argno,
>>>                                            abs_pos, isnull, &myisout);
>>>            if (myisout)        /* out of partition? */
>>>                break;
>>>            if (!*isnull)
>>>                notnull_offset++;
>>>            /* record the row status */
>>>            put_notnull_info(winobj, abs_pos, *isnull);
>>>        }
>>>    } while (notnull_offset < notnull_relpos);
>>> 
>>>    /* get tuple and evaluate in partition */
>>>    datum = gettuple_eval_partition(winobj, argno,
>>>                                    abs_pos, isnull, &myisout);
>>> 
>>> And Coverity is telling that there is no point in setting a datum in
>>> this else condition to just override its value when we exit the while
>>> loop.  To me, it's a sigh that this code's logic could be simplified.
>> 
>> To fix the issue, I think we can change:
>> 
>>>    datum = gettuple_eval_partition(winobj, argno,
>>>                                    abs_pos, isnull, &myisout);
>> 
>> to:
>> 
>>     (void) gettuple_eval_partition(winobj, argno,
>>                                           abs_pos, isnull, &myisout);
>> 
>> This explicitely stats that we ignore the return value from
>> gettuple_eval_partition. I hope coverity understands this.
>> 
>>> 
> 
> I think Coverity is complaining about the redundant call to gettuple_eval_partition().
> 
> In the “else” clause, the function is called, then when “if (myisout)” is satisfied, it will break out the while loop. After that, the function is immediately called again, so “datum” is overwritten. But I haven’t spent time thinking about how to fix.

Yes, the function is called again. But I think the cost is cheap in
this case.  Inside the function window_gettupleslot() is called. It
could be costly if it spools tuples. But as tuple is already spooled
by the former call of gettuple_eval_partition(), almost no cost is
needed. We could avoid the redundant call by putting more code after
the former function call to return immediately, or introduce a goto
statement or a flag. But I think they will make the code harder to
read and do not worth the trouble. Others may think differently
though.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-19T09:53:23Z

    >>> 2. AFAICS there is only one notnull_info array, which amounts to
>>> assuming that the window function will have only one argument position
>>> that it calls WinGetFuncArgInFrame or WinGetFuncArgInPartition for.
>>> That may be true for the built-in functions but it seems mighty
>>> restrictive for extensions.  Worse yet, there's no check, so that
>>> you'd just get silently wrong answers if two or more arguments are
>>> evaluated.  I think there ought to be a separate array for each argno;
>>> of course only created if the window function actually asks for
>>> evaluations of a particular argno.
>> 
>> I missed that. Thank you for pointed it out. I agree it would be
>> better allow to use multiple argument positions that calls
>> WinGetFuncArgInFrame or WinGetFuncArgInPartition in
>> extensions. Attached is a PoC patch for that.
>> 
>> Currently there's an issue with the patch, however.
>> 
>> SELECT x, y, mywindowfunc2(x, y, 2) IGNORE NULLS OVER w FROM g
>> WINDOW w AS (ORDER BY y);
>> psql:test2.sql:9: ERROR:  cannot fetch row before WindowObject's mark position
>> 
>> mywindowfunc2 is a user defined window function, taking 3 arguments. x
>> and y are expected to be evaluated to integer. The third argument is
>> relative offset to current row. In the query above x and y are
>> retrieved using two WinGetFuncArgInPartition() calls. The data set
>> (table "g") looks like below.
>> 
>>  x  | y 
>> ----+---
>>     | 1
>>     | 2
>>  10 | 3
>>  20 | 4
>> (4 rows)
>> 
>> I think the cause of the error is:
>> 
>> (1) WinGetFuncArgInPartition keep on fetching column x until it's
>> evalued to not null and placed in the second row (in this case that's
>> x==20). In WinGetFuncArgInPartition WinSetMarkPosition is called at
>> abs_pos==3.
>> 
>> (2) WinGetFuncArgInPartition tries to fetch column y at row 0. Since
>> the mark was set to at row 3, the error occurred.
>> 
>> To avoid the error, we could call WinGetFuncArgInPartition with
>> set_mark = false (and call WinSetMarkPosition separately) but I am not
>> sure if it's an acceptable solution.
> 
> Attached is a v2 patch to fix the "cannot fetch row before
> WindowObject's mark position" error, by tweaking the logic to
> calculate the set mark position in WinGetFuncArgInPartition.

Attached is a v3 patch which is ready for commit IMO.  Major
difference from v2 patch is, now the patch satisfies the request
below.

>>> of course only created if the window function actually asks for
>>> evaluations of a particular argno.

The NOT NULL information array is allocated only when the window
function actually asks for evaluations of a particular argno using
WinGetFuncArgInFrame or WinGetFuncArgInPartition.

If there's no objection, I am going to commit in a few days.
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Chao Li <li.evan.chao@gmail.com> — 2025-10-20T03:22:04Z

    
> On Oct 19, 2025, at 17:53, Tatsuo Ishii <ishii@postgresql.org> wrote:
> 
> 
> If there's no objection, I am going to commit in a few days.
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
> <v3-0001-Fix-multi-WinGetFuncArgInFrame-Partition-calls-wi.patch>

A very trivial commit:

```
+	else
+
+		/*
+		 * For other cases we have no idea what position of row callers would
+		 * fetch next time. Also for relpos < 0 case (we go backward), we
+		 * cannot set mark either. For those cases we always set mark at 0.
+		 */
+		mark_pos = 0;
```

The empty line after “else” is not needed.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-20T03:58:47Z

    > A very trivial commit:
> 
> ```
> +	else
> +
> +		/*
> +		 * For other cases we have no idea what position of row callers would
> +		 * fetch next time. Also for relpos < 0 case (we go backward), we
> +		 * cannot set mark either. For those cases we always set mark at 0.
> +		 */
> +		mark_pos = 0;
> ```
> 
> The empty line after “else” is not needed.

That was added by pgindent.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-22T03:14:11Z

    >>>> 2. AFAICS there is only one notnull_info array, which amounts to
>>>> assuming that the window function will have only one argument position
>>>> that it calls WinGetFuncArgInFrame or WinGetFuncArgInPartition for.
>>>> That may be true for the built-in functions but it seems mighty
>>>> restrictive for extensions.  Worse yet, there's no check, so that
>>>> you'd just get silently wrong answers if two or more arguments are
>>>> evaluated.  I think there ought to be a separate array for each argno;
>>>> of course only created if the window function actually asks for
>>>> evaluations of a particular argno.
>>> 
>>> I missed that. Thank you for pointed it out. I agree it would be
>>> better allow to use multiple argument positions that calls
>>> WinGetFuncArgInFrame or WinGetFuncArgInPartition in
>>> extensions. Attached is a PoC patch for that.
>>> 
>>> Currently there's an issue with the patch, however.
>>> 
>>> SELECT x, y, mywindowfunc2(x, y, 2) IGNORE NULLS OVER w FROM g
>>> WINDOW w AS (ORDER BY y);
>>> psql:test2.sql:9: ERROR:  cannot fetch row before WindowObject's mark position
>>> 
>>> mywindowfunc2 is a user defined window function, taking 3 arguments. x
>>> and y are expected to be evaluated to integer. The third argument is
>>> relative offset to current row. In the query above x and y are
>>> retrieved using two WinGetFuncArgInPartition() calls. The data set
>>> (table "g") looks like below.
>>> 
>>>  x  | y 
>>> ----+---
>>>     | 1
>>>     | 2
>>>  10 | 3
>>>  20 | 4
>>> (4 rows)
>>> 
>>> I think the cause of the error is:
>>> 
>>> (1) WinGetFuncArgInPartition keep on fetching column x until it's
>>> evalued to not null and placed in the second row (in this case that's
>>> x==20). In WinGetFuncArgInPartition WinSetMarkPosition is called at
>>> abs_pos==3.
>>> 
>>> (2) WinGetFuncArgInPartition tries to fetch column y at row 0. Since
>>> the mark was set to at row 3, the error occurred.
>>> 
>>> To avoid the error, we could call WinGetFuncArgInPartition with
>>> set_mark = false (and call WinSetMarkPosition separately) but I am not
>>> sure if it's an acceptable solution.
>> 
>> Attached is a v2 patch to fix the "cannot fetch row before
>> WindowObject's mark position" error, by tweaking the logic to
>> calculate the set mark position in WinGetFuncArgInPartition.
> 
> Attached is a v3 patch which is ready for commit IMO.  Major
> difference from v2 patch is, now the patch satisfies the request
> below.
> 
>>>> of course only created if the window function actually asks for
>>>> evaluations of a particular argno.
> 
> The NOT NULL information array is allocated only when the window
> function actually asks for evaluations of a particular argno using
> WinGetFuncArgInFrame or WinGetFuncArgInPartition.
> 
> If there's no objection, I am going to commit in a few days.

Patch pushed. Thanks.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    David Rowley <dgrowleyml@gmail.com> — 2025-10-22T04:18:31Z

    On Mon, 20 Oct 2025 at 16:59, Tatsuo Ishii <ishii@postgresql.org> wrote:
>
> > A very trivial commit:
> >
> > ```
> > +     else
> > +
> > +             /*
> > +              * For other cases we have no idea what position of row callers would
> > +              * fetch next time. Also for relpos < 0 case (we go backward), we
> > +              * cannot set mark either. For those cases we always set mark at 0.
> > +              */
> > +             mark_pos = 0;
> > ```
> >
> > The empty line after “else” is not needed.
>
> That was added by pgindent.

If it's written down somewhere, I can't find it, but the rule we
normally follow here is; don't use braces if the code block has a
single statement without any comments that appear on a separate line.
Otherwise, use braces.

Since your comments are not on the same line as the statement, it
should have braces. I imagine that's why pgindent is "acting weird".

David

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-22T05:49:11Z

    > On Mon, 20 Oct 2025 at 16:59, Tatsuo Ishii <ishii@postgresql.org> wrote:
>>
>> > A very trivial commit:
>> >
>> > ```
>> > +     else
>> > +
>> > +             /*
>> > +              * For other cases we have no idea what position of row callers would
>> > +              * fetch next time. Also for relpos < 0 case (we go backward), we
>> > +              * cannot set mark either. For those cases we always set mark at 0.
>> > +              */
>> > +             mark_pos = 0;
>> > ```
>> >
>> > The empty line after “else” is not needed.
>>
>> That was added by pgindent.
> 
> If it's written down somewhere, I can't find it, but the rule we
> normally follow here is; don't use braces if the code block has a
> single statement without any comments that appear on a separate line.
> Otherwise, use braces.

Oh ok, I didn't know that.

> Since your comments are not on the same line as the statement, it
> should have braces. I imagine that's why pgindent is "acting weird".

Attached is a trivial patch to follow the rule.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

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

    Tatsuo Ishii <ishii@postgresql.org> — 2025-10-23T02:06:59Z

    >> On Mon, 20 Oct 2025 at 16:59, Tatsuo Ishii <ishii@postgresql.org> wrote:
>>>
>>> > A very trivial commit:
>>> >
>>> > ```
>>> > +     else
>>> > +
>>> > +             /*
>>> > +              * For other cases we have no idea what position of row callers would
>>> > +              * fetch next time. Also for relpos < 0 case (we go backward), we
>>> > +              * cannot set mark either. For those cases we always set mark at 0.
>>> > +              */
>>> > +             mark_pos = 0;
>>> > ```
>>> >
>>> > The empty line after “else” is not needed.
>>>
>>> That was added by pgindent.
>> 
>> If it's written down somewhere, I can't find it, but the rule we
>> normally follow here is; don't use braces if the code block has a
>> single statement without any comments that appear on a separate line.
>> Otherwise, use braces.
> 
> Oh ok, I didn't know that.
> 
>> Since your comments are not on the same line as the statement, it
>> should have braces. I imagine that's why pgindent is "acting weird".
> 
> Attached is a trivial patch to follow the rule.

Patch pushed. Thanks.
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp