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

On Sat, May 03, 2003 at 08:53:14PM -0400, Tom Lane wrote:
> Bradley Baetz <bbaetz@acm.org> writes:
> > 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.
>
> But that is exactly the distinction that we have to worry about when not
> at top level.  The error in the AND part of the proposed patch is
> exhibited by
>         WHERE NOT ((a.a1 > 1) AND (b.b1 > 1))
> When a.a1 is NULL, the AND can't yield TRUE --- but it can yield FALSE,
> which will become TRUE at the top level.  So neither a nor b can be
> considered non-nullable in this expression.

I tried testing this, but the problem is that this is rewritten as

WHERE (a.a1<=1 OR b.b1<=1)

so it doesn't trigger. (I was sure I'd tested that before submiting the
patch) Is there a way to turn that transformation off for testing
purposes? Or are we guaranteed that there wont' be OR/AND expressions
inside a NOT, because they will have always been expanded?

>
> You do have an insight here, which is that if the same rel can be shown
> to null *all* the arms of an OR, it nulls the OR.  The same holds true
> of an AND, I think.

Yes, but with an AND, you can use any of them (modulo the NOT issue, of
course)

> SAFE_IF_NULL        a rel is nonnullable if nulling it forces this
>             expression to return NULL
> SAFE_IF_NULL_OR_FALSE    a rel is nonnullable if nulling it forces this
>             expression to return NULL or FALSE
> SAFE_IF_NULL_OR_TRUE    a rel is nonnullable if nulling it forces this
>             expression to return NULL or TRUE

I'll have to think abotu this a bit more.

Do we agree that if the thing inside the NOT is a single expression,
then what I've doing is safe? IS it safe even if theres and/or involved
as the argument to a (strict) operator?

>             regards, tom lane

Bradley