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

On Mar 29, 7:14 am, "Shakespeare" <what...@xxxxxxxxx> wrote:
"Arthernan" <arther...@xxxxxxxxxxx> schreef in berichtnews:e65e3d5b-d8be-4dec-a1fa-8c17a1e89bf2@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Mar 28, 11:41 am, "fitzjarr...@xxxxxxx" <fitzjarr...@xxxxxxx>
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
.

Relevant Pages