Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4.6, 10.2, 10.3, 10.4
-
None
Description
Consider this query:
with t(a, b) as (values (1, 1), (2, 1), (3, 2), (4, 2))
|
select
|
a,
|
sum(a) over () b,
|
sum(a) over (partition by b) c,
|
sum(a) over (order by a rows between unbounded preceding and 1 preceding) d
|
from t
|
order by a;
|
The expected result is:
a|b |c|d|
|
-|--|-|-|
|
1|10|3| |
|
2|10|3|1|
|
3|10|7|3|
|
4|10|7|6|
|
The actual result is:
a|b |c|d|
|
-|--|-|-|
|
1|10|3|2|
|
2|10|3| |
|
3|10|7|3|
|
4|10|7|6|
|
This can be illustrated when not projecting column c, in case of which the problem goes away:
with t(a, b) as (values (1, 1), (2, 1), (3, 2), (4, 2))
|
select
|
a,
|
sum(a) over () b,
|
-- sum(a) over (partition by b) c,
|
sum(a) over (order by a rows between unbounded preceding and 1 preceding) d
|
from t
|
order by a;
|
The result is now correct:
a|b |d|
|
-|--|-|
|
1|10| |
|
2|10|1|
|
3|10|3|
|
4|10|6|
|