Why LNNVL should not be a function

08.08.2013

Let's have a look at Laurent Schneider's funny query (that he had already posted on several blogs :))

select * from dual where lnnvl(1=0) and lnnvl(1=0) is null;

D

-

X

Does it mean that lnnvl(1=0) is both true and unknown ?

Of course not.

Actually, it is the same issue as the REGEXP_LIKE "function" i played with a few days ago :

http://pacmann.over-blog.com/article-why-regexp_like-is-more-than-a-function-119416964.html

lnnvl Should be used as a condition... and when doing so, it does behave as expected :

SELECT CASE WHEN lnnvl(1=0) THEN 'True' WHEN NOT lnnvl(1=0) THEN 'False' ELSE 'Null' END "1=0"

, CASE WHEN lnnvl(1=1) THEN 'True' WHEN NOT lnnvl(1=1) THEN 'False' ELSE 'Null' END "1=1"

, CASE WHEN lnnvl(1=null) THEN 'True' WHEN NOT lnnvl(1=null) THEN 'False' ELSE 'Null' END "1=null"

, CASE WHEN lnnvl(null=null) THEN 'True' WHEN NOT lnnvl(null=null) THEN 'False' ELSE 'Null' END "null=null"

FROM dual;

1=0 1=1 1=nul null=

----- ----- ----- -----

True False True True

Remember, lnnvl(cond) is evaluated to true whenever cond is false or null and evaluated to false otherwise, making a simpler syntax for handling null cases in predicates.

But now, try this :

SELECT lnnvl(1=0) "1=0"

, lnnvl(1=1) "1=1"

, lnnvl(1=null) "1=null"

, lnnvl(null=null) "null=null"

FROM dual;

1=0 1=1 1=null null=null

---------- ---------- ---------- ----------

lnnvl always returns NULL !

But i would not expect lnnvl to return something else... but rather raise an exception, since it cannot return a boolean value within a SQL statement !

Also in my opinion, Oracle documentation is wrong when stating :

The function can be used only in the WHERE clause of a query. It takes as an argument a condition and returns TRUE if the condition is FALSE or UNKNOWN and FALSE if the condition is TRUE.

It just behaves, and should be used, the same way as REGEXP_LIKE, LIKE, (1 = 0) does : these are logical conditions being evaluated, not functions that return any value.

SELECT regexp_like('a', 'a')

FROM dual;

SELECT regexp_like('a', 'a')

*

ERROR at line 1:

ORA-00904: "REGEXP_LIKE": invalid identifier

Anyway, I wish Oracle SQL logical algebra would just work as any logical algebra does, have REGEXP_LIKE and LNNVL really return a boolean, and having both statements being equivalent : LNNVL(cond) or LNNVL(cond) = true...

Lnnvl has always been kind of myth in Oracle, until it became documented in 10gR1 release... but buggy !

Funnily enough, even Tom Kyte was not aware of lnnvl beeing documented (read the comments) :

http://awads.net/wp/2006/10/11/previously-undocumented-lnnvl-sql-function-buggy/