svn commit: r1631457 - in /db/derby/code/branches/10.10: ./ java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java

[mi...@apache.org]

Author: mikem
Date: Mon Oct 13 16:17:58 2014
New Revision: 1631457

URL: http://svn.apache.org/r1631457
Log:
DERBY-6567: Incorrect nullability for CASE expression with parameter

backported change#1593495 from trunk to 10.10 branch.

Make sure the result of a CASE expression is nullable if (and only if)
at least one of the result expressions is nullable.


Modified:
    db/derby/code/branches/10.10/   (props changed)
    db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
    db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java

Propchange: db/derby/code/branches/10.10/
------------------------------------------------------------------------------
  Merged /db/derby/code/trunk:r1593495

Modified: db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java?rev=1631457&r1=1631456&r2=1631457&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java (original)
+++ db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java Mon Oct 13 16:17:58 2014
@@ -501,6 +501,10 @@ public class ConditionalNode extends Val
 		*/
 		setType(thenElseList.getDominantTypeServices());
 
+        // The result is nullable if and only if at least one of the result
+        // expressions is nullable (DERBY-6567).
+        setNullability(thenElseList.isNullable());
+
 		/*
 		** Generate a CastNode if necessary and
 		** stick it over the original expression

Modified: db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java?rev=1631457&r1=1631456&r2=1631457&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java (original)
+++ db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java Mon Oct 13 16:17:58 2014
@@ -26,6 +26,7 @@ import java.sql.ResultSetMetaData;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.sql.ResultSet;
+import java.sql.Types;
 import java.util.ArrayList;
 import java.util.Arrays;
 
@@ -486,4 +487,44 @@ public class CaseExpressionTest extends 
                 expectedRows, false);
     }
 
+    /**
+     * Test that parameters can be used in CASE expressions.
+     */
+    public void testParameters() throws SQLException {
+        // If all of the result expressions are untyped parameters, the
+        // type cannot be determined, and an error should be raised.
+        assertCompileError("42X87", "values case when true then ? else ? end");
+
+        // If at least one result expression is typed, the parameter should
+        // get its type from it.
+        PreparedStatement ps = prepareStatement(
+                "values case when true then ? else 1 end");
+
+        // DERBY-6567: The result should be nullable, since the parameter
+        // could be set to null. It used to be reported as not nullable.
+        assertEquals(ResultSetMetaData.columnNullable,
+                     ps.getMetaData().isNullable(1));
+
+        ps.setNull(1, Types.INTEGER);
+        JDBC.assertSingleValueResultSet(ps.executeQuery(), null);
+
+        ps.setInt(1, 1);
+        JDBC.assertSingleValueResultSet(ps.executeQuery(), "1");
+
+        // Parameters in the WHEN clause can be untyped. They will
+        // implicitly get the BOOLEAN type.
+        ps = prepareStatement("values case when ? then 1 else 0 end");
+        assertEquals(Types.BOOLEAN,
+                     ps.getParameterMetaData().getParameterType(1));
+
+        ps.setBoolean(1, true);
+        JDBC.assertSingleValueResultSet(ps.executeQuery(), "1");
+
+        ps.setBoolean(1, false);
+        JDBC.assertSingleValueResultSet(ps.executeQuery(), "0");
+
+        ps.setNull(1, Types.BOOLEAN);
+        JDBC.assertSingleValueResultSet(ps.executeQuery(), "0");
+    }
+
 }