ORCA: don't push LOJ ON-pred onto its own outer in PushThruOuterChild (#1836)
* ORCA: don't emit NIJ ON-pred edges into the Select above DPv2 join trees
When two non-inner joins in an NAry join have structurally identical ON
predicates, ORCA could silently drop or misplace predicates, producing
wrong results:
select x.c1, y2.c1 from x left join y1 on x.c1
left join y2 on x.c1
where y2.c1 is null;
returned 0 rows instead of the two null-padded FALSE rows, because a
copy of the ON pred ended up as a scan filter on x.
Root cause: CJoinOrderDPv2's m_expression_to_edge_map is keyed on
structural equality (CExpression::HashValue / CUtils::Equals). With two
structurally identical ON preds, RecursivelyMarkEdgesAsUsed can only
ever mark one of the duplicate edges as used, so
AddSelectNodeForRemainingEdges treated the other edge as a leftover
WHERE predicate and emitted it into a Select on top of the join tree.
The normalizer then legitimately pushed that Select onto the LOJ's own
outer child, filtering out rows that outer-join semantics require to be
null-padded. (The map is only populated when a WHERE predicate
references an NIJ right child, which is why the WHERE clause is needed
to trigger the bug.)
Fix at the source: skip ON-pred edges (m_loj_num > 0) when collecting
remaining edges. An NIJ's ON predicate is always applied by the join
itself when its right child is placed (IsRightChildOfNIJ), so an
"unused" ON-pred edge can only be a bookkeeping artifact of the
structural-equality map and must never be duplicated above the join.
An earlier attempt fixed this downstream, by stripping conjuncts that
structurally match the LOJ's ON pred in CNormalizer::PushThruOuterChild.
That layer cannot distinguish the leaked ON-pred copy from legitimate,
structurally identical conjuncts arriving from above, and silently
deleted user predicates:
select * from x left join y on x.c1 where x.c1; -- 3 rows, not 1
select 1 from a t1
left join (a t2 left join a t3 on t2.id = 1)
on t2.id = 1; -- lost the
-- Index Cond on
-- t2 and the ON
-- pred entirely
With this fix, the original repro returns the correct 2 rows with no
scan filter on x, the queries above return planner-identical results,
and the nested-LOJ query regains Index Cond: (id = 1) on t2.
Add the repro as a regression test in bfv_joins.Apache Cloudberry (Incubating), created by the original developers of Greenplum Database, is one advanced and mature open-source Massively Parallel Processing (MPP) database, which evolves from the open-source version of the Pivotal Greenplum Database®️ but features a newer PostgreSQL kernel and more advanced enterprise capabilities. It can serve as a data warehouse and can also be used for large-scale analytics and AI/ML workloads.
You can follow these guides to build Cloudberry on Linux OS (including RHEL/Rocky Linux, and Ubuntu) and macOS.
Welcome to try out Cloudberry via building one Docker-based Sandbox, which is tailored to help you gain a basic understanding of Cloudberry's capabilities and features.
This is the main repository for Apache Cloudberry (Incubating). Alongside this, there are several ecosystem repositories for Cloudberry, including the website, extensions, connectors, adapters, and other utilities.
We have many channels for community members to discuss, ask for help, feedback, and chat:
| Type | Description |
|---|---|
| Slack | Click to Join the real-time chat on Slack for QA, Dev, Events, and more. Don't miss out! Check out the Slack guide to learn more. |
| Q&A | Ask for help when running/developing Cloudberry, visit GitHub Discussions - QA. |
| New ideas / Feature Requests | Share ideas for new features, visit GitHub Discussions - Ideas. |
| Report bugs | Problems and issues in Apache Cloudberry core. If you find bugs, welcome to submit them here. |
| Report a security vulnerability | View our security policy to learn how to report and contact us. |
| Community events | Including meetups, webinars, conferences, and more events, visit the Events page and subscribe to the events calendar. |
| Documentation | Official documentation for Cloudberry. You can explore it to discover more details about us. |
Contributions can be diverse, such as code enhancements, bug fixes, feature proposals, documents, marketing, and so on. No contribution is too small, we encourage all types of contributions. Cloudberry community welcomes contributions from anyone, new and experienced! Our contribution guide will help you get started with the contribution.
| Type | Description |
|---|---|
| Code contribution | Learn how to contribute code to the Cloudberry, including coding preparation, conventions, workflow, review, and checklist following the code contribution guide. |
| Submit the proposal | Proposing major changes to Cloudberry through proposal guide. |
| Doc contribution | We need you to join us to help us improve the documentation, see the doc contribution guide. |
| AI guidline | For AI-assisted development, please review our AI guideline for advice on responsible AI usage. |
You can check our Cloudberry Roadmap out to see the product plans and goals we want to achieve. Welcome to share your thoughts and ideas to join us in shaping the future of Apache Cloudberry (Incubating). (We will update the Roadmap after entering the Incubator.)
Thanks to PostgreSQL, Greenplum Database and other great open source projects to make Apache Cloudberry has a sound foundation.
Cloudberry is licensed under the Apache License, Version 2.0. For details, see the LICENSE.
Apache Cloudberry is an effort undergoing incubation at The Apache Software Foundation (ASF), sponsored by the Apache Incubator. Incubation is required for all newly accepted projects until a further review indicates that the infrastructure, communications, and decision making process have stabilized in a manner consistent with other successful ASF projects. While incubation status is not necessarily a reflection of the completeness or stability of the code, it does indicate that the project has yet to be fully endorsed by the ASF.