Re: Query Help

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Thu, 17 Jan 2008 19:20:48 -0800 (PST)
Message-ID: <0a2069d4-2a8c-428e-af9a-0e31aa59ecc5@e32g2000prn.googlegroups.com>

On Jan 17, 8:05�am, spamb..._at_milmac.com (Doug Miller) wrote:
> In article <1c892130-538b-49bd-a6dc-b098222ee..._at_x69g2000hsx.googlegroups.com>, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
>
> >Just a minor correction to my previous post... additional thoughts
> >shortly after submitting the previous post. �These two logic checks
> >are not necessarily equivalent when dealing with data stored in a
> >database, but are equivalent when dealing only with Boolean logic:
> >IF (AGE <> 65) OR (AGE = 65 AND RETIRED = "Y") THEN
>
> >IF (AGE <> 65) OR (RETIRED = "Y") THEN
>
> >The first expression does not permit NULL values for the AGE, while
> >the second does. �
>
> I disagree: if AGE is NULL, then the first clause (AGE <> 65) is true, and in
> either form, the second clause won't even be evaluated (due to the
> conjunction).
>
> --
> Regards,
> � � � � Doug Miller (alphageek at milmac dot com)
>
> It's time to throw all their damned tea in the harbor again.

Very good answers provided by David Fitzjarrell and Shakespeare. David's demonstration shows very clearly how things work in PL/SQL. Some programming languages, and possibly databases provide different responses given the same input. First, let's example your first assertion "if AGE is NULL, then the first clause (AGE <> 65) is true". Let's see if we can build a test case that can be reproduced on different databases:

First, we will create a simple table with 4 rows: CREATE TABLE T1 (
AGE NUMBER(10),
RETIRED CHAR(1));

INSERT INTO T1 VALUES (65,'Y');
INSERT INTO T1 VALUES (64,'Y');
INSERT INTO T1 VALUES (NULL,'Y');
INSERT INTO T1 VALUES (NULL,'N');

Your assertion suggests that rows (in order inserted) 2, 3, and 4 should be returned because AGE <> 65. Also, row 1 should be returned since it satisfies the second condition RETIRED = "Y". Let's see what happens:
SELECT
  AGE,
  RETIRED
FROM
  T1
WHERE
  (AGE <> 65) OR (RETIRED = 'Y');

AGE R
---------- -

        65 Y
        64 Y
           Y

Your WHERE clause returned rows 1, 2, 3, but not row 4. A NULL value cannot be determined if it is equal to 65 or not equal to 65, and that is why row 4 was excluded.

Now, compare the results above with the results of the following: SELECT
  AGE,
  RETIRED
FROM
  T1
WHERE
  (AGE <> 65) OR (AGE = 65 AND RETIRED = 'Y');

AGE R
---------- -

        65 Y
        64 Y

Note that the third row was also excluded by the above query.

Let's take a look at your second assertion "the second clause won't even be evaluated (due to the conjunction)"

This is a little more difficult to test. C++ allows short-circuiting of test conditions separated by an OR (or AND), VB does not, VB.Net has optional syntax that allows short-circuiting, and Crystal Reports supports short-circuiting and automatic conversion of NULLs to empty strings. What about Oracle, specifically SQL?

First, let's create a sequence (an automaticallt increasing number), and then verify that it works correctly: CREATE SEQUENCE MYVALUE INCREMENT BY 1 START WITH 1; SELECT
MYVALUE.NEXTVAL MYVALUE
FROM
DUAL; MYVALUE

The above incremented the MYVALUE sequence by 1 and returned the value 1.

SELECT
MYVALUE.NEXTVAL MYVALUE
FROM
DUAL; MYVALUE

The above incremented the MYVALUE sequence by 1 and returned the value 2.

We can't include a sequence directly in a WHERE clause to determine if Oracle permits short-circuiting. However, we can create a PL/SQL function to select the next value from the MYVALUE sequence, and see if that function is called.

CREATE OR REPLACE FUNCTION GET_MY_VALUE
RETURN NUMBER
IS
MYVAL NUMBER;
BEGIN
SELECT MYVALUE.NEXTVAL INTO MYVAL FROM DUAL; RETURN MYVAL;
END GET_MY_VALUE;
/

Now, let's test to make certain that the PL/SQL function works as expected:
SELECT
GET_MY_VALUE
FROM
DUAL; GET_MY_VALUE

The function returned 3, which is 1 greater than before, so it appears to work correctly.

Let's try again:
SELECT
GET_MY_VALUE
FROM
DUAL; GET_MY_VALUE

The function returned 4, which is 1 greater than before.

Now, let's turn on a 10046 trace at level 4 to help determine what is going on behind the scenes:
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4'; Let's create a SQL statement with an OR condition in the WHERE clause, such that the first half of the OR condition will always be TRUE: SELECT
  'FOUND'
FROM
  DUAL
WHERE
  (1 = 1) OR (GET_MY_VALUE > 1); 'FOUN

