Re: first_value()/nth_value() with IGNORE NULLS loop forever (EXCLUDE GROUP/TIES, frame past partition end, no ORDER BY)

> The following query succeeded before this fix but fails afterward.
> Is this intentional?
>
> WITH t AS MATERIALIZED (
> SELECT NULLIF(i, 1) / (2 - i) AS v
> FROM generate_series(1, 2) AS g(i)
> )
> SELECT first_value(v) IGNORE NULLS OVER (ROWS CURRENT ROW)
> FROM t
> LIMIT 1;

Not intentional. I think it just happened to work by chance. The
outer node (WITH t AS MATERIALIZED ... FROM generate_series(1, 2)...)
raises an error (division by zero) as soon as the second row is
fetched.

When the window aggregate node (SELECT first_value(v) IGNORE NULLS
OVER (ROWS CURRENT ROW) FROM t) is executed, the outer node provides
the first row with v = NULL. So the window agg node tries to fetch the
second row to find out a non NULL row.

Previously we called row_is_in_frame() to determine if the second row
is in the frame without fetching the row in this
case. row_is_in_frame() determined that the second row is out of frame
because the framing option is "ROWS CURRENT ROW", meaning only the
current row (in this case the first row) is within a frame. Thus it
gave up searching further rows and return NULL without an error.

After the fix, before calling row_is_in_frame(), we call
spool_tuples() which tries to read rows from outer node to check
whether we hit the partition end. In the case above, we want to read
up to the second row. Unfortunately it raises the error in the outer
node.

I think this kind of behavior (reading beyond frame end) is not
specific to window functions with IGNORE NULLLS. Consider following
query:

WITH t AS MATERIALIZED (
SELECT NULLIF(i, 1) / (2 - i) AS v
FROM generate_series(1, 2) AS g(i)
)
SELECT nth_value(v,2) RESPECT NULLS OVER (ROWS CURRENT ROW)
FROM t
LIMIT 1;

This throws an error:
ERROR: division by zero

The window agg node tries to fetch the second row to evaluate
nth_value(v,2) and fails. One may ask why it fetches the second row
even if it is out of frame. In my understanding to determine whether a
row is in frame, we need to fetch the row first. We need to check the
tuple content in some frame options.

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