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

On Mar 31, 12:38 pm, "Shakespeare" <what..._at_xs4all.nl> wrote:
> "Arthernan" <arther..._at_hotmail.com> schreef in berichtnews:4b2f54b6-f801-42bf-9040-47cc2d3f2abc_at_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
>
> I'm sorry, I give up. I've read your post several times and still don't get
> it.
>
> Shakespeare

I think he is looking for something like Tom talks about starting around page 714 here:
http://books.google.com/books?id=8_WChqD3nc4C&pg=PA723&lpg=PA723&dq=%22function+dump_fixed_width%22&source=web&ots=S_h46hJRTS&sig=SWr1tWWISLiX2YAhH9ZxOf7qQbc&hl=en#PPA707,M1 (If that doesn't work, the reference is to Expert One-On-One Oracle by Tom Kyte. I'm sure there are other examples online, and perhaps in other books).

jg

--
@home.com is bogus.
"Eat my voltage!" - Chargers graphic on the back of a Prius.