Re: COALESCE does short circuit - NOT

From: Uwe Küchler <uwe_at_kuechler.org>
Date: Thu, 19 Jan 2012 18:22:43 +0100
Message-ID: <e500a260b1f0ca287021492e865cd8b0.squirrel_at_mx1bln1.prossl.de>



Hej Maxim,

this is a good hint. Looks like this rule takes precedence over the short-circuit behaviour of functions. Well, sequences are something special in many ways...

Too bad, in cases where a sequence is only rarely needed lots of its values get thrown away then.

I've got another simple test case to show this:

SQL> create table testtab( x number );

Tabelle wurde erstellt.

SQL> insert into testtab values(NULL);

1 Zeile wurde erstellt.

SQL> insert into testtab values(42);

1 Zeile wurde erstellt.

SQL> insert into testtab values(4711);

1 Zeile wurde erstellt.

SQL> select x, COALESCE( x, seq_test.nextval ) y, seq_test.currval cur   2 from testtab;

         X Y CUR
---------- ---------- ----------

                   12         12
        42         42         13
      4711       4711         14


Cheers,
Uwe

--
http://oraculix.wordpress.com/

Am Do, 19.01.2012, 17:37, schrieb Maxim:

> Uwe, i think, it simply works this way. It doesn't matter, should it be
> short circuited or not, single reference in the select is sufficient for
> oracle to increment the sequence (once per row in the resultset) - it can
> be as well interpreted this way from documentation:
> http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns002.htm#SQLRF50946
>
> Within a single SQL statement containing a reference to NEXTVAL, Oracle
> increments the sequence once:
>
> For each row returned by the outer query block of a SELECT statement. Such
> a query block can appear in the following places:
>
> -
>
> A top-level SELECT statement
>
>
> The same effect you can observe in dead leafs of case statement (e.g. case
> when 1=2 then seq.nextval -- will be incremented as well)
>
> Best regards
>
> Maxim
>
> 2012/1/19 "Uwe Küchler" <uwe_at_kuechler.org>
>
>> Hi Folks,
>>
>>
>>
>>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-l
Received on Thu Jan 19 2012 - 11:22:43 CST

Original text of this message