Re: COALESCE does short circuit - NOT

  • From: "Uwe Küchler" <uwe@xxxxxxxxxxxx>
  • To: "Andy Klock" <andy@xxxxxxxxxxxxxxx>
  • Date: Thu, 19 Jan 2012 16:22:08 +0100

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@xxxxxxxxxxxx>
>
>> 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):
>>
>>
>


--
//www.freelists.org/webpage/oracle-l


Other related posts: