public inbox for [email protected]
help / color / mirror / Atom feedBUG #19460: FULL JOIN rewriting issue on empty queries
2+ messages / 2 participants
[nested] [flat]
* BUG #19460: FULL JOIN rewriting issue on empty queries
@ 2026-04-19 17:57 PG Bug reporting form <[email protected]>
2026-04-19 21:10 ` Re: BUG #19460: FULL JOIN rewriting issue on empty queries Tom Lane <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-04-19 17:57 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
The following bug has been logged on the website:
Bug reference: 19460
Logged by: François Jehl
Email address: [email protected]
PostgreSQL version: 17.9
Operating system: Linux
Description:
Good evening,
After migrating from version 15 to 17.9, the following query fails with:
ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join
conditions
This is weird because the FULL JOIN is a basic equi-join that succeeds on
PG15 (we tested it; it also fails on 16).
It requires a table t(id UUID PRIMARY KEY) but here is a repro on DbFiddle
https://www.db-fiddle.com/f/hCq5S13Zs3EV8f86Mxxh3B/3.
SELECT COALESCE(lhs.id, rhs.id) AS id
FROM (SELECT gen_random_uuid() AS id) AS lhs
FULL OUTER JOIN (
SELECT sub.id
FROM (
SELECT empty_source.id
FROM (SELECT NULL::UUID AS id WHERE FALSE) AS empty_source
LEFT OUTER JOIN (
SELECT t.id FROM t WHERE t.id =
'26c5112c-0a8f-4315-9ff5-7dcb59b8359e'::UUID
) AS sub ON sub.id = empty_source.id
) AS sub
) AS rhs ON rhs.id = lhs.id;
Adding OFFSET 0 to the empty subquery on the RHS prevents the error,
suggesting the query rewriter is doing something it should not!
Another thing: removing the PK constraint on t.id, or removing/changing the
WHERE filter to a non-PK column, makes the query succeed. The PK equality
filter is required to trigger the failure (maybe because it generates some
additional inlining, which then allows the empty subquery to collapse).
We're happy to provide more context or test patches if helpful.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19460: FULL JOIN rewriting issue on empty queries
2026-04-19 17:57 BUG #19460: FULL JOIN rewriting issue on empty queries PG Bug reporting form <[email protected]>
@ 2026-04-19 21:10 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2026-04-19 21:10 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]; Robert Haas <[email protected]>
PG Bug reporting form <[email protected]> writes:
> After migrating from version 15 to 17.9, the following query fails with:
> ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join
> conditions
Thanks for the report!
This turns out to be because somebody long ago thought that outer join
removal could be lazy about how much of the planner's data structures
it needs to update. Specifically, when the lower LEFT OUTER JOIN
gets removed, we failed to remove the associated relids from the
left_relids and right_relids of the upper "ON rhs.id = lhs.id" clause,
and that blocks recognition of the applicability of a hash or merge
join, because clause_sides_match_join() fails.
The fix seems pretty trivial, as attached. (While I'm only certain
that we have to fix left_relids and right_relids, this discovery
makes it seem like it'd be pretty foolish not to fix all the relid
sets of a RestrictInfo.) I didn't make a regression test case yet,
but we need one since no existing test results change (!?).
I'm feeling a tad nervous about pushing this into released branches.
It seems likely that it might enable quite a few join plans that were
previously not considered, and people tend not to like plan changes in
stable branches. However, (a) it's hard to argue that this isn't a
regression from pre-v16, and (b) since this change affects no existing
test, maybe the blast radius isn't as big as I fear.
Thoughts?
regards, tom lane
Attachments:
[text/x-diff] v1-fix-missing-RestrictInfo-updates.patch (2.0K, 2-v1-fix-missing-RestrictInfo-updates.patch)
download | inline diff:
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index 12e9ed0d0c7..72f0d018f3d 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -650,7 +650,7 @@ remove_leftjoinrel_from_query(PlannerInfo *root, int relid,
/*
* Remove any references to relid or ojrelid from the RestrictInfo.
*
- * We only bother to clean out bits in clause_relids and required_relids,
+ * We only bother to clean out bits in the RestrictInfo's various relid sets,
* not nullingrel bits in contained Vars and PHVs. (This might have to be
* improved sometime.) However, if the RestrictInfo contains an OR clause
* we have to also clean up the sub-clauses.
@@ -672,6 +672,22 @@ remove_rel_from_restrictinfo(RestrictInfo *rinfo, int relid, int ojrelid)
rinfo->required_relids = bms_copy(rinfo->required_relids);
rinfo->required_relids = bms_del_member(rinfo->required_relids, relid);
rinfo->required_relids = bms_del_member(rinfo->required_relids, ojrelid);
+ /* Likewise for incompatible_relids */
+ rinfo->incompatible_relids = bms_copy(rinfo->incompatible_relids);
+ rinfo->incompatible_relids = bms_del_member(rinfo->incompatible_relids, relid);
+ rinfo->incompatible_relids = bms_del_member(rinfo->incompatible_relids, ojrelid);
+ /* Likewise for outer_relids */
+ rinfo->outer_relids = bms_copy(rinfo->outer_relids);
+ rinfo->outer_relids = bms_del_member(rinfo->outer_relids, relid);
+ rinfo->outer_relids = bms_del_member(rinfo->outer_relids, ojrelid);
+ /* Likewise for left_relids */
+ rinfo->left_relids = bms_copy(rinfo->left_relids);
+ rinfo->left_relids = bms_del_member(rinfo->left_relids, relid);
+ rinfo->left_relids = bms_del_member(rinfo->left_relids, ojrelid);
+ /* Likewise for right_relids */
+ rinfo->right_relids = bms_copy(rinfo->right_relids);
+ rinfo->right_relids = bms_del_member(rinfo->right_relids, relid);
+ rinfo->right_relids = bms_del_member(rinfo->right_relids, ojrelid);
/* If it's an OR, recurse to clean up sub-clauses */
if (restriction_is_or_clause(rinfo))
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-04-19 21:10 UTC | newest]
Thread overview: 2+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-19 17:57 BUG #19460: FULL JOIN rewriting issue on empty queries PG Bug reporting form <[email protected]>
2026-04-19 21:10 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox