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

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/