pgsql: Flatten join alias Vars before rechecking subquery pullup safety

Flatten join alias Vars before rechecking subquery pullup safety

pull_up_simple_subquery() rechecks is_simple_subquery() after pulling
up the subquery's own subqueries, but it flattened join alias Vars in
the subquery's targetlist only after that, and never in its quals. A
join alias Var within the subquery, such as a whole-row Var of a join,
can expand to an expression containing lateral references to the outer
query once the join's inputs have been pulled up, and until it is
flattened is_simple_subquery() cannot see them. We could thus pull up
a LATERAL subquery whose targetlist or quals reference rels outside
the lowest outer join above it. This leads to assertion failures in
distribute_qual_to_rels() or "wrong phnullingrels" errors, and in some
cases to wrong query results, where rows that should have been
preserved by an outer join above the subquery are discarded.

To fix, flatten join alias Vars in the targetlist, and in the jointree
quals if the subquery is LATERAL, before the recheck. If the recheck
fails, the flattened copy is discarded along with the rest of the
modified subquery, so no harm is done.

Back-patch to all supported branches, as this can lead to wrong query
results.

Author: Richard Guo <guofenglinux(at)gmail(dot)com>
Reviewed-by: Tatsuya Kawata <kawatatatsuya0913(at)gmail(dot)com>
Discussion: https://postgr.es/m/CAMbWs48GFZ=3Bjc1ug9JRsB0Qupg+CGcb9js5vmTM6kY2OGWOg@mail.gmail.com
Backpatch-through: 14

Branch
------
REL_17_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/c37a2bb54ccc1e56e2c60aed161a89ce27c676f4

Modified Files
--------------
src/backend/optimizer/prep/prepjointree.c | 69 +++++++++++++++++----
src/test/regress/expected/join.out | 100 ++++++++++++++++++++++++++++++
src/test/regress/sql/join.sql | 33 ++++++++++
3 files changed, 189 insertions(+), 13 deletions(-)