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
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/0d6103e537e1299046e5906149f9a7d2108be2cf

Modified Files
--------------
src/backend/optimizer/README | 8 ++---
src/backend/optimizer/plan/initsplan.c | 21 +++++-------
src/backend/optimizer/plan/subselect.c | 51 +++++++++++++++++++++++++--
src/backend/optimizer/util/relnode.c | 8 ++---
src/include/nodes/pathnodes.h | 3 +-
src/include/nodes/primnodes.h | 5 +--
src/test/regress/expected/join.out | 63 ++++++++++++++++++++++++++++++++++
src/test/regress/sql/join.sql | 43 +++++++++++++++++++++++
8 files changed, 174 insertions(+), 28 deletions(-)