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

 

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/

Partager cet article

Published by Pacman - dans SGBD
commenter cet article

commentaires

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