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

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Tue, 10 Dec 2019 04:18:05 +0300
Message-ID: <CAOVevU4JjjapWQ1aM3gPjYgMUUKzze71PM84ig6Pd4YHZGToUw_at_mail.gmail.com>

> My conclusion .. just sloppy programming.

Hm, I can't agree...
1. Let's generalize nvl/nvl2 to abstract function1(param1...paramN). In this case it's not so easy and obvious even for compiled languages with more powerful optimization features.
Although all the languages I know use short-circuit evaluation for boolean || &&, because "Something() || die()" or "DoThis() && AndThat()" patterns are very short, easy and popular.
But usually they don't use short-circuit evaluation for everything, because you can miss floating errors in the periodically unreachable code. For example, cpp calculates all input parameters by default: [xtender_at_orasql ctest]$ cat short.cpp #include <iostream>
using namespace std;

int f1(){
  cout << "f1() called.\n";
  return 1;
}

int f2(int x){
  cout << "f2() called.\n";
  return 1/x;
}

int f(int a, int x, int y){
  cout << "f\n";
  return a>1 ? x : y;
}

int main (int argc,char* argv[])
{
  cout << "res: " << f(argc, f1(), f2(0)); }
[xtender_at_orasql ctest]$ g++ short.cpp && ./a.out donotdivide f2() called.
Floating point exception

And only with optimization levels 2+ it doesn't: [xtender_at_orasql ctest]$ g++ -O2 short.cpp && ./a.out donotdivide f2() called.
f1() called.
f
res: 1

In fact compiler doesn't know - do you really want to call f2() everytime or not. Even if it looks like unnesessary useless function... You know PL/SQL has "NULL statement" and all CPUs have NOP instruction... So you have a choice: optimize such code and avoid all the code unnessesary for further calculations or detect all possible errors.

2. We know that CBO has 2 optimizations for NVL() in where clause: for NVL(literal, ... ) = literal and for NVL(:bind, ... ) = ... For example:
SQL> select 1 from dual where nvl(1, 1/0) = 1;

         1

---

         1

SQL> _at_last

SQL_ID 6cys35tx6w1pw, child number 0

---

select 1 from dual where nvl(1, 1/0) = 1

Plan hash value: 1388734953

---

| Id | Operation | Name | E-Rows | Cost (%CPU)| E-Time |

---

|   0 | SELECT STATEMENT |      |        |     2 (100)|          |
|   1 |  FAST DUAL       |      |      1 |     2   (0)| 00:00:01 |
------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

---

   1 - SEL$1 / DUAL_at_SEL$1

As you can see there is no filter predicate in the plan. So Oracle developers could easily make so simple optimization, for example using syntactic sugar - replacing nvl to case-when-then like they did for nullif:
SQL> explain plan for select * from dual where nullif(dummy,'z')='x';

Explained.
PLAN_TABLE_OUTPUT

---

Plan hash value: 272002086

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---

|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   1 - filter(CASE "DUMMY" WHEN 'z' THEN NULL ELSE "DUMMY" END ='x')

13 rows selected.

But they didn't for some reasons. Probably because they already created coalesce() and they didn't want to break customer's systems with new different behaviour for so old and popular function, which is older than coalesce from ANSI standard.

On Mon, Dec 9, 2019 at 9:17 PM Mark W. Farnham <mwf_at_rsiz.com> wrote:

> to clarify, this is in response to
>
> 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;
>
> my point is that may be shorthand in source code text, but that doing a
> function call to evaluate a Boolean in hand is not a shortcut in execution
> path versus a function call (unless you have a very smart multi-pass
> compiler that does co-generation of code, which is not applicable handing
> sql to the Oracle parser.)
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Mark W. Farnham
> Sent: Monday, December 09, 2019 9:15 AM
> To: jonathan_at_jlcomp.demon.co.uk; 'Oracle L'
> Subject: RE: Interesting difference between nvl() and coalesce()
>
> 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.
>
> --> If calling a function instead of adding a Boolean based on which
> environment seems as fast to you, be my guest.
>
> Whenever in the nevernull environment, you don't need to call coalesce.
> When
> in the alwaysnull environment you can call your function directly, also
> avoiding the call to coalesce. That's for speed. I would also hold that the
> code would read more simply. But I've always had a bias toward more source
> code and less execution pathway, probably because I tweaked performance of
> the little inmost bits of an operating system near the beginning of my
> career.
>
> On a modern machine it would be interesting to test whether repairing your
> code (er, okay changing your code) to Booleans and eliminating the unneeded
> coalesce call when you change the nvl ran faster.
>
> It would also be interesting to know which is clearer to understand, but
> that would be a difficult subjective test requiring double blind pools of
> virgin readers of the code.
>
> Probably the least cost change is whichever takes less effort to implement,
> and a global text replace of nvl with coalesce may indeed be the cheapest
> ownership solution.
>
> mwf
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Jonathan Lewis
> Sent: Monday, December 09, 2019 5:00 AM
> To: Oracle L
> Subject: Re: Interesting difference between nvl() and coalesce()
>
> >> 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
>
> >>>> 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 :)"
>
>
> That should cheer Stefan up a little bit.
>
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Maris Elsins <elmaris_at_gmail.com>
> Sent: 09 December 2019 07:59
> To: andysayer_at_gmail.com
> Cc: knecht.stefan_at_gmail.com; Chris Taylor; Mark W. Farnham; Stefan
> Koehler;
> oracle-l-freelists; woodwardinformatics_at_strychnine.co.uk
> Subject: 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
> <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
> <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<mailto: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<mailto: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<mailto: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<mailto:
> 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<mailto: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<mailto:
> 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<http://www.strychnine.co.uk/>
>
>
>
> Am 08.12.2019 um 15:37 schrieb Mark W. Farnham
> <mwf_at_rsiz.com<mailto: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>
> [mailto: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<mailto: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<http://www.soocs.de/>
> Twitter: _at_OracleSK
>
> Stefan Knecht <knecht.stefan_at_gmail.com<mailto: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<http://zztat.net/> | _at_zztat_oracle |
> fb.me/zztat<http://fb.me/zztat> | zztat.net/blog/<http://zztat.net/blog/>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org


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

Received on Tue Dec 10 2019 - 02:18:05 CET