| From: | Alena Rybakina <lena(dot)ribackina(at)yandex(dot)ru> |
|---|---|
| To: | Ilia Evdokimov <ilya(dot)evdokimov(at)tantorlabs(dot)com> |
| Cc: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Peter Petrov <pspetrov91(at)gmail(dot)com>, Ranier Vilela <ranier(dot)vf(at)gmail(dot)com>, David Rowley <dgrowleyml(at)gmail(dot)com>, solaimurugan vellaipandiyan <drsolaimurugan(dot)v(at)gmail(dot)com> |
| Subject: | Re: pull-up subquery if JOIN-ON contains refs to upper-query |
| Date: | 2026-10-13 11:44:15 |
| Message-ID: | 600857c0-8439-4c9c-8d87-6cb46dfc4406@yandex.ru |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi all,
Inspired by the Cloudberry (a Greenplum-based database) bug discussion
[0], I added support for pulling up an EXISTS subquery from an outer
join’s ON clause when it references both join inputs. This is
implemented in the second patch.
The implementation shares subquery preparation with the existing
semijoin/antijoin conversion. When those transformations are blocked by
references to the preserved input, it tries a LEFT JOIN against distinct
correlation keys, attached to the nullable input. A CASE expression in
the original ON clause checks the match marker and evaluates the
remaining correlations. For NOT EXISTS, this fallback is allowed only
when all correlation keys are fixed by the nullable input; otherwise,
the SubPlan is retained.
[0]
https://github.com/apache/cloudberry/pull/1933/changes/4e9f8c7db45186b520632803e2eb22a350f5d8b9
I also tested the patches with SQLsmith. The only internal error
encountered was the one already reported in [1], which is unrelated to
these changes.
--
Regards,
Alena Rybakina
Yandex
| Attachment | Content-Type | Size |
|---|---|---|
| v11-0001-Allow-pulling-up-EXISTS-sublinks-with-correlated.patch | text/plain | 32.9 KB |
| v11-0002-Pull-up-EXISTS-from-outer-join-ON-clauses-refere.patch | text/plain | 57.1 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Shubhra Jain | 2026-10-13 11:50:03 | Re: Prevent premature startup of pg_upgrade targets |
| Previous Message | Heikki Linnakangas | 2026-10-13 11:43:06 | Re: [PATCH] numeric: canonicalize wide digit accumulation |