Alena0704 opened a new issue, #2046:
URL: https://github.com/apache/cloudberry/issues/2046
### Apache Cloudberry version
main branch, REL_2_STABLE
### What happened
With GPORCA (`optimizer = on`), a correlated scalar subquery with an
aggregate returns NULL for outer rows that have no matching inner rows. That is
only correct for aggregates whose value on empty input is NULL. GPORCA
special-cases `count` only, so other aggregates that return a non-NULL value on
empty input come out wrong:
- `regr_count` (empty input → `0`)
- hypothetical-set aggregates `rank` / `dense_rank` / `percent_rank` /
`cume_dist` ... `WITHIN GROUP` (→ `1` / `1` / `0` / `1`)
- any user-defined aggregate with a non-NULL `initcond`
The Postgres planner (`optimizer = off`) returns the correct values for the
same query.
### What you think should happen instead
A correlated aggregate subquery with no matching rows should return the
aggregate's value on empty input, just as it does when run on its own (`...
WHERE false`) and as the Postgres planner returns it.
### How to reproduce
```sql
create table t1(a int, b int, d int);
insert into t1 values (3,1,1),(1,9,5),(0,2,7),(5,5,1),(2,4,9);
create table t2(a int, b int);
insert into t2 values (1,10),(2,20),(1,30);
analyze t1; analyze t2;
create aggregate sum_from_zero(int) (sfunc = int4pl, stype = int4, initcond
= '0');
-- values on empty input
select regr_count(a,b), rank(5) within group (order by b), sum_from_zero(a)
from t2 where false;
-- regr_count | rank | sum_from_zero
-- ------------+------+---------------
-- 0 | 1 | 0
set optimizer = on;
select a, d,
(select count(*) from t2 where t2.a = t1.d)
as cnt,
(select regr_count(t2.a,t2.b) from t2 where t2.a = t1.d)
as regr,
(select rank(5) within group (order by t2.b) from t2 where t2.a =
t1.d) as rnk,
(select sum_from_zero(t2.a) from t2 where t2.a = t1.d)
as sfz
from t1 order by 1,2;
```
Result with `optimizer = on`:
```
a | d | cnt | regr | rnk | sfz
---+---+-----+------+-----+-----
0 | 7 | 0 | | | <-- WRONG, expected 0 | 1 | 0
1 | 5 | 0 | | | <-- WRONG
2 | 9 | 0 | | | <-- WRONG
3 | 1 | 2 | 2 | 1 | 2
5 | 1 | 2 | 2 | 1 | 2
```
Result with `optimizer = off` (correct):
```
a | d | cnt | regr | rnk | sfz
---+---+-----+------+-----+-----
0 | 7 | 0 | 0 | 1 | 0
1 | 5 | 0 | 0 | 1 | 0
2 | 9 | 0 | 0 | 1 | 0
3 | 1 | 2 | 2 | 1 | 2
5 | 1 | 2 | 2 | 1 | 2
```
### Operating System
any
### Anything else
The same subquery in `WHERE` loses the no-match rows, because it gets turned
into an inner join:
```sql
select a,b,d from t1 where t1.a > (select regr_count(t2.a,t2.b) from t2
where t2.a = t1.d) order by 1,2,3;
-- returns 2 rows (3|1|1, 5|5|1); expected 4 (also 1|9|5 and 2|4|9)
```
### Are you willing to submit PR?
- [x] 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]
