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

On Mar 29, 12:43 pm, Charles Hooper <hooperc2...@xxxxxxxxx> wrote:
On Mar 28, 5:43 pm, Arthernan <arther...@xxxxxxxxxxx> 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...@xxxxxxxxx>
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.
.

Relevant Pages

  • Re: user permissions
    ... I would like to add some comments regarding 'EXECUTE AS' clause in SQL ... Server 2005 as a complement of Rick's response. ... Microsoft Online Community Support ...
    (microsoft.public.sqlserver.security)
  • Re: Do all the legs of an "OR" with null comparisons get executed?
    ... But if I did "MMDDOB is NULL" instead of MMDDOB=NULL. ... The WHERE clause is not working the way you expect, ... Placing as Oracle would evaluate the WHERE clause: ... evaluation order by using in the WHERE clause, ...
    (comp.databases.oracle.server)
  • Re: for what are for/while else clauses
    ... print avalue, ... When somebody else "will the else clause execute", ... It seems misleading to me to focus on the controlling condition, i.e., ...
    (comp.lang.python)
  • Re: Order of evaluation of functions in records & somewhat recursive function
    ... I am not sure if the evaluation is in this order, but I think it MAY BE. ... Execute the ORDER BY Clause ... if you are using the results of the vbaFun to order by then the vbaFun obviously has to run before the ORDER by clause can execute. ...
    (microsoft.public.access.queries)
  • SQL problem with SWI-Prolog
    ... execute an SQL query with a WHERE clause. ... I managed to execute simple SQL ... queries but my precidates always fail when i specify a WHERE clause. ... odbc_prepare(ccc, 'SELECT name, postcode FROM credit_history where ID ...
    (comp.lang.prolog)