Re: Interesting difference between nvl() and coalesce()

Small correction: nvl2() test should be:

select nvl2(1, 2, foo) from dual
/
NVL2(1,2,FOO)

            2

FOO CALLED But it still calls and evaluates argument 3 even if argument 1 clearly is not null.

On Sun, Dec 8, 2019 at 3:01 PM Stefan Knecht <knecht.stefan_at_gmail.com> wrote:

>
> Found this interesting. Very subtle difference in behavior, that just
> broke a whole bunch of code:
>
> create or replace function foo return number as
> begin
> dbms_output.put_line('FOO CALLED');
> return 42;
> end;
> /
>
> set serverout on
>
> select nvl(1, foo) from dual
> /
> select nvl2(1, foo, 2) from dual
> /
> select coalesce(1, foo) from dual
> /
> select case when 1 = 2 then foo else 1 end from dual
> /
>
> At first glance, these 4 statements should do the exact same thing, right?
>
> Yes and no:
>
> SQL> select nvl(1, foo) from dual
> 2 /
>
> NVL(1,FOO)
> ----------
> 1
>
> *FOO CALLED*
> SQL> select nvl2(1, foo, 2) from dual
> 2 /
>
> NVL2(1,FOO,2)
> -------------
> 42
>
> *FOO CALLED*
> SQL>
> SQL> select coalesce(1, foo) from dual
> 2 /
>
> COALESCE(1,FOO)
> ---------------
> 1
>
> SQL> select case when 1 = 2 then foo else 1 end from dual
> 2 /
>
> CASEWHEN1=2THENFOOELSE1END
> --------------------------
> 1
>
>
> So it appears that both NVL() and NVL2() are executing the function in the
> second argument, regardless of whether the first argument is NULL or not.
>
> Coalesce seems "smarter" and only executes the function if it needs to.
>
> Does anyone see a reason why this is?
>
> Tested this on 12.1 and 12.2, both show the same results. Behavior is
> consistently inconsistent - e.g. it happens both in a SQL and PL/SQL
> context.
>
>
> Cheers
>
> Stefan
>
>
>
> --
> //
> zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
> Visit us at zztat.net | _at_zztat_oracle | fb.me/zztat | zztat.net/blog/
>

-- 
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | _at_zztat_oracle | fb.me/zztat | zztat.net/blog/

--
http://www.freelists.org/webpage/oracle-l