Fix incorrect matching of subexpressions in outer-join plan nodes.
Previously we would re-use input subexpressions in all expression trees
attached to a Join plan node. However, if it's an outer join and the
subexpression appears in the nullable-side input, this is potentially
incorrect for apparently-matching subexpressions that came from above
the outer join (ie, targetlist and qpqual expressions), because the
executor will treat the subexpression value as NULL when maybe it should
not be.
The case is fairly hard to hit because (a) you need a non-strict
subexpression (else NULL is correct), and (b) we don't usually compute
expressions in the outputs of non-toplevel plan nodes. But we might do
so if the expressions are sort keys for a mergejoin, for example.
Probably in the long run we should make a more explicit distinction between
Vars appearing above and below an outer join, but that will be a major
planner redesign and not at all back-patchable. For the moment, just hack
set_join_references so that it will not match any non-Var expressions
coming from nullable inputs to expressions that came from above the join.
(This is somewhat overkill, in that a strict expression could still be
matched, but it doesn't seem worth the effort to check that.)
Per report from Qingqing Zhou. The added regression test case is based
on his example.
This has been broken for a very long time, so back-patch to all active
branches.
Branch
------
REL9_0_STABLE
Details
-------
http://git.postgresql.org/pg/commitdiff/da88191945c49159d2f6354f75d5cdc2ba5d36d5
Modified Files
--------------
src/backend/optimizer/plan/setrefs.c | 67 +++++++++++++++++++++++++++-------
src/test/regress/expected/join.out | 48 ++++++++++++++++++++++++
src/test/regress/sql/join.sql | 20 ++++++++++
3 files changed, 121 insertions(+), 14 deletions(-)
