Suivre ce blog
Editer l'article Administration Créer mon blog
8 août 2013 4 08 /08 /août /2013 09:43

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;






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 : 



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


Partager cet article

Published by Pacman - dans SGBD
commenter cet article


Laurent Schneider 08/08/2013 11:03

lnnvl(cond) only accept some conditions, lnnvl(not(1=1)) won't work

Thanks for reminding me this anthologic ora-3113 :)

Pacman 09/08/2013 09:25

You know every single bug in Oracle, don't you :)