Why does NVL always evaluate 2nd parameter

Question

Does anyone know, why Oracle's NVL (and NVL2) function always evaluate the second parameter, even if the first parameter is not NULL?

Simple test:

CREATE FUNCTION nvl_test RETURN NUMBER AS
BEGIN
  dbms_output.put_line('Called');
  RETURN 1;
END nvl_test;

SELECT NVL( 0, nvl_test ) FROM dual

returns 0, but also prints Called.

nvl_test has been called, even though the result is ignored since first parameter is not NULL.

Answer 1

They are obviously not short-circuiting, but I can't find any references in Oracle documentation.

Check out this discussion: http://forums.oracle.com/forums/thread.jspa?messageID=3478040

Answer 2

In general, it would make sense that the second parameter is evaluated before calling the function, because in general that is how functions are called: all arguments to the function are evaluated and the evaluated values are sent to the function.

However, in the case of a very common system function like NVL, I would have thought PL/SQL could optimise, treating the function call as a special case. But perhaps that is more difficult than it sounds (to me), as I'm sure this optimisation would have occurred to the developers of Oracle.

Answer 3

In the majority of cases the arguments are the other way round. That is, the first argument is a function, variable or column, and the second argument is a literal. So maybe Oracle's engineers decided to evaluate all the arguments first, in order to trap this sort of thing:

SQL> select nvl(get_number, 'A') from dual
  2  /
select nvl(get_number, 'A') from dual
                       *
ERROR at line 1:
ORA-01722: invalid number


SQL>

Answer 4

Here is a post where Tom Kyte confirms that decode and case short circuit but not nvl but he doesn't give justification or documentation for why. Just states it to be:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:926029357278#14932880517348

So in your case you should use decode or case instead of nvl if an expensive function will be called in your query.

Answer 5

It's always been that way, so Oracle has to keep it that way to remain backwards compatible.

Use COALESCE instead to get the short-circuit behaviour.