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

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Mon, 31 Mar 2008 21:38:38 +0200
Message-ID: <47f13dc6$0$14348$e4fe514c@news.xs4all.nl>

"Arthernan" <arthernan_at_hotmail.com> schreef in bericht news: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. Received on Mon Mar 31 2008 - 14:38:38 CDT

Original text of this message