Re: lag() default value ignored for some window partition depending on table records count?

On Tue, Apr 26, 2022 at 3:22 PM Loïc Revest <l(dot)revest(at)apc(dot)fr> wrote:

>
> - For every of this date/numeric couple within their "window
> partitioning", we need to determine the preceding value, thus the use of
> lag(<value>, 1, 0::numeric), since it's relevant for us here to get the
> very first record of the partition having "0.00" as its preceding value;
>

I'm a bit out of my league on the promises that window functions give with
respect to qual pushdown and the like (and your nested view structure's
impact on that): but the fact that your window doesn't do partitioning
would seem to be a factor here. It is perfectly fine for lag to return a
null if, in this example, the previous peer_id's LAST graph.date has a null
value for graph.agg_points and that is computed before removing all peer_id
values except the one the in the query where clause.

- Except that the "first" row of some partitions gets NULL instead of
> 0::numeric as the result of lag(<value>, 1, 0::numeric), while other get
> 0::numeric as expected;
> - Things get stranger when the table containing the data get "purged" from
> every record except those corresponding to the "window partition" whose
> first record gets NULL as lag() output: now it also gets 0::numeric...
>
> - "Odd" behavior may be seen for particular window partition when row
> count ∼ 600k, but not when only relevant rows (58 records) are kept;
>

Exactly, your under-specified window clause in the view doesn't match up
with the usage. It seems like operator-error to me. You want the first
record to be relative to the final output, which is per-id, but the window
doesn't actually compute that.

David J.