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