pgsql: Give antijoins made from sublinks a relid

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 <guofenglinux(at)gmail(dot)com>
Reviewed-by: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Reviewed-by: wenhui qiu <qiuwenhuifx(at)gmail(dot)com>
Discussion: https://postgr.es/m/CAMbWs4_rEdcG89BgC-AO7b+Bf2z8w3976+4zP==Urt=CXB1hsg@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(-)