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'm about to run out for a bit, so I'll reply to the rest later.

I was considering WHERE NOT (a.a1>1), which was already acceptable, but
you're right in that your example can have either of them being null.

Its really just NOT which is the problem. I wonder if its safe to do
this for stuff inside a NULL iff there is only one nonnullable_rel from
the expression? I'll think about that a bit.

I'll read through your comments in more detail in a few hours.

> (BTW, I don't quite like the adjective SAFE here; can you think of a
> better term?)

NULL_STRICT, perhaps?

>
> The SAFE_IF_NULL_OR_TRUE context might prove not to be worth the trouble
> of implementing --- I'm not sure if either AND or OR can really exploit
> it any better than they can exploit SAFE_IS_NULL.  If not, we'd be back
> to just a boolean context value, but we'd have a clearer understanding
> of what it really means.

The main use (which got me to look into this) is IN.

Bradley