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

Hello Stefan,
yes, this is a known behavior (called short-circuiting), works as designed and may be impact the performance for specific data sets / queries.

Jonathan has written a blog post about this some time ago: https://jonathanlewis.wordpress.com/2014/01/01/nvl-2/

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> Stefan Knecht <knecht.stefan_at_gmail.com> hat am 8. Dezember 2019 um 09:01 geschrieben:
>
>
> 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

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