Can anyone explain this?
This is certainly a bug, a simpler repro is
SELECT grp,
val,
sum(val) over wf as correct_result,
nullif(sum(val) over wf, -1) as wrong_result,
sum(val) over () as equivalent_result
FROM (VALUES
(1, 1),
(1, 3),
(2, 10),
(2, 20)
) V(grp, val)
window wf as (partition by grp order by val rows between current row and current row);
which returns
grp | val | correct_result | wrong_result | equivalent_result |
---|---|---|---|---|
1 | 1 | 1 | 34 | 34 |
1 | 3 | 3 | 34 | 34 |
2 | 10 | 10 | 34 | 34 |
2 | 20 | 20 | 34 | 34 |
From which I infer something goes wrong with this particular permutation of features and it just ends up treating it as OVER()
and ignoring the rest of the window specification. (i.e. the partition
, order by
and rows
clause in this case)