[
https://issues.apache.org/jira/browse/DERBY-6569?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13993526#comment-13993526
]
Knut Anders Hatlen commented on DERBY-6569:
-------------------------------------------
One might argue that this is not a bug, since the behaviour is not specified by
the SQL standard.
In any case, it would be useful to find a way to make NULLIF evaluate the
expressions in its operands only once, as that would also improve the
performance of valid NULLIF expressions. That mechanism may also be useful when
implementing simple case expressions (DERBY-1576).
> 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)
