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

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