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 node
8 files changed
tree: 84cb20fe0bd14c0102501edbddc99bffd958bf30
  1. .abi-check/
  2. .github/
  3. config/
  4. contrib/
  5. dependency/
  6. devops/
  7. doc/
  8. gpAux/
  9. gpcontrib/
  10. gpMgmt/
  11. licenses/
  12. mcp-server/
  13. src/
  14. .asf.yaml
  15. .clang-tidy
  16. .dir-locals.el
  17. .editorconfig
  18. .git-blame-ignore-revs
  19. .gitattributes
  20. .gitignore
  21. .gitmessage
  22. .gitmodules
  23. aclocal.m4
  24. AGENTS.md.template
  25. AI_GUIDELINE.md
  26. CODE_OF_CONDUCT.md
  27. configure
  28. configure.ac
  29. CONTRIBUTING.md
  30. DISCLAIMER
  31. getversion
  32. GNUmakefile.in
  33. LICENSE
  34. Makefile
  35. meson.build
  36. meson_options.txt
  37. NOTICE
  38. pom.xml
  39. putversion
  40. python-dependencies.txt
  41. README.apache.md
  42. README.md
  43. SECURITY.md
  44. sonar-project.properties
README.md

Apache Cloudberry (Incubating)

Website Documentation Slack Twitter Follow WeChat Youtube GitHub Discussions GitHub commit activity(branch) GitHub contributors GitHub License Apache Cloudberry Build Ask DeepWiki Apache Rat Audit

Introduction

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.

Build and try out

Build from source

You can follow these guides to build Cloudberry on Linux OS (including RHEL/Rocky Linux, and Ubuntu) and macOS.

Try out quickly

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.

Repositories

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.

Community & Support

We have many channels for community members to discuss, ask for help, feedback, and chat:

TypeDescription
SlackClick 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&AAsk for help when running/developing Cloudberry, visit GitHub Discussions - QA.
New ideas / Feature RequestsShare ideas for new features, visit GitHub Discussions - Ideas.
Report bugsProblems and issues in Apache Cloudberry core. If you find bugs, welcome to submit them here.
Report a security vulnerabilityView our security policy to learn how to report and contact us.
Community eventsIncluding meetups, webinars, conferences, and more events, visit the Events page and subscribe to the events calendar.
DocumentationOfficial documentation for Cloudberry. You can explore it to discover more details about us.

Contribution

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.

TypeDescription
Code contributionLearn how to contribute code to the Cloudberry, including coding preparation, conventions, workflow, review, and checklist following the code contribution guide.
Submit the proposalProposing major changes to Cloudberry through proposal guide.
Doc contributionWe need you to join us to help us improve the documentation, see the doc contribution guide.
AI guidlineFor AI-assisted development, please review our AI guideline for advice on responsible AI usage.

Roadmap

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.)

Acknowledgment

Thanks to PostgreSQL, Greenplum Database and other great open source projects to make Apache Cloudberry has a sound foundation.

License

Cloudberry is licensed under the Apache License, Version 2.0. For details, see the LICENSE.

ASF Incubator disclaimer

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.