COALESCE does short circuit - NOT

From: Uwe Küchler <uwe_at_kuechler.org>
Date: Thu, 19 Jan 2012 16:00:19 +0100
Message-ID: <e48fa1df3d606db4354d5d20322675e3.squirrel_at_mx1bln1.prossl.de>

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

SQL> set heading off
SQL> create sequence seq_test;

Sequence created.

SQL> select NVL( NULL, seq_test.nextval ) x from dual;

         1

SQL> select NVL( 9, seq_test.nextval ) x from dual;

         9

SQL> select seq_test.currval from dual;

         2

SQL> select COALESCE( 9, seq_test.nextval ) x from dual;

         9

SQL> select seq_test.currval from dual;

         3

What the heck is happening here?
Cheers,
Uwe

--

http://oraculix.wordpress.com/

--

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