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/04 12:20:46 UTC
[PR] Fix join condition lost after pull up sublink to join [cloudberry]
Alena0704 opened a new pull request, #1960:
URL: https://github.com/apache/cloudberry/pull/1960
Fix join condition lost after pull up sublink to join
After pulling up the sublink to join, the raw join condition may get
lost in the rewritten query, potentially leading to incorrect results.
Within the SubqueryToJoinWalker() function, we address this issue
by adding an 'else' branch to prevent the loss of join clauses and
keep them in their original positions.
(cherry picked from open-gpdb commit c06d16b86316e1d66182f5c75a6c3abe6c30acd4)
### 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
- [ ] Passed `make installcheck`
- [ ] 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
- [x] Followed [contribution guide](https://cloudberry.apache.org/contribute/code)
- [ ] Added/updated documentation
- [ ] Reviewed code for security implications
- [ ] This PR contains AI-assisted code generation
- [x] 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: [dev@cloudberry.apache.org](https://lists.apache.org/list.html?dev@cloudberry.apache.org) (subscribe: dev-subscribe@cloudberry.apache.org)
- 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: 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
Re: [PR] Fix join condition lost after pull up sublink to join [cloudberry]
Posted by "Alena0704 (via GitHub)" <gi...@apache.org>.
Alena0704 commented on PR #1960:
URL: https://github.com/apache/cloudberry/pull/1960#issuecomment-5540388135
When a correlated aggregate subquery is pulled up into a join, `SubqueryToJoinWalker()` records only two kinds of quals: non-correlated branches found *inside* an AND `BoolExpr`, and correlated equality `OpExpr`s that become the grouping key. A single non-correlated qual matches neither branch — it falls through to the final `return` and is recorded
nowhere. `RemoveInnerJoinQuals()` then clears `je->quals`, and `subselect->jointree->quals` is overwritten with what the walker collected, so the predicate is gone from the rewritten query.
A one-predicate `JOIN ... ON` clause is exactly such a qual, so its condition is silently dropped and the aggregate is computed over an unrestricted join:
```sql
set optimizer = off;
create table o (a int, d int); insert into o values (2, 1);
create table i1(a int); insert into i1 values (1);
create table i2(a int); insert into i2 values (1), (2);
select * from o where o.a > (select max(i2.a) from i1 join i2 on i2.a = i1.a where i1.a = o.d);
```
```
postgres=# create table o (a int, d int); insert into o values (2, 1);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
INSERT 0 1
postgres=# create table i1(a int); insert into i1 values (1);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
INSERT 0 1
postgres=# create table i2(a int); insert into i2 values (1), (2);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
INSERT 0 2
postgres=#
postgres=# -- returns (2,1) as a SubPlan, returns nothing when pulled up
postgres=# select * from o where o.a > (select max(i2.a) from i1 join i2 on i2.a = i1.a where i1.a = o.d);
a | d
---+---
2 | 1
(1 row)
postgres=# set optimizer = off;
SET
postgres=# select * from o where o.a > (select max(i2.a) from i1 join i2 on i2.a = i1.a where i1.a = o.d);
a | d
---+---
(0 rows)
```
`i1.a = 1`, so `ON` leaves only `i2.a = 1` and the subquery is `1`; `2 > 1` holds and the row must be returned. Instead the query returns nothing, because a `Nested Loop` with no `Join Cond` producing 2 rows instead of 1, which makes `max` come out as 2.
The fix adds an `else` branch that keeps such quals where they were. Only the Postgres planner is affected (`optimizer=off`, or an ORCA fallback); ORCA does its own decorrelation and preserves the `ON` condition.
--
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
Re: [PR] Fix join condition lost after pull up sublink to join [cloudberry]
Posted by "leborchuk (via GitHub)" <gi...@apache.org>.
leborchuk merged PR #1960:
URL: https://github.com/apache/cloudberry/pull/1960
--
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