Details
-
Type:
Bug
-
Status: In Review (View Workflow)
-
Priority:
Major
-
Resolution: Unresolved
-
Affects Version/s: 10.2, 10.3, 10.4.6, 10.4
-
Component/s: Optimizer - Window functions
-
Labels: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|
|