Give antijoins made from sublinks a relid When pull_up_sublinks() converts a sublink to an antijoin, it builds a JoinExpr with no RTE, on the grounds that nothing above the join can reference its RHS. That overlooks PlaceHolderVars. A PHV with no Vars of its own is evaluated at its phrels, and for the outputs of a pulled-up subquery phrels is all the relids in the subquery's jointree, including the RHS of such a join.
A qual using the PHV thus appears to reference the RHS rels, and can be pushed down into the RHS as a parameterized join clause. There it has the opposite of the intended effect: outer rows that fail the qual find no match and are emitted, giving wrong results. For an outer join, including an antijoin made by reducing a left join, this cannot happen because the join's relid is among the qual's relids. Before v16, the qual's nullable_relids prevented it. To fix, make a join RTE for an antijoin built from a sublink, so that it has a relid like any other outer join. Semijoins are left as they are: a qual evaluated within a semijoin's RHS does not change the result. Back-patch to v16. Before that, the qual's nullable_relids included the antijoin's RHS, which kept the qual from being moved there; that protection went away when nullable_relids was replaced by outer-join relids. Author: Richard Guo <[email protected]> Reviewed-by: Tom Lane <[email protected]> Reviewed-by: wenhui qiu <[email protected]> Discussion: https://postgr.es/m/CAMbWs4_rEdcG89BgC-AO7b+Bf2z8w3976+4zP==Urt=cxb1...@mail.gmail.com Backpatch-through: 16 Branch ------ REL_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/ca4273866be4a5f7ab1420fd123aa97f2588363d Modified Files -------------- src/backend/optimizer/plan/initsplan.c | 21 +++++++-------- src/backend/optimizer/plan/subselect.c | 48 +++++++++++++++++++++++++++++++++- src/include/nodes/pathnodes.h | 3 +-- src/include/nodes/primnodes.h | 5 ++-- src/test/regress/expected/join.out | 29 ++++++++++++++++++++ src/test/regress/sql/join.sql | 18 +++++++++++++ 6 files changed, 107 insertions(+), 17 deletions(-)
