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)