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

On Mar 29, 12:43 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Mar 28, 5:43 pm, Arthernan <arther..._at_hotmail.com> wrote:
>
>
>
> > 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
>
> > 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
>
> > But if I did "MMDDOB is NULL" instead of MMDDOB=NULL. I would get
> > records back when I don't want them.
>
> > The final query will be parametized like this "MMDDOB=:MMDDOB". So I
> > want to know if :MMDDOB happens to be NULL, will oracle be smart
> > enougth to realize that whole "and clause" can go away. For example if
> > I did MMDDOB=1231, whenever MMDDOB was NULL the row would be filtered
> > out. Just look at my original SQL above and you will see what I mean.
>
> > Arturo Hernandez- Hide quoted text -
>
> > - Show quoted text -
>
> Look closely at the responses that Frank van Bortel provided to you.
> The WHERE clause is not working the way you expect, and that is why
> you receive too many rows when using MMDDOB IS NULL.
>
> Placing ( ) as Oracle would evaluate the WHERE clause:
> (((((((((((((((((((((((((((((((((((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')
>
> Now, let's just replace MMDDOB=NULL with FALSE, as it can never be
> TRUE (as indicated by several people in this thread):
> (((((((((((((((((((((((((((((((((((UPP_FIRST='JOHN' and
> UPP_LAST='SMITH') and FALSE) 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 FALSE) and
> AA=1) and BB=2) or UPP_FIRST='JOHN') and FALSE) and AA=1) and CC='CA')
> or {UPP_FIRST='JOHN') and BB=2) and CC='CA')} or UPP_LAST='SMITH') and
> FALSE) and AA=1) and BB=2) or UPP_LAST='SMITH') and FALSE) and AA=1)
> and CC='CA') or UPP_LAST='SMITH') and BB=2) and CC='CA') or FALSE) and
> AA=1) and BB=2) and CC='CA')
>
> Now, let's go through the WHERE clause and set all other conditions to
> TRUE, just to see what happens if those conditions were TRUE:
> (((((((((((((((((((((((((((((((((((TRUE and TRUE) and FALSE) and TRUE)
> or TRUE) and TRUE) and TRUE) or {TRUE) and TRUE) and TRUE)} or TRUE)
> and FALSE) and TRUE) and TRUE) or TRUE) and FALSE) and TRUE) and TRUE)
> or TRUE) and TRUE) and TRUE) or TRUE) and FALSE) and TRUE) and TRUE)
> or TRUE) and FALSE) and TRUE) and TRUE) or TRUE) and TRUE) and TRUE)
> or FALSE) and TRUE) and TRUE) and TRUE)
>
> In the above, look closely for the curly brackets { } (I did not
> identify all such cases). Now, simplifying the above a little, we
> start to see the problem - most of the WHERE clause evaluates to FALSE
> - always
> ((((((((((((((((((((((((((((((FALSE or TRUE) and TRUE) and TRUE) or
> TRUE) and FALSE) and TRUE) and TRUE) or TRUE) and FALSE) and TRUE) and
> TRUE) or TRUE) and TRUE) and TRUE) or TRUE) and FALSE) and TRUE) and
> TRUE) or TRUE) and FALSE) and TRUE) and TRUE) or TRUE) and TRUE) and
> TRUE) or FALSE) and TRUE) and TRUE) and TRUE)
>
> Simplifying again
> (((((((((((((((((((((((((((TRUE) or TRUE) and FALSE) and TRUE) and
> TRUE) or TRUE) and FALSE) and TRUE) and TRUE) or TRUE) and TRUE) and
> TRUE) or TRUE) and FALSE) and TRUE) and TRUE) or TRUE) and FALSE) and
> TRUE) and TRUE) or TRUE) and TRUE) and TRUE) or FALSE) and TRUE) and
> TRUE) and TRUE)
>
> There are only a couple of conditions in the above that could possibly
> evaluate to TRUE.
>
> The reason that the WHERE clause does not work with the MMDDOB IS NULL
> condition is due to the order of evaluation. You did not specify the
> evaluation order by using ( ) in the WHERE clause, so Oracle used the
> default (note that the default evaluation order differs from some
> programming languages, such as C).
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

I really appreciate your time, but I do not think my question is coming throught. I am going to parametize my query. So I am not really going to execute something that reads .... A=NULL... What I am going to have is ..... A=:A....notice the colon before the second A. I do not know if :A will have a NULL value. Just like I don't know if A will have one a NULL value. I understand Oracle execution enough to know the logic matches the intent. What I do not know is how will it be executed. Received on Sun Mar 30 2008 - 16:59:36 CDT