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)
