Re: NVL doesn't short-circuit

One would think the CASE expression would do the right thing.

select (case when 'a' = 'a' then 1 else test_seq.nextval end) from dual; shows exactly the same behavior as the decode.

-V

on 11/3/2003 1:23 PM ctcgag_at_hotmail.com said the following:

>Kenneth Koenraadt wrote:
>
>
>>Hi Ctcgag,
>>
>>Cannot confirm your observation regarding DECODE (at least not in 9i):
>>
>>SQL>
>>SQL> select decode('a','a',3,0/0) from dual;
>>
>>DECODE('A','A',3,0/0)
>>---------------------
>> 3
>>
>>
>
>How strange. I get the same thing as you. But using the sequence
>rather than the div by zero as an indicator, it definitely is
>evaluating the nextval:
>
>SQL> select test_seq.nextval from dual;
>
> NEXTVAL
>----------
> 137
>
>SQL> select decode('a','a',3,test_seq.nextval) from dual;
>
>DECODE('A','A',3,TEST_SEQ.NEXTVAL)
>----------------------------------
> 3
>
>SQL> /
>
>DECODE('A','A',3,TEST_SEQ.NEXTVAL)
>----------------------------------
> 3
>
>SQL> /
>
>DECODE('A','A',3,TEST_SEQ.NEXTVAL)
>----------------------------------
> 3
>
>SQL> /
>
>DECODE('A','A',3,TEST_SEQ.NEXTVAL)
>----------------------------------
> 3
>
>SQL> select test_seq.nextval from dual;
>
> NEXTVAL
>----------
> 142
>
>
>
>
>
>>PL/SQL *does* short-circuit.PL/SQL users guide states that :
>>
>>"Quote"
>>
>>"PL/SQL uses short-circuit evaluation. That is, PL/SQL stops
>>evaluating the expression as soon as the result can be determined
>>(...) In any case, it is a poor programming practice to rely on
>>short-circuit evaluation.
>>
>>"Unquote"
>>
>>I do not agree with the last sentence, but that's personal.
>>
>>
>
>I agree with your disagreement. Obviously it is poor practise to
>rely on it in this case, because it is unreliable. But where reliable,
>I'd say it is excellent practise.
>
>
>Oh well, back to using PL/SQL loops rather than simple SQL
>to implement this. Too bad I don't get paid by the line.
>
>Xho
>
Received on Mon Nov 03 2003 - 16:01:02 CST