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

From: Bradley Baetz <bbaetz(at)acm(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-patches(at)postgresql(dot)org
Subject: Re: apply outer->inner join optimisation to OR clauses
Date: 2003-05-04 03:42:52
Message-ID: 20030504034252.GA6050@mango.home
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-patches

On Sat, May 03, 2003 at 08:53:14PM -0400, Tom Lane wrote:
> Bradley Baetz <bbaetz(at)acm(dot)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

In response to

Responses

Browse pgsql-patches by date

  From Date Subject
Next Message Bruce Momjian 2003-05-04 04:43:27 Disable alternate locations on Win32
Previous Message Bruce Momjian 2003-05-04 03:08:09 Re: GUC patch for Win32