Gregory Stark <stark@enterprisedb.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> * When we have a non-nullable expression in a sub-select's targetlist,
>> and it's below an outer join, replace the expression by
>> CASE WHEN flag_var THEN original_expression ELSE NULL END
>> and then flatten as normal.
> I don't understand how this gets you any further ahead. Doesn't it just move
> the problems you have now with the original_expression to flag_var instead?
No, because the point is that a var will go to null when passed through
an outer join that is trying to set it to null. The original_expression
might be something that doesn't go to null by itself; for instance a
non-null constant (see bug report cited in original message).
An alternative approach to solving the problem is to ensure that the
expression gets evaluated below the outer join and then pass its value
up as a variable. But that requires a lot more planner hacking than
I have time to get done for 8.4. The current planner tries to postpone
expression evaluation as long as possible, and changing that isn't
trivial. (It's not necessarily desirable, either --- pushing an
expensive expression down to a place where it'd get evaluated more times
isn't a win.)
This is definitely an area where more work remains, but I'm trying to
solve the largest practical problem in a way that's implementable
without too much work. If I end up throwing that work away in some
future release, in favor of a more general answer, I won't cry too much.
regards, tom lane