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: @OracleSK
Stefan Knecht <knecht.stefan@xxxxxxxxx> 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
