svn commit: r1625520 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj testing/org/apache/derbyTesting/functionTests/tests/lang/NullIfTest.java

[ka...@apache.org]

Author: kahatlen
Date: Wed Sep 17 11:29:05 2014
New Revision: 1625520

URL: http://svn.apache.org/r1625520
Log:
DERBY-6569: NULLIF may return incorrect results if first operand calls non-deterministic function

Make sure the left operand of the NULLIF expression is only evaluated
once, so that it does not matter if it is non-deterministic.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullIfTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=1625520&r1=1625519&r2=1625520&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Wed Sep 17 11:29:05 2014
@@ -12565,6 +12565,12 @@ valueSpecification() throws StandardExce
         ValueNodeList whenList = new ValueNodeList(cm);
         ValueNodeList thenElseList = new ValueNodeList(cm);
 
+        // DERBY-6569: The left operand will be grafted into the conditional
+        // twice (both in the WHEN clause and in the THEN clause), but we
+        // only want it to be evaluated once. Wrap it in a CachedValueNode
+        // to prevent multiple evaluations.
+        leftExpression = new CachedValueNode(leftExpression);
+
         // Generate L = R for the WHEN clause.
         whenList.addElement(new BinaryRelationalOperatorNode(
                                     BinaryRelationalOperatorNode.K_EQUALS,

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullIfTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullIfTest.java?rev=1625520&r1=1625519&r2=1625520&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullIfTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullIfTest.java Wed Sep 17 11:29:05 2014
@@ -348,6 +348,34 @@ public class NullIfTest extends BaseJDBC
     }
 
     /**
+     * Test how NULLIF behaves if the left operand calls a non-deterministic
+     * function. DERBY-6569.
+     */
+    public void testNonDeterministicLeftOperand() throws SQLException {
+        Statement s = createStatement();
+        ResultSet rs = s.executeQuery(
+                "SELECT NULLIF(INT(RANDOM()*2), 1) FROM ("
+                + "VALUES 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20"
+                + ") AS V(X)");
+
+        // INT(RANDOM()*2) evaluates to 0 or 1. Since NULLIF(0, 1) evaluates
+        // to 0 and NULLIF(1, 1) evaluates to NULL, one would expect
+        // NULLIF(INT(RANDOM()*2), 1) to evaluate to either 0 or NULL.
+        // Before DERBY-6569, it sometimes evaluated to 1.
+        int count = 0;
+        while (rs.next()) {
+            int i = rs.getInt(1);
+            if (!rs.wasNull() && i != 0) {
+                fail("Expected NULL or 0, got " + i);
+            }
+            count++;
+        }
+        rs.close();
+
+        assertEquals(20, count);
+    }
+
+    /**
      * Runs the test fixtures in embedded and client.
      * 
      * @return test suite