Ah, thanks Stefan - I should have checked there instead of on MoS :)
On Sun, Dec 8, 2019 at 3:12 PM Stefan Koehler <contact@xxxxxxxx> 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: @OracleSK
Stefan Knecht <knecht.stefan@xxxxxxxxx> hat am 8. Dezember 2019 um09:01 geschrieben:
broke a whole bunch of code:
Found this interesting. Very subtle difference in behavior, that just
right?
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,
the second argument, regardless of whether the first argument is NULL or
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
not.
consistently inconsistent - e.g. it happens both in a SQL and PL/SQL
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
context.
Cheers
Stefan