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

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

--
http://www.freelists.org/webpage/oracle-l