Re: BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f

Hi Andrey,

> Added in v4.

I looked at v4.

This is how I read the old code. It assumes the current row is in
the frame. Then EXCLUDE TIES leaves only the current row of the
part of its peer group that overlaps the frame, so the arithmetic
keeps room for it: on the head side the first overlapping position
is remapped to currentpos, and the positions after it are shifted
by (grouptailpos - overlapstart - 1). The tail side is the mirror
image, with overlapend - 1 and (overlapend - 1 - groupheadpos).

The "- 1" is there because the current row is taken to be inside
this overlap.

But what if the current row is before the frame head (or, seeking
from the tail, at or after the frame tail)? Then the current row is
not part of the overlap, so there is no reason to subtract 1. The
old code uses the same arithmetic anyway: the remap points outside
the frame, and the shift falls one row short.

So in that case, without the remap and the "- 1", what is left is
exactly the EXCLUDE GROUP formula, on both the head and the tail
side. Working it out from the "- 1", I end up where your first
mail did: when the current row is not in the frame, EXCLUDE TIES
removes the same rows as EXCLUDE GROUP, so it should skip the
overlap the same way.

On backpatching: v4 applies cleanly to REL_14_STABLE through
REL_19_STABLE, and "make check" passes on all of them.

The fix looks correct to me. I'll mark it Ready for Committer.

Regards,
Henson