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

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