Alena0704 opened a new issue, #2047:
URL: https://github.com/apache/cloudberry/issues/2047
### Apache Cloudberry version
main; REL_2_STABLE
### What happened
With GPORCA (`optimizer = on`), a correlated scalar subquery that combines
an aggregate with a window function returns a wrong value. The subquery has no
`GROUP BY`, so it produces exactly one row per outer row, and `count(*) over
()` inside it must be `1`. GPORCA decorrelates the subquery into a
`GroupAggregate` grouped by the correlation column and puts the `WindowAgg` on
top. The window then runs over all groups instead of the single aggregate row.
This gives a wrong value in the target list and wrong rows in `WHERE`. The
Postgres planner (`optimizer = off`) computes the target-list form correctly
(see below for `WHERE`).
### What you think should happen instead
_No response_
### How to reproduce
```sql
create table w1(a int, b int); insert into w1 values (4,1);
create table w2(a int); insert into w2 values (1),(1),(2);
analyze w1; analyze w2;
set optimizer = on;
select a, b, (select sum(w2.a) + count(*) over () from w2 where w2.a = w1.b)
from w1;
-- a | b | ?column?
-- ---+---+----------
-- 4 | 1 | 4 <-- WRONG, expected 3 (sum = 2, count(*) over () =
1)
select a, b from w1 where w1.a > (select sum(w2.a) + count(*) over () from
w2 where w2.a = w1.b);
-- (0 rows) <-- WRONG, expected 4 | 1
```
With `optimizer = off`, the target-list query returns the expected `4 | 1 |
3`. The `WHERE` query is correct on main only when the pull-up is guarded
against window functions (#1933). On REL_2_STABLE (6fb99be10ed), the Postgres
planner also returns 0 rows for it.
Plan with `optimizer = on`:
```
explain (costs off)
select a, b from w1 where w1.a > (select sum(w2.a) + count(*) over () from
w2 where w2.a = w1.b);
Hash Join
Join Filter: (w1.a > ((sum(w2.a)) + count(*) OVER (?)))
-> WindowAgg
-> GroupAggregate
Group Key: w2.a <-- window is computed across all
groups
```
`SET optimizer = off;` fixes the target-list form. On REL_2_STABLE it does
not fix the `WHERE` form.
### Operating System
any
### Anything else
_No response_
### Are you willing to submit PR?
- [ ] Yes, I am willing to submit a PR!
### Code of Conduct
- [x] I agree to follow this project's [Code of
Conduct](https://github.com/apache/cloudberry/blob/main/CODE_OF_CONDUCT.md).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
