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

Perhaps to add a bit of detail - here it wasn't just a performance thing. It literally broke the code, because we have logic in the function that's called in the second argument of nvl(), and that logic is only valid if the first argument that came in, was indeed NULL.

Sigh.

On Sun, Dec 8, 2019 at 3:18 PM Stefan Knecht <knecht.stefan_at_gmail.com> wrote:

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

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