You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by "Alena0704 (via GitHub)" <gi...@apache.org> on 2026/09/23 18:52:37 UTC
[I] [Bug] Wrong results: GPORCA returns NULL instead of the empty-input value for correlated aggregate subqueries other than count [cloudberry]
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: commits-unsubscribe@cloudberry.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] [Bug] Wrong results: GPORCA returns NULL instead of the empty-input value for correlated aggregate subqueries other than count [cloudberry]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #2046:
URL: https://github.com/apache/cloudberry/issues/2046#issuecomment-5800967908
Hi, @Alena0704 welcome!🎊 Thanks for taking the time to point this out.🙌
--
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: commits-unsubscribe@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org