Fix qual-clause-misplacement issues with pulled-up LATERAL subqueries.
In an example such as
SELECT * FROM
i LEFT JOIN LATERAL (SELECT * FROM j WHERE i.n = j.n) j ON true;
it is safe to pull up the LATERAL subquery into its parent, but we must
then treat the "i.n = j.n" clause as a qual clause of the LEFT JOIN. The
previous coding in deconstruct_recurse mistakenly labeled the clause as
"is_pushed_down", resulting in wrong semantics if the clause were applied
at the join node, as per an example submitted awhile ago by Jeremy Evans.
To fix, postpone processing of such clauses until we return back up to
the appropriate recursion depth in deconstruct_recurse.
In addition, tighten the is-safe-to-pull-up checks in is_simple_subquery;
we previously missed the possibility that the LATERAL subquery might itself
contain an outer join that makes lateral references in lower quals unsafe.
A regression test case equivalent to Jeremy's example was already in my
commit of yesterday, but was giving the wrong results because of this
bug. This patch fixes the expected output for that, and also adds a
test case for the second problem.
Branch
------
master
Details
-------
http://git.postgresql.org/pg/commitdiff/c64de21e9625acad57e2caf8f22435e1617fb1ce
Modified Files
--------------
src/backend/optimizer/README | 9 +-
src/backend/optimizer/plan/initsplan.c | 161 +++++++++++++++++++++++------
src/backend/optimizer/prep/prepjointree.c | 146 +++++++++++++++++++++++---
src/test/regress/expected/join.out | 76 ++++++++++++--
src/test/regress/sql/join.sql | 9 ++
5 files changed, 339 insertions(+), 62 deletions(-)