RE: COALESCE does short circuit - NOT

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 19 Jan 2012 10:46:59 -0500
Message-ID: <0cc701ccd6c1$9701c6d0$c5055470$_at_rsiz.com>



I think this is because the value of nextval is submitted as the input argument, that is, it has already been evaluated and coalesce isn't doing anything with it. I haven't proven that to be true, it is just what I'm thinking until I get a chance to test it.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of "Uwe Küchler"
Sent: Thursday, January 19, 2012 10:22 AM To: Andy Klock
Cc: oracle-l
Subject: Re: COALESCE does short circuit - NOT

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-l


--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jan 19 2012 - 09:46:59 CST

Original text of this message