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

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 <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/ <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 <https://www.linkedin.com/in/michaeldoshea>
> Woodward Informatics Ltd, http://www.strychnine.co.uk <http://www.strychnine.co.uk/>
> 
> 
> 

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