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

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