Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-patches by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group