Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r572738 - in /db/derby/code/trunk/java:
 engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
 testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java
Date: Tue, 04 Sep 2007 17:08:57 -0000
To: derby-commits@db.apache.org
From: kmarsden@apache.org
Message-Id: <20070904170857.E7E9F1A983A@eris.apache.org>

Author: kmarsden
Date: Tue Sep  4 10:08:56 2007
New Revision: 572738

URL: http://svn.apache.org/viewvc?rev=572738&view=rev
Log:
DERBY-3032   Derby
java.lang.ClassCastException returning null from a case statement in subquery


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

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java?rev=572738&r1=572737&r2=572738&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java Tue Sep  4 10:08:56 2007
@@ -282,16 +282,22 @@
 	 *
 	 * @param thenElseList    The thenElseList to update.
 	 * @param castType        The type to cast SQL parsed NULL's too.
+	 * @param fromList        FromList to pass on to bindExpression if recast is performed
+	 * @param subqueryList    SubqueryList to pass on to bindExpression if recast is performed
+	 * @param aggregateVector AggregateVector to pass on to bindExpression if recast is performed
 	 *
 	 * @exception             StandardException Thrown on error.
 	 */
 	private void recastNullNodes(ValueNodeList thenElseList,
-	                           DataTypeDescriptor castType)
+	                           DataTypeDescriptor castType, FromList fromList,
+	                           SubqueryList subqueryList, Vector aggregateVector)
 	 throws StandardException {
 
 		// Don't do anything if we couldn't find a castType.
 		if (castType == null) return;
-
+		
+		// need to have nullNodes nullable
+		castType.setNullability(true);
 		ValueNode thenNode = (ValueNode)thenElseList.elementAt(0);
 		ValueNode elseNode = (ValueNode)thenElseList.elementAt(1);
 
@@ -299,22 +305,29 @@
 		if (isNullNode(thenNode) &&
 		    shouldCast(castType, thenNode.getTypeServices()))
 		{
+			// recast and rebind. findTypes would have bound as SQL CHAR.
+			// need to rebind here. (DERBY-3032)
 			thenElseList.setElementAt(recastNullNode(thenNode, castType), 0);
+			((ValueNode) thenElseList.elementAt(0)).bindExpression(fromList, subqueryList, aggregateVector);
+			
 		// otherwise recurse on thenNode, but only if it's a conditional
 		} else if (isConditionalNode(thenNode)) {
 			recastNullNodes(((ConditionalNode)thenNode).thenElseList,
-			                castType);
+			                castType,fromList, subqueryList, aggregateVector);
 		}
 
 		// lastly, check if the "else" node is NULL
 		if (isNullNode(elseNode) &&
 		    shouldCast(castType, elseNode.getTypeServices()))
 		{
+			// recast and rebind. findTypes would have bound as SQL CHAR.
+			// need to rebind here. (DERBY-3032)
 			thenElseList.setElementAt(recastNullNode(elseNode, castType), 1);
+			((ValueNode) thenElseList.elementAt(1)).bindExpression(fromList, subqueryList, aggregateVector);
 		// otherwise recurse on elseNode, but only if it's a conditional
 		} else if (isConditionalNode(elseNode)) {
 			recastNullNodes(((ConditionalNode)elseNode).thenElseList,
-			                castType);
+			                castType,fromList,subqueryList,aggregateVector);
 		}
 	}
 
@@ -396,16 +409,21 @@
 				aggregateVector);
 
 		} else {
-			/* Following call to "findType()" will indirectly bind the
+			/* Following call to "findType()"  and "recastNullNodes" will indirectly bind the
 			 * expressions in the thenElseList, so no need to call
 			 * "thenElseList.bindExpression(...)" after we do this.
 			 * DERBY-2986.
 			 */
 			recastNullNodes(thenElseList,
-				findType(thenElseList, fromList, subqueryList, aggregateVector));
+				findType(thenElseList, fromList, subqueryList, aggregateVector),fromList,
+					subqueryList,
+					aggregateVector);
+			
  		}
-
+		
+		
 		// Can't get the then and else expressions until after they've been bound
+		// expressions have been bound by findType and rebound by recastNullNodes if needed.
 		ValueNode thenExpression = (ValueNode) thenElseList.elementAt(0);
 		ValueNode elseExpression = (ValueNode) thenElseList.elementAt(1);
 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java?rev=572738&r1=572737&r2=572738&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java Tue Sep  4 10:08:56 2007
@@ -22,6 +22,7 @@
 package org.apache.derbyTesting.functionTests.tests.lang;
 
 import java.sql.Connection;
+import java.sql.ResultSetMetaData;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.sql.ResultSet;
@@ -279,7 +280,37 @@
         }
      
     }
+
     
-  
+    /**
+     * Test fix for DERBY-3032. Fix ClassCastException if SQL NULL is returned from conditional.
+     * 
+     * @throws SQLException
+     */
+    public void testDerby3032() throws SQLException 
+    {
+        Statement s = createStatement();
+        
+
+        s.executeUpdate("create table t (d date, vc varchar(30))");
+        s.executeUpdate("insert into t values(CURRENT_DATE, 'hello')");
+        ResultSet rs = s.executeQuery("SELECT d from t where d = (SELECT CASE WHEN 1 = 1 THEN CURRENT_DATE ELSE NULL END from t)");
+        JDBC.assertDrainResults(rs,1);
+        
+        // Make sure null gets cast properly to date type to avoid cast exception. DERBY-3032
+        rs = s.executeQuery("SELECT d from t where d = (SELECT CASE WHEN 1 = 1 THEN NULL  ELSE CURRENT_DATE  END from t)");
+        JDBC.assertEmpty(rs);
+        
+        rs = s.executeQuery("SELECT d from t where d = (SELECT CASE WHEN 1 = 0 THEN CURRENT_DATE  ELSE NULL END from t)");
+        JDBC.assertEmpty(rs);
+        
+        // Make sure metadata has correct type
+        rs = s.executeQuery("SELECT CASE WHEN 1 = 1 THEN NULL  ELSE CURRENT_DATE  END from t");
+        ResultSetMetaData rsmd = rs.getMetaData();
+        assertEquals(java.sql.Types.DATE, rsmd.getColumnType(1));
+        // should be nullable since it returns NULL #:)
+        assertTrue(rsmd.isNullable(1) == ResultSetMetaData.columnNullable);
+        JDBC.assertSingleValueResultSet(rs, null);        
+    }
     
 }