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

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.10.2.0
• Fix Version/s: 10.12.1.1
• Component/s: SQL
• Labels: derby_backport_reject_10_11
• Issue & fix info: Repro attached
• Bug behavior facts: Deviation from standard
• Assignee: Knut Anders Hatlen
• Reporter: Knut Anders Hatlen
• Created: 09/May/14 11:11
• Updated: 09/Sep/16 09:51
• Resolved: 17/Sep/14 11:29

Description

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:

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

Issue Links

• relates to: DERBY-1576 Extend the CASE expression syntax for "simple case"