FOUND Disabling the trace so as not to confuse the situation when we later check the NEXTVAL of the sequence:
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'; Let's see if the second half of the OR condition was evaluated or not. If it was not evaluated, the value returned should be one greater than the last time we checked the NEXTVAL of the sequence. If it was evaluated, the value returned should be two greater than the last time we checked the NEXTVAL of the sequence: SELECT
MYVALUE.NEXTVAL MYVALUE
FROM
DUAL; MYVALUE

It looks like Oracle does allow short-circuiting of OR conditions, but let's confirm by checking the trace file that we generated by setting event 10046:
PARSING IN CURSOR #5 len=66 dep=0 uid=0 oct=3 lid=0 tim=179928174658 hv=2929544144 ad='25e04918' sqlid='36w6vf6r9ukyh' SELECT
  'FOUND'
FROM
  DUAL
WHERE
  (1 = 1) OR (GET_MY_VALUE > 1)
END OF STMT
PARSE
#5:c=0,e=34300,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=179928174652 BINDS #5:
EXEC #5:c=0,e=59,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=179928176367 FETCH #5:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=179928176450

STAT #5 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0
time=0 us cost=2 size=0 card=1)'
FETCH #5:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=179928196143
=====================

In the above, only 1 cursor was parsed, and its dep was equal to 0, indicating that the function was not evaluated.

Let's try another test, such that the first half of the OR condition will always be FALSE (unless of course one decides to divide by 0): ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4'; SELECT
  'FOUND'
FROM
  DUAL
WHERE
  (1 = 2) OR (GET_MY_VALUE > 1); 'FOUN

FOUND ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'; Let's check the NEXTVAL of the sequence to determine if the second half of the OR condition was evaluated. If it was not evaluated, the value returned should be one greater than the last time we checked the NEXTVAL of the sequence. If it was evaluated, the value returned should be two greater than the last time we checked the NEXTVAL of the sequence:
SELECT
MYVALUE.NEXTVAL MYVALUE
FROM
DUAL; MYVALUE

The returned value was two higher than the last time checked, so the second half was evaluated. Let's check the trace file just for confirmation:

PARSING IN CURSOR #1 len=66 dep=0 uid=0 oct=3 lid=0 tim=180750448587 hv=3085677967 ad='25e326ac' sqlid='49m6ksavyrdcg' SELECT
  'FOUND'
FROM
  DUAL
WHERE
  (1 = 2) OR (GET_MY_VALUE > 1)
END OF STMT
PARSE
#1:c=0,e=1973,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=180750448582 BINDS #1:
BINDS #4:

PARSING IN CURSOR #4 len=32 dep=1 uid=0 oct=3 lid=0 tim=180750449027 hv=3972398240 ad='25fb973c' sqlid='bzhnd9rqcbz50' SELECT MYVALUE.NEXTVAL FROM DUAL
END OF STMT
EXEC #4:c=0,e=35,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=180750449023

FETCH #4:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,tim=180750449131
EXEC #1:c=0,e=305,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=180750449186
FETCH #1:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=180750449264

STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='FILTER (cr=0 pr=0 pw=0 time=0 us)'

STAT #1 id=2 cnt=1 pid=1 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0
time=0 us cost=2 size=0 card=1)'
FETCH #1:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=180750449701

Note that in the above there are two cursors parsed, with the first showing a dep of 0, and the second with a dep of 1 (the function call).

Let's see if Oracle allows short-circuiting of AND conditions: ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4'; SELECT
  'FOUND'
FROM
  DUAL
WHERE
  (1 = 2) AND (GET_MY_VALUE > 1); no rows selected

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'; SELECT
MYVALUE.NEXTVAL MYVALUE
FROM
DUAL; MYVALUE

PARSING IN CURSOR #5 len=67 dep=0 uid=0 oct=3 lid=0 tim=183903614533 hv=1150342053 ad='2f232fc0' sqlid='c13dunp291nx5' SELECT
  'FOUND'
FROM
  DUAL
WHERE
  (1 = 2) AND (GET_MY_VALUE > 1)
END OF STMT
PARSE #5:c=0,e=106,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=183903614527 BINDS #5:
EXEC #5:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=183903614960 FETCH #5:c=0,e=10,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=183903615026 STAT #5 id=1 cnt=0 pid=0 pos=1 obj=0 op='FILTER (cr=0 pr=0 pw=0 time=0 us)'
STAT #5 id=2 cnt=0 pid=1 pos=1 obj=0 op='FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)'

It appears, at least for Oracle 11.1.0.6.0 SQL, that your second assertion is correct.

Doug, thanks for the discussion.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Jan 17 2008 - 21:20:48 CST

This message: [ Message body ]
Next message: hitgonoracle_at_gmail.com: "what are the different between database migration and database upgration?"
Previous message: fitzjarrell_at_cox.net: "Re: Query Help"
In reply to: Doug Miller: "Re: Query Help"
Next in thread: Charles Hooper: "Re: Query Help"
Reply: Charles Hooper: "Re: Query Help"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message