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

From: Arthernan <arthernan_at_hotmail.com>
Date: Sun, 30 Mar 2008 14:53:24 -0700 (PDT)
Message-ID: <4b2f54b6-f801-42bf-9040-47cc2d3f2abc@t54g2000hsg.googlegroups.com>

On Mar 29, 7:14 am, "Shakespeare" <what..._at_xs4all.nl> wrote:
> "Arthernan" <arther..._at_hotmail.com> schreef in berichtnews:e65e3d5b-d8be-4dec-a1fa-8c17a1e89bf2_at_b1g2000hsg.googlegroups.com...
> On Mar 28, 11:41 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net>
> 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
> > I do not want to get any rows back if MMDDOB was NULL. In this case I
>
> In that case you should use
>
> WHERE MMDDOB is not NULL and (--- rest of your conditions).
>
> MMDDOB = NULL is ALWAYS false (or actually undefined but not true) so these
> clauses dont need to be calculated. You could replace the whole part with
> "0=1"
>
> > want Oracle to skip that comparison at execution. And it does do that
> > when it is not parametized. I just don't know if it will when I
> > parametize it.
> > The code is very simple and straight forward as it is. And I do
> > believe it is also correct.
>
> No it's not, comparing with '=NULL' id definitely wrong.
>
> > I don't want to make it complicated if it
> > not necessary.
>
> But you already did.
>
> > Arturo Hernandez
>
> Shakespeare

Ohh my!! I do not think I'm comunicating here.

If I write "where a=b" no parameters, no constants just database columns. Any row where "a has a null value" or "b has a null value" is filtered out. This is by design. Ther is nothing wrong in having null values in the "a column" or the "b column". I do not intend to write code that would read "where a=NULL" that is incorrect. What I do intend to write is a PARAMETIZED sql that reads "where a=:a" notice the colon after the variable name a. This is a standard wayt to parametize SQL statements. Normally I do not write such complex queries as the one above. But just like in a table there is no way to know ahead of time whether there will be non-null values in the "where a=b" example. I do not know if there could be NULL values in the parameters.

I wrote the SQL with MMDDOB=NULL as an example, but that is not all. I wrote that because it's easy to run an explain plan on it. But my question is about the PARAMETIZED sql. here is my original question

---

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 Sun Mar 30 2008 - 16:53:24 CDT