[jira] [Created] (DERBY-6569) NULLIF may return incorrect results if first operand calls non-deterministic function

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

Knut Anders Hatlen created DERBY-6569:
-----------------------------------------

             Summary: NULLIF may return incorrect results if first operand calls non-deterministic function
                 Key: DERBY-6569
                 URL: https://issues.apache.org/jira/browse/DERBY-6569
             Project: Derby
          Issue Type: Bug
          Components: SQL
    Affects Versions: 10.10.2.0
            Reporter: Knut Anders Hatlen


The SQL standard doesn't allow non-deterministic function calls in the operands of NULLIF. Derby does however allow such calls, but the results may not be as one might expect.

Take an expression such as NULLIF(expr, 1). It shouldn't ever return 1. If expr is 1, it should return NULL, and if expr is not 1, it should return expr.

If expr contains a call to a non-deterministic function, it may actually end up returning 1 sometimes:

{noformat}
ij> SELECT NULLIF(INT(RANDOM()*2), 1) FROM SYS.SYSTABLES;
1          
-----------
1          
1          
1          
NULL       
NULL       
NULL       
NULL       
0          
1          
NULL       
NULL       
0          
0          
NULL       
0          
1          
0          
NULL       
1          
0          
NULL       
NULL       
NULL       

23 rows selected
{noformat}



--
This message was sent by Atlassian JIRA
(v6.2#6252)