Обсуждение: lag() default value ignored for some window partition depending on table records count?

=?UTF-8?B?TG/Dr2MgUmV2ZXN0?= <l.revest@apc.fr> writes:
> Long story short:
> - We track history data in a partitioned table which boils down to  "key
> fields + a JSONB data column", the latter being a list of date/numeric
> values pair ;
> - 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;
> - 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;

Hm ...

> Attached is the test case database script we've been able to reproduce the
> "issue" one hundred percent of the time on, but without the data - even
> after compression this weights ∼8.5Mb. Data that I'd happily share would
> anyone points me towards the customary way to do it here with such "big" a
> file.

With no data, this is pretty pointless.  Maybe you could make a script
to fill the tables with some dummy data that reproduces the problem?

            regards, tom lane

On Wed, 27 Apr 2022 at 10:48, David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Tue, Apr 26, 2022 at 3:22 PM Loïc Revest <l.revest@apc.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
partitionhaving "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
yournested view structure's impact on that): but the fact that your window doesn't do partitioning would seem to be a
factorhere.  It is perfectly fine for lag to return a null if, in this example, the previous peer_id's LAST graph.date
hasa null value for graph.agg_points and that is computed before removing all peer_id values except the one the in the
querywhere clause. 

There can only be qual pushdown when the column in the qual is present
in the PARTITION BY clause. In this case, there's no PARTITION BY
clause, so can't be pushdowns.

And yeah, due to lack of a PARTITION BY, LAG's default value is only
applied to exactly 1 row, the one with the lowest peer_id and
graph.date.  The example one showing the NULL is peer_id 10043, and
one showing 0.0 is peer_id 10015. So 10043 is certainly not the lowest
peer_id in the recordset. That indicates the NULL is not from the LAG
but actually just a NULL returned by jsonb_to_recordset().

Loïc, If the required behaviour is to replace NULL with 0.0, then
COALESCE is what you need.

David

>> On Wed, 27 Apr 2022 at 10:48, David G. Johnston
>> <david.g.johnston@gmail.com> wrote:
>>
>> There can only be qual pushdown when the column in the qual is present
>> in the PARTITION BY clause. In this case, there's no PARTITION BY
>> clause, so can't be pushdowns.
>>
peer_id being only used as a sort criterion instead of the
partitioning factor is indeed the culprit, and a true shame I haven't
figured it out on my own.

lag(<value>, 1, 0::numeric) OVER (PARTITION BY peer_id ORDER BY
calculation_time) returns 0::numeric for first row in partition, as
expected.

Thanks a lot to all three of you for pointing it out to me, and my
apologies for the unrelevant noise on that list.

Regards,
L. Revest