Re: apply outer->inner join optimisation to OR clauses

On Sat, May 03, 2003 at 01:22:11PM -0400, Tom Lane wrote:
> Bradley Baetz <bbaetz(at)acm(dot)org> writes:
> > The attached patch applies the optimisation translating outer joins to
> > inner joins (where safe) to the cases where the WHERE clause has OR bits
> > in it too, if the column is present (and not null) in all of the OR
> > bits.
>
> Your change for AND is obviously incorrect, and I don't think I believe
> the OR case either. Why is it safe to pass down a TRUE top_level flag?

Its safe because

WHERE (foo=1) OR (foo=2) OR (foo IS NOT NULL)

still means that foo is non_nullable. Without my patch, this is done for
AND anyway, via the List node. I don't see the difference in this
context between

(List (Op = foo 1) (Op = foo 2) (NullTest isNotNull foo))

and

(BoolExpr And (Op = foo 1) (Op = foo 2) (NullTest isNotNull foo))

since the second is simplified into the first earlier on.

As I understand it, the top_level flag is intended to prevent against
|NOT (foo IS NOT NULL)|, which does not make foo non_nullable, and |NOT
(foo IS True/False/etc)|, which also doesn't prove that foo isn't
nullable.

For the opExpr, we want to stop ((foo IS NOT NULL)=False) from making
foo nonnullable. For the BooleanExpr's OR and AND I don't see a
combination which can take place that way - none of the operands are
combined with the other so that they can 'interfere' with each other
like that.

I don't think that the fact that |SELECT foo>2 OR NULL| gives NULL, not
FALSE, if foo <=2 is an issue either, since they're both not a true
value.

>
> regards, tom lane

Bradley