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

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.