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