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

From: Maris Elsins <elmaris_at_gmail.com>
Date: Mon, 9 Dec 2019 09:59:17 +0200
Message-ID: <CABQhObu6fYM7kRKkNu5TGPiCthjXHBiafr0-G8-PH-YZsJ2afA_at_mail.gmail.com>



Hi,

(I didn't read all the updates, sorry if this was already mentioned)

NVL
<https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/NVL.html#GUID-3AB61E54-9201-4D6A-B48A-79F4C4A034B2> evaluates all arguments but COALESCE
<https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/COALESCE.html#GUID-3F9007A7-C0CA-4707-9CBA-1DBF2CDE0C87> (returns
the first non-null argument) stops at the first non-null argument.

I learned this during UKOUG Techfest 19 where I attended a "SQL Syntax: Oracle vs. ANSI. Fight!" by Alex Nuijten & Chris Saxon. Useful conference. ROI++ :D

---
Maris Elsins




On Mon, Dec 9, 2019 at 9:17 AM Andy Sayer <andysayer_at_gmail.com> wrote:


> “In hindsight, I guess I'm more flabbergasted by the fact that I have
> either completely forgotten about this, or have never run into this before.
> And both of those worry me equally :)”
>
> I’m in the “didn’t everyone know this difference?” camp. I think the main
> problem here is that the issue is such a simple difference, it’s one you’ll
> either learn very early on or never (or when you hit something that causes
> a problem). The description of coalesce doing short circuiting is in the
> docs but coalesce is such an early learnt function that you probably don’t
> look it up in the docs.
>
> There’s probably a whole bunch of these nuances with simple functions that
> you either learn very early on in your career or don’t.
>
> In fairness to NVL, I think it’s mostly used to replace nulls with a
> constant, so short circuit evaluation wouldn’t be a big deal most of the
> time (although probably never hurts).
>
> Just my 2c,
> Andy
>
> On Mon, 9 Dec 2019 at 03:18, Stefan Knecht <knecht.stefan_at_gmail.com>
> wrote:
>
>> Yeah that's exactly it, Chris.
>>
>> I can see it making sense to evaluate both expressions in a SQL context,
>> for perhaps some or other performance benefit.
>>
>> But if you look at it purely from a procedural perspective:
>>
>> x := nvl(some_var, some_func);
>>
>> It doesn't make a whole lot of sense to execute f2 if f1 is not null.
>>
>> In this particular case, I used this simply as a shorthand for if
>> some_var is null then some_func else some_var end if;
>>
>> And I can definitely now answer the question Michael asked in his post
>> "how difficult would it be to find that bug?": hella, hella difficult.
>>
>> As for Mark's comments - the reason we do this is the fact that the code
>> needs to run in two distinct environments. In one, the value we test will
>> never be null, and in some it will always be null. In the latter case, we
>> need to use the function to do a little bit of extra work. Using coalesce()
>> in this case in lieu of nvl() will do exactly what we need.
>>
>> In hindsight, I guess I'm more flabbergasted by the fact that I have
>> either completely forgotten about this, or have never run into this before.
>> And both of those worry me equally :)
>>
>>
>> Cheers
>>
>> Stefan
>>
>>
>>
>>
>>
>> On Mon, Dec 9, 2019 at 1:37 AM Chris Taylor <
>> christopherdtaylor1994_at_gmail.com> wrote:
>>
>>> oooooh like I said, I figured I was missing something. LOL
>>>
>>> I didn't realize the issue was more about the original NVL/NVL2
>>> functions.
>>>
>>> I can definitely see that side of it on the one hand, but on the other,
>>> those are also designed that way and have been around for a long time.
>>>
>>> I guess I'm just surprised anyone would be grumpy about a provided
>>> function , when that function documented behavior is , erm, well,
>>> documented :)
>>>
>>> Chris
>>>
>>> On Sun, Dec 8, 2019, 11:12 AM Michael D O'Shea/Woodward Informatics Ltd <
>>> woodwardinformatics_at_strychnine.co.uk> wrote:
>>>
>>>> Hi Chris, not wishing to put words into Stefan’s mouth, I believe he is
>>>> just pointing out an unexpected behavioural difference between two inbuilt
>>>> Oracle functions.
>>>>
>>>> The surprise is that NVL, NVL2, ... DECODE would be DESIGNED IN THE
>>>> FIRST PLACE to execute an expression or function twice. It just makes no
>>>> sense. I cannot think of another programming language where a form of a
>>>> null coalescing operator would execute a function/expression twice. This is
>>>> far from normal behaviour or expectation. Actually it is a man-trap,
>>>> especially if the function caused side effects from dual execution or order
>>>> of execution.
>>>>
>>>> Stefan asked why.
>>>>
>>>> I postulate just sloppy programming by Oracle.
>>>>
>>>> Of course chronologically NVL etc. came first. Perhaps the programmers
>>>> that wrote COALESCE did it properly second time around, or perhaps it is
>>>> prescribed this way in the ANSI/ISO specs?
>>>>
>>>> Mike
>>>>
>>>>
>>>> Michael D. O’Shea, https://www.linkedin.com/in/michaeldoshea
>>>> Woodward Informatics Ltd, http://www.strychnine.co.uk
>>>>
>>>>
>>>> Am 08.12.2019 um 16:52 schrieb Chris Taylor <
>>>> christopherdtaylor1994_at_gmail.com>:
>>>>
>>>> I'm a little confused by this thread (though I understand the effect) .
>>>>
>>>> Coalesce by design/creation is supposed to stop at the first not null
>>>> evaluation. That's what it's for. It's not for evaluating all given
>>>> arguments passed to it.
>>>>
>>>> So if you have code that needs to evaluate "all the things", then
>>>> coalesce isn't the code you're looking for, correct?
>>>>
>>>> I know I'm probably missing something or not tracking, but that
>>>> function is designed to stop evaluating at the first not null evaluation.
>>>>
>>>> Chris
>>>>
>>>> On Sun, Dec 8, 2019, 10:01 AM Michael D O'Shea/Woodward Informatics Ltd
>>>> <woodwardinformatics_at_strychnine.co.uk> wrote:
>>>>
>>>>>
>>>>> > 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.
>>>>>
>>>>> Yes.
>>>>>
>>>>> I ranted about this a few years ago now here:
>>>>> http://www.strychnine.co.uk/2016/10/junk-dna-junk-oracle-part-0000002/
>>>>>
>>>>> > Does anyone see a reason why this is?
>>>>>
>>>>> My conclusion …. just sloppy programming. Of course given the codebase
>>>>> built upon NVL, other NULL handling functions, and those with a failover
>>>>> value (*eg*. DECODE), the behaviour cannot be fixed without breaking
>>>>> the existing user codebase, as you have observed.
>>>>>
>>>>> Mike
>>>>>
>>>>>
>>>>> Michael D. O’Shea, https://www.linkedin.com/in/michaeldoshea
>>>>> Woodward Informatics Ltd, http://www.strychnine.co.uk
>>>>>
>>>>>
>>>>>
>>>>> Am 08.12.2019 um 15:37 schrieb Mark W. Farnham <mwf_at_rsiz.com>:
>>>>>
>>>>> As for repairing your code, if a bit of code is only valid for an
>>>>> inbound null argument, that check for is not null turning the guts into a
>>>>> no-op is the cohesive solution (also known in ancient times as a
>>>>> "firewall."). You can do that with a wrapper and protect the fragile
>>>>> function within a package body or use a wide variety of ways to project the
>>>>> fragility, or you can do it in the function. Try to avoid doing it twice
>>>>> (or n) though: repetitive firewalls instead of encapsulation can become
>>>>> significant and might underflow routine detection in profilers despite
>>>>> being wasteful in aggregate. Avoid also replicating the "if is not null"
>>>>> inline source code wrapper, which is an example of writing redundant
>>>>> "sprinkled all over" application code that retains the overhead even after
>>>>> the function is encapsulated.
>>>>>
>>>>> THE PRECISE formulation of where to put "firewalls" has in the past
>>>>> been something of a religious war. I am agnostic on that religious war
>>>>> other than holding it should be done a single way in a given application
>>>>> suite.
>>>>>
>>>>> mwf
>>>>>
>>>>> -----Original Message-----
>>>>> From: oracle-l-bounce_at_freelists.org [
>>>>> mailto:oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>]
>>>>> On Behalf Of Stefan Koehler
>>>>> Sent: Sunday, December 08, 2019 3:26 AM
>>>>> To: knecht.stefan_at_gmail.com
>>>>> Cc: oracle-l-freelists
>>>>> Subject: Re: Interesting difference between nvl() and coalesce()
>>>>>
>>>>> Hello Stefan,
>>>>> ah OK. Just to get the complete picture, please be also aware about
>>>>> the impact on the CBO if you gonna change the code.
>>>>>
>>>>> https://jonathanlewis.wordpress.com/2018/02/13/coalesce-v-nvl/
>>>>>
>>>>> 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:20 geschrieben:
>>>>>
>>>>>
>>>>> 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.
>>>>>
>>>>> --
>>>>> http://www.freelists.org/webpage/oracle-l
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> http://www.freelists.org/webpage/oracle-l
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>
>> --
>> //
>> 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
Received on Mon Dec 09 2019 - 08:59:17 CET

Original text of this message