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]