Re: Do all the legs of an "OR" with null comparisons get executed?

From: Cantor <cantor77_at_excite.com>
Date: Fri, 28 Mar 2008 14:31:18 -0700 (PDT)
Message-ID: <01982c11-c3d7-40c2-a97b-014da22cf53e@s50g2000hsb.googlegroups.com>

On Mar 28, 2:15 pm, Frank van Bortel <frank.van.bor..._at_gmail.com> wrote:
> Arthernan wrote:
> > That is an alternative. I do think the logic is correct thought. Take
> > the first comparison for example:
>
> > UPP_FIRST='JOHN' and UPP_LAST='SMITH' and MMDDOB=NULL and AA=1
>
> MMDDOB=NULL should be "MMDDOB is NULL"
>
> And no, Oracle is smart enough to know boolean algebra; if
> a part of the boolean equation yields true (or false), the
> rest is no executed - it would not change the outcome.
>
> Now, you figure out when to OR or AND, and when TRUE or
> FALSE :)
> --
>
> Regards,
> Frank van Bortel
>
> Top-posting in UseNet newsgroups is one way to shut me up

I think is causing confusion is that the original text got removed. So I'll include it in this post.

"MMDDOB=NULL" is false all the time while "MMDDOB is NULL" depends on the value of MMDDOB. This actually works along with the intent of the query, so the query is actually correct. What got cutoff is that the final code will be parametized. So each one of the parameters could be made NULL at run-time.

• first post =============

If I do an explain plan of the statement below I can see that the "and" clauses that have MMDDOB=NULL do not get executed

select * from v_search where

UPP_FIRST='JOHN' and UPP_LAST='SMITH' and MMDDOB=NULL and AA=1 or
UPP_FIRST='JOHN' and UPP_LAST='SMITH' and BB=2 or
UPP_FIRST='JOHN' and UPP_LAST='SMITH' and CC='CA' or
UPP_FIRST='JOHN' and MMDDOB=NULL and AA=1 and BB=2 or
UPP_FIRST='JOHN' and MMDDOB=NULL and AA=1 and CC='CA' or
UPP_FIRST='JOHN' and BB=2 and CC='CA' or
UPP_LAST='SMITH' and MMDDOB=NULL and AA=1 and BB=2 or
UPP_LAST='SMITH' and MMDDOB=NULL and AA=1 and CC='CA' or
UPP_LAST='SMITH' and BB=2 and CC='CA' or

MMDDOB=NULL and AA=1 and BB=2 and CC='CA'

My problem is how to know if it does the same thing when I parametize the query as

select * from v_search where
UPP_FIRST=:UPP_FIRST and UPP_LAST=:UPP_LAST and MMDDOB=:MMDD and AA=:AA or

UPP_FIRST=:UPP_FIRST and UPP_LAST=:UPP_LAST and BB=:BB or
UPP_FIRST=:UPP_FIRST and UPP_LAST=:UPP_LAST and CC=:CC or
UPP_FIRST=:UPP_FIRST and MMDDOB=:MMDD and AA=:AA and BB=:BB or
UPP_FIRST=:UPP_FIRST and MMDDOB=:MMDD and AA=:AA and CC=:CC or
UPP_FIRST=:UPP_FIRST and BB=:BB and CC=:CC or
UPP_LAST=:UPP_LAST and MMDDOB=:MMDD and AA=:AA and BB=:BB or
UPP_LAST=:UPP_LAST and MMDDOB=:MMDD and AA=:AA and CC=:CC or
UPP_LAST=:UPP_LAST and BB=:BB and CC=:CC or

MMDDOB=:MMDD and AA=:AA and BB=:BB and CC=:CC Received on Fri Mar 28 2008 - 16:31:18 CDT