Fix JOIN motion type selection for join quals containing outer refs (#1895)
In some cases planner failed to mark join qual restrict clauses as ones referring outer query levels. This makes join motion planning code to make wrong query: it uses redistribute motion in cases it shouldn't.
In this simple reproduces this leads to motion plan node rescan (this is execute-time ERROR normally). Note that for `generate_series(1,1) t1` it would work, because our parametrized plan would execute only once.
```
CREATE TABLE t2(i int);
CREATE TABLE t3(i int);
INSERT INTO t2 SELECT generate_series(1,10);
INSERT INTO t3 SELECT generate_series(1,10);
select * from generate_series(1,2) t1, lateral (select t3.i from t2 join t3 on t2.i = t3.i + t1 order by 1) z;
ERROR: illegal rescan of motion node: invalid plan (nodeMotion.c:1368) (seg1 slice1 127.0.1.1:7003 pid=879693) (nodeMotion.c:1368)
HINT: Likely caused by bad NL-join, try setting enable_nestloop to off
reshke=# explain select * from generate_series(1,1) t1, lateral (select t3.i from t2 join t3 on t2.i = t3.i + t1 order by 1) z;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=10000623579.03..10000785868.61 rows=9273690 width=8)
-> Nested Loop (cost=10000623579.03..10000662219.41 rows=3091230 width=8)
-> Function Scan on generate_series t1 (cost=0.00..0.01 rows=1 width=4)
-> Materialize (cost=623579.02..646763.25 rows=3091230 width=4)
-> Sort (cost=623579.02..631307.10 rows=3091230 width=4)
Sort Key: t3.i
-> Hash Join (cost=756.25..290348.30 rows=3091230 width=4)
Hash Cond: ((t3.i + t1.t1) = t2.i)
-> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..997.00 rows=32100 width=4)
Hash Key: (t3.i + t1.t1)
-> Seq Scan on t3 (cost=0.00..355.00 rows=32100 width=4)
-> Hash (cost=355.00..355.00 rows=32100 width=4)
-> Seq Scan on t2 (cost=0.00..355.00 rows=32100 width=4)
Optimizer: Postgres query optimizer
(14 rows)
```
As we can see, Redistribute Motion uses `Hash Key: (t3.i + t1.t1)` which is parametrized by outer rel (t1).
With fix:
```
reshke=# explain select * from generate_series(1,1) t1, lateral (select t3.i from t2 join t3 on t2.i = t3.i + t1 order by 1) z;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=10000000117.94..10000000129.99 rows=963 width=8)
-> Function Scan on generate_series t1 (cost=0.00..0.01 rows=1 width=4)
-> Materialize (cost=117.94..125.16 rows=963 width=4)
-> Sort (cost=117.94..120.34 rows=963 width=4)
Sort Key: t3.i
-> Hash Join (cost=33.90..70.21 rows=963 width=4)
Hash Cond: (t2.i = (t3.i + t1.t1))
-> Materialize (cost=0.00..21.87 rows=963 width=4)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..17.05 rows=963 width=4)
-> Seq Scan on t2 (cost=0.00..4.21 rows=321 width=4)
-> Hash (cost=21.87..21.87 rows=963 width=4)
-> Materialize (cost=0.00..21.87 rows=963 width=4)
-> Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..17.05 rows=963 width=4)
-> Seq Scan on t3 (cost=0.00..4.21 rows=321 width=4)
Optimizer: Postgres query optimizer
(15 rows)
```
The before-plan is better in term of performance, expect it is not valid ;). We receive executor-time error becuase of motion rescan. The after-fix plan is worse, but can be executed correctly. In fact, the sole thing this PR do is correctly use infrastructure committed at 00e25afe119c
in fact, I think pushing down join below the motion is possible, so some types of plans. But this is separate problem, in this PR I merely try to fix ERROR: illegal rescan of motion nodeApache 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.