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_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/7fb765945b51fcbef6d3c43ad9c1be247a8be6d4 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(-)
