postgres.git / summary / log / commit / refs
commit b83b7ddf7a64dce41624b5c0dabefd74dfe10ec0
Author: Richard Guo <rguo@postgresql.org>
Date: Mon Oct 05 06:01:48 2026 +0000
First released in: 16.16, 17.12, 18.7, 19.0
Fix relids of EC-derived join clauses for lateral UNION ALL members
When a LATERAL UNION ALL subquery is flattened into an appendrel, a
child EquivalenceMember's expression can contain lateral references to
other relations. create_join_clause() builds a clause from such a
member with make_restrictinfo(), which computes clause_relids from the
expression, and so includes the relids of the lateral references along
with those of any outer joins that null them. Commit 03107b4ed made
sure that clause_relids include the child's relids, but it only ever
added relids.
This is a problem when a lateral reference is nullable. The child is
parameterized by its lateral_relids, which do not include the outer
joins that null the lateral references, so the clause fails the
join_clause_is_movable_into() assertion that
get_baserel_parampathinfo() applies to EC-derived clauses.
To fix, set a child clause's clause_relids from its members' em_relids
rather than from their expressions. This matches how
adjust_appendrel_attrs() computes clause_relids for a child's other
join clauses. The lateral references need not be included, since
every path for the child is parameterized by at least its
lateral_relids. Also update num_base_rels to match, which commit
03107b4ed neglected to do.
Back-patch to v16, where outer-join relids were added to
clause_relids. Before that, the relids of a lateral reference are
always part of the child's parameterization, so the problem does not
arise.
Author: Richard Guo <guofenglinux@gmail.com>
Reviewed-by: wenhui qiu <qiuwenhuifx@gmail.com>
Discussion: https://postgr.es/m/CAMbWs4-cRxpQNyQJW=PkDagSDGQ=3Rp2t_fSAyROKsDStjKHUQ@mail.gmail.com
Backpatch-through: 16
src/backend/optimizer/path/equivclass.c | 32 +++++++-----
src/test/regress/expected/join.out | 92 +++++++++++++++++++++++++++++++++
src/test/regress/sql/join.sql | 26 ++++++++++
3 files changed, 138 insertions(+), 12 deletions(-)
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 503eb0f3c94..54fbd5a4a7f 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -1881,19 +1881,27 @@ create_join_clause(PlannerInfo *root,
ec->ec_min_security);
/*
- * If either EM is a child, force the clause's clause_relids to include
- * the relid(s) of the child rel. In normal cases it would already, but
- * not if we are considering appendrel child relations with pseudoconstant
- * translated variables (i.e., UNION ALL sub-selects with constant output
- * items). We must do this so that join_clause_is_movable_into() will
- * think that the clause should be evaluated at the correct place.
+ * If either EM is a child, set the clause's clause_relids from the
+ * members' em_relids rather than the relids found in the expressions.
+ * These normally match, but not for UNION ALL sub-selects whose output
+ * items are constants (mentioning no rels) or contain lateral references
+ * (mentioning rels that the child's parameterization supplies). We must
+ * do this so that join_clause_is_movable_into() will think that the
+ * clause should be evaluated at the correct place.
*/
- if (leftem->em_is_child)
- rinfo->clause_relids = bms_add_members(rinfo->clause_relids,
- leftem->em_relids);
- if (rightem->em_is_child)
- rinfo->clause_relids = bms_add_members(rinfo->clause_relids,
- rightem->em_relids);
+ if (leftem->em_is_child || rightem->em_is_child)
+ {
+ Relids baserels;
+
+ rinfo->clause_relids = bms_union(leftem->em_relids,
+ rightem->em_relids);
+
+ /* keep num_base_rels in sync, as in make_restrictinfo() */
+ baserels = bms_difference(rinfo->clause_relids,
+ root->outer_join_rels);
+ rinfo->num_base_rels = bms_num_members(baserels);
+ bms_free(baserels);
+ }
/* If it's a child clause, copy the parent's rinfo_serial */
if (parent_rinfo)
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 63d0e99b512..67b8ed757bc 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -7352,6 +7352,98 @@ select * from
4567890123456789 | -4567890123456789 | | |
(10 rows)
+-- check EC-derived clauses for a UNION ALL member with nullable lateral refs
+explain (costs off)
+select * from
+ int8_tbl x left join int8_tbl y on x.q2 = y.q1,
+ lateral (select x.q1 as v union all select x.q1 + y.q2) ss
+where x.q2 = ss.v;
+ QUERY PLAN
+-------------------------------------------------------
+ Nested Loop
+ -> Hash Left Join
+ Hash Cond: (x.q2 = y.q1)
+ -> Seq Scan on int8_tbl x
+ -> Hash
+ -> Seq Scan on int8_tbl y
+ -> Append
+ -> Result
+ One-Time Filter: (x.q2 = x.q1)
+ -> Result
+ One-Time Filter: (x.q2 = (x.q1 + y.q2))
+(11 rows)
+
+select * from
+ int8_tbl x left join int8_tbl y on x.q2 = y.q1,
+ lateral (select x.q1 as v union all select x.q1 + y.q2) ss
+where x.q2 = ss.v;
+ q1 | q2 | q1 | q2 | v
+------------------+------------------+------------------+-------------------+------------------
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789
+(3 rows)
+
+-- likewise when the member is scanned below the outer join nulling those refs
+explain (costs off)
+select * from
+ int8_tbl x left join int8_tbl y on true
+ left join (int8_tbl z join
+ lateral (select z.q1 as v union all select y.q1 + z.q1) ss
+ on z.q2 = ss.v)
+ on y.q1 = 1;
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on int8_tbl x
+ -> Materialize
+ -> Nested Loop Left Join
+ Join Filter: (y.q1 = 1)
+ -> Seq Scan on int8_tbl y
+ -> Nested Loop
+ -> Seq Scan on int8_tbl z
+ -> Append
+ -> Result
+ One-Time Filter: (z.q2 = z.q1)
+ -> Result
+ One-Time Filter: (z.q2 = (y.q1 + z.q1))
+(13 rows)
+
+select * from
+ int8_tbl x left join int8_tbl y on true
+ left join (int8_tbl z join
+ lateral (select z.q1 as v union all select y.q1 + z.q1) ss
+ on z.q2 = ss.v)
+ on y.q1 = 1;
+ q1 | q2 | q1 | q2 | q1 | q2 | v
+------------------+-------------------+------------------+-------------------+----+----+---
+ 123 | 456 | 123 | 456 | | |
+ 123 | 456 | 123 | 4567890123456789 | | |
+ 123 | 456 | 4567890123456789 | 123 | | |
+ 123 | 456 | 4567890123456789 | 4567890123456789 | | |
+ 123 | 456 | 4567890123456789 | -4567890123456789 | | |
+ 123 | 4567890123456789 | 123 | 456 | | |
+ 123 | 4567890123456789 | 123 | 4567890123456789 | | |
+ 123 | 4567890123456789 | 4567890123456789 | 123 | | |
+ 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | | |
+ 123 | 4567890123456789 | 4567890123456789 | -4567890123456789 | | |
+ 4567890123456789 | 123 | 123 | 456 | | |
+ 4567890123456789 | 123 | 123 | 4567890123456789 | | |
+ 4567890123456789 | 123 | 4567890123456789 | 123 | | |
+ 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | | |
+ 4567890123456789 | 123 | 4567890123456789 | -4567890123456789 | | |
+ 4567890123456789 | 4567890123456789 | 123 | 456 | | |
+ 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | | |
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | | |
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | | |
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 | | |
+ 4567890123456789 | -4567890123456789 | 123 | 456 | | |
+ 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | | |
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | 123 | | |
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | | |
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | -4567890123456789 | | |
+(25 rows)
+
-- lateral can result in join conditions appearing below their
-- real semantic level
explain (verbose, costs off)
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 5848979630d..2e3494e83af 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -2705,6 +2705,32 @@ select * from
int8_tbl a left join
lateral (select *, coalesce(a.q2, 42) as x from int8_tbl b) ss on a.q2 = ss.q1;
+-- check EC-derived clauses for a UNION ALL member with nullable lateral refs
+explain (costs off)
+select * from
+ int8_tbl x left join int8_tbl y on x.q2 = y.q1,
+ lateral (select x.q1 as v union all select x.q1 + y.q2) ss
+where x.q2 = ss.v;
+select * from
+ int8_tbl x left join int8_tbl y on x.q2 = y.q1,
+ lateral (select x.q1 as v union all select x.q1 + y.q2) ss
+where x.q2 = ss.v;
+
+-- likewise when the member is scanned below the outer join nulling those refs
+explain (costs off)
+select * from
+ int8_tbl x left join int8_tbl y on true
+ left join (int8_tbl z join
+ lateral (select z.q1 as v union all select y.q1 + z.q1) ss
+ on z.q2 = ss.v)
+ on y.q1 = 1;
+select * from
+ int8_tbl x left join int8_tbl y on true
+ left join (int8_tbl z join
+ lateral (select z.q1 as v union all select y.q1 + z.q1) ss
+ on z.q2 = ss.v)
+ on y.q1 = 1;
+
-- lateral can result in join conditions appearing below their
-- real semantic level
explain (verbose, costs off)
[parent: adc1dc6f5b10]