Re: COALESCE does short circuit - NOT
Date: Thu, 19 Jan 2012 16:22:08 +0100
Message-ID: <623834619c91a8d77472b1f5dd80dc63.squirrel_at_mx1bln1.prossl.de>
Hi Andy,
"Short Circuit" means, the database evaluates each expr value and determines whether it is NULL, rather than evaluating all of the expr values before determining whether any of them is NULL. http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions023.htm
That's what the Docu says, but in fact this is not the case with Sequences. As you can see, the sequence is incremented, although the first expr is NOT NULL and COALESCE should have exited before evaluating seq_test.nextval.
Cheers,
Uwe
Am Do, 19.01.2012, 16:17, schrieb Andy Klock:
> I don't understand what you mean by short-circuited, but your results are
> what I would expect. COALESCE will return the first non null value in
> the
> list, so it shouldn't fire the sequence.nextval.
>
> SQL> select * from v$version;
>
> Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
> Production
> ...
> SQL> select seq_test.currval from dual;
>
> 3
>
> SQL> select COALESCE(null,seq_test.nextval ) x from dual;
>
> 4
>
> SQL> select COALESCE( 9, seq_test.nextval ) x from dual;
>
> 9
>
> SQL> select seq_test.currval from dual;
>
> 5
>
>
> 2012/1/19 "Uwe Küchler" <uwe_at_kuechler.org>
>
>> Hi Folks,
>> although this topic was already widely discussed in the blogosphere, I'd
>> like to discuss it again.
>> It is said that from 10g on COALESCE uses short-circuit evaluation.
>> But what happens in the following code snippet is that a sequence is
>> always incremented, regardless whether it should be short-ciruited or
>> not
>> (I ran this on 10.2.0.4 and 11.2.0.1):
>>
>>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jan 19 2012 - 09:22:08 CST