The leadInFrame/lagInFrame return random values when used with a Nullable column.
How to reproduce
Vitaliy Zakaznikov, [07.06.21 11:22]
user-host :) select number, leadInFrame(number) OVER () AS lead FROM values('number Nullable(Int8)', (1),(1),(2),(3))
SELECT
number,
leadInFrame(number) OVER () AS lead
FROM values('number Nullable(Int8)', 1, 1, 2, 3)
Query id: ad10e785-b9c0-45cd-8c33-3bea552aad0c
┌─number─┬─lead─┐
│ 1 │ 90 │
│ 1 │ 45 │
│ 2 │ 15 │
│ 3 │ 0 │
└────────┴──────┘
4 rows in set. Elapsed: 0.004 sec.
user-host :)
user-host :) select number, leadInFrame(number,1,0) OVER () AS lead FROM values('number Nullable(Int8)', (1),(1),(2),(3))
SELECT
number,
leadInFrame(number, 1, 0) OVER () AS lead
FROM values('number Nullable(Int8)', 1, 1, 2, 3)
Query id: 85ce430a-44ce-4172-98ca-f1ea08878e9a
┌─number─┬─lead─┐
│ 1 │ 90 │
│ 1 │ 45 │
│ 2 │ 15 │
│ 3 │ 0 │
└────────┴──────┘
4 rows in set. Elapsed: 0.004 sec.
user-host :)
user-host :) select number, lagInFrame(number,1,0) OVER () AS lead FROM values('number Nullable(Int8)', (1),(1),(2),(3)
SELECT
number,
lagInFrame(number, 1, 0) OVER () AS lead
FROM values('number Nullable(Int8)', 1, 1, 2, 3)
Query id: 200ec588-c922-4a7e-a0a8-94c3c1d29c4f
┌─number─┬─lead─┐
│ 1 │ 0 │
│ 1 │ -48 │
│ 2 │ 90 │
│ 3 │ 45 │
└────────┴──────┘
4 rows in set. Elapsed: 0.002 sec.
user-host :)
The
leadInFrame/lagInFramereturn random values when used with a Nullable column.How to reproduce