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

Ah, thanks Stefan - I should have checked there instead of on MoS :)

On Sun, Dec 8, 2019 at 3:12 PM Stefan Koehler <contact_at_soocs.de> wrote:

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

-- 
//
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