COALESCE does short circuit - NOT
- From: "Uwe Küchler" <uwe@xxxxxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 19 Jan 2012 16:00:19 +0100
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/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
