Alena0704 opened a new pull request, #1928:
URL: https://github.com/apache/cloudberry/pull/1928
Keep no-match rows when pulling up a correlated aggregate subquery
With the Postgres planner (optimizer=off or an ORCA fallback), a correlated
scalar subquery with an aggregate is pulled up into an INNER join with a
grouped subquery (convert_EXPR_to_join), which drops outer rows that have no
match. The original subquery keeps them: it computes the aggregate over empty
input, so e.g. COUNT yields 0 there:
select ... from t1
where t1.a > (select count(*) from t2 where t2.a = t1.d);
A row with no match in t2 must be compared as "t1.a > 0" and can pass, but
the INNER join dropped it.
To fix this, pull the subquery up into a LEFT join, so no-match rows survive
as null-extended rows, and rewrite the comparison to return the same value the
subquery would:
outer OP CASE WHEN match_flag THEN expr ELSE empty_input_default END
match_flag is a constant TRUE column added to the subquery. For a matched
row the CASE returns the real expression; for a null-extended row the flag is
NULL and the CASE returns the empty-input default (0 for COUNT, NULL for other
aggregates).
The comparison runs above the LEFT join as a filter, not as the join
condition: as a join qual it would null-extend matched rows that fail it, and
the default would let them back in.
The LEFT join is not always needed. If a no-match row cannot pass the
comparison anyway -- e.g. "1 = (select count(*) ...)" turns into "1 = 0" for it
-- dropping it is fine and the INNER join is kept as before. This is detected
by substituting the empty-input default into the comparison and
constant-folding it. Ordinary sum/avg/min/max comparisons fall into this group:
their empty-input value is NULL, and a comparison with NULL does not pass, so
those plans do not change.
If the comparison cannot be placed above the join (the sublink is in an
outer join's ON clause) or the subquery's targetlist is correlated, the pull-up
bails out and the sublink runs as a SubPlan, as before.
Adapted from https://github.com/open-gpdb/gpdb/pull/397.
Co-Authored-By: excaliiibur
[[email protected]](mailto:[email protected])
Reproduction the bug:
```
postgres=# drop table t_out;
DROP TABLE
postgres=# drop table t_in;
DROP TABLE
postgres=# create table t_out as select 1 as a distributed by (a);
SELECT 1
postgres=# create table t_in as select 2 as a distributed by (a);
SELECT 1
postgres=# set optimizer=0;
SET
postgres=# select * from t_out where a > (select count(*) from t_in where
t_in.a = t_out.a);
a
---
(0 rows)
postgres=# set optimizer=1;
SET
postgres=# select * from t_outwhere a > (select count(*) from t_in where
t_in.a = t_out.a);
a
---
1
(1 row)
```
Fixes #ISSUE_Number
### What does this PR do?
<!-- Brief overview of the changes, including any major features or fixes -->
### Type of Change
- [x] Bug fix (non-breaking change)
- [ ] New feature (non-breaking change)
- [ ] Breaking change (fix or feature with breaking changes)
- [ ] Documentation update
### Breaking Changes
<!-- Remove if not applicable. If yes, explain impact and migration path -->
### Test Plan
<!-- How did you test these changes? -->
- [ ] Unit tests added/updated
- [ ] Integration tests added/updated
- [x] Passed `make installcheck`
- [x] Passed `make -C src/test installcheck-cbdb-parallel`
### Impact
<!-- Remove sections that don't apply -->
**Performance:**
<!-- Any performance implications? -->
**User-facing changes:**
<!-- Any changes visible to users? -->
**Dependencies:**
<!-- New dependencies or version changes? -->
### Checklist
- [ ] Followed [contribution
guide](https://cloudberry.apache.org/contribute/code)
- [ ] Added/updated documentation
- [ ] Reviewed code for security implications
- [ ] This PR contains AI-assisted code generation
- [ ] Requested review from [cloudberry
committers](https://github.com/orgs/apache/teams/cloudberry-committers)
### Additional Context
<!-- Any other information that would help reviewers? Remove if none -->
### CI Skip Instructions
<!--
To skip CI builds, add the appropriate CI skip identifier to your PR title.
The identifier must:
- Be in square brackets []
- Include the word "ci" and either "skip" or "no"
- Only use for documentation-only changes or when absolutely necessary
-->
---
<!-- Join our community:
- Mailing list:
[[email protected]](https://lists.apache.org/[email protected])
(subscribe: [email protected])
- Discussions: https://github.com/apache/cloudberry/discussions -->
--
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]
