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 <[email protected]> Reviewed-by: Tatsuya Kawata <[email protected]> Discussion: https://postgr.es/m/CAMbWs48GFZ=3bjc1ug9jrsb0qupg+cgcb9js5vmtm6ky2og...@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(-)
