You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ka...@apache.org on 2014/06/02 11:45:46 UTC
svn commit: r1599142 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/
shared/org/apache/derby/shared/common/reference/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: kahatlen
Date: Mon Jun 2 09:45:45 2014
New Revision: 1599142
URL: http://svn.apache.org/r1599142
Log:
DERBY-2002: Case expression allows NULL in all parts of <result>
Require at least one expression with a known type (that is, not NULL or
a ? parameter) in the THEN and ELSE clauses of a CASE expression.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.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=1599142&r1=1599141&r2=1599142&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 Mon Jun 2 09:45:45 2014
@@ -21,7 +21,6 @@
package org.apache.derby.impl.sql.compile;
-import java.sql.Types;
import java.util.List;
import org.apache.derby.iapi.error.StandardException;
import org.apache.derby.iapi.reference.ClassName;
@@ -110,41 +109,6 @@ class ConditionalNode extends ValueNode
}
/**
- * Find a type to which we can cast the untyped NULLs generated by
- * the parser (for clauses such as ELSE NULL). This does not have to
- * be the type that the CASE expression ends up returning. It is
- * enough that it is a type that can be converted into the type of the
- * CASE expression in order to keep the type checking in the compiler
- * happy.
- *
- * @param fromList The fromList (required for Column References).
- *
- * @exception StandardException Thrown on error.
- */
- private DataTypeDescriptor findType(
- FromList fromList, SubqueryList subqueryList, List<AggregateNode> aggregates)
- throws StandardException
- {
- /* We need to "prebind" because we want the Types. Provide
- * dummy SubqueryList and AggreateList (we don't care)
- */
- thenElseList.bindExpression(fromList, subqueryList, aggregates);
-
- // Find the first typed expression.
- DataTypeDescriptor dtd = thenElseList.getTypeServices();
-
- if (dtd == null) {
- // If none of the expressions have a type, we should probably have
- // raised an error (DERBY-2002). However, Derby has always used the
- // type CHAR(1) in this case, so return that for now.
- dtd = DataTypeDescriptor.getBuiltInDataTypeDescriptor(
- Types.CHAR, 1);
- }
-
- return dtd;
- }
-
- /**
* This method makes sure any SQL NULLs will be cast to the correct type.
*
* @param castType The type to cast SQL parsed NULL's too.
@@ -159,9 +123,6 @@ class ConditionalNode extends ValueNode
SubqueryList subqueryList, List<AggregateNode> aggregates)
throws StandardException {
- // Don't do anything if we couldn't find a castType.
- if (castType == null) return;
-
// need to have nullNodes nullable
castType = castType.getNullabilityType(true);
@@ -231,12 +192,21 @@ class ConditionalNode extends ValueNode
caseOperandParameters.getDominantTypeServices());
}
- // 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(findType(fromList, subqueryList, aggregates),
- fromList, subqueryList, aggregates);
+ thenElseList.bindExpression(fromList, subqueryList, aggregates);
+
+ // Find the type of the first typed value in thenElseList and cast
+ // all untyped NULL values to that type. We don't need to find the
+ // dominant type here, since a top-level cast to that type will be
+ // added later, if necessary.
+ DataTypeDescriptor nullType = thenElseList.getTypeServices();
+ if (nullType == null) {
+ // There are no values with a known type in the list. Raise
+ // an error.
+ throw StandardException.newException(
+ SQLState.LANG_ALL_RESULT_EXPRESSIONS_UNTYPED);
+ } else {
+ recastNullNodes(nullType, fromList, subqueryList, aggregates);
+ }
// Set the result type of this conditional to be the dominant type
// of the result expressions.
@@ -257,14 +227,6 @@ class ConditionalNode extends ValueNode
}
}
- /* We can't determine the type for the result expression if
- * all result expressions are ?s.
- */
- if (thenElseList.containsAllParameterNodes())
- {
- throw StandardException.newException(SQLState.LANG_ALL_RESULT_EXPRESSIONS_PARAMS, "conditional");
- }
-
// Set the type of the parameters.
thenElseList.setParameterDescriptor(getTypeServices());
Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml?rev=1599142&r1=1599141&r2=1599142&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml Mon Jun 2 09:45:45 2014
@@ -2124,8 +2124,7 @@ Guide.
<msg>
<name>42X87</name>
- <text>At least one result expression (THEN or ELSE) of the '{0}' expression must not be a '?'. </text>
- <arg>expression</arg>
+ <text>At least one result expression (THEN or ELSE) of the CASE expression must have a known type.</text>
</msg>
<msg>
Modified: db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?rev=1599142&r1=1599141&r2=1599142&view=diff
==============================================================================
--- db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java (original)
+++ db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java Mon Jun 2 09:45:45 2014
@@ -921,7 +921,7 @@ public interface SQLState {
String LANG_CANT_DROP_BACKING_INDEX = "42X84";
String LANG_CONSTRAINT_SCHEMA_MISMATCH = "42X85";
String LANG_DROP_OR_ALTER_NON_EXISTING_CONSTRAINT = "42X86";
- String LANG_ALL_RESULT_EXPRESSIONS_PARAMS = "42X87";
+ String LANG_ALL_RESULT_EXPRESSIONS_UNTYPED = "42X87";
String LANG_CONDITIONAL_NON_BOOLEAN = "42X88";
String LANG_NOT_TYPE_COMPATIBLE = "42X89";
String LANG_TOO_MANY_PRIMARY_KEY_CONSTRAINTS = "42X90";
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=1599142&r1=1599141&r2=1599142&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 Mon Jun 2 09:45:45 2014
@@ -496,6 +496,15 @@ public class CaseExpressionTest extends
ps.setInt(1, 1);
JDBC.assertSingleValueResultSet(ps.executeQuery(), "1");
+ ps = prepareStatement(
+ "values case when true then ? else cast(? as integer) end");
+ ParameterMetaData params = ps.getParameterMetaData();
+ assertEquals(Types.INTEGER, params.getParameterType(1));
+ assertEquals(Types.INTEGER, params.getParameterType(2));
+ ps.setInt(1, 1);
+ ps.setInt(2, 2);
+ 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");
@@ -518,16 +527,29 @@ public class CaseExpressionTest extends
public void testUntypedNulls() throws SQLException {
Statement s = createStatement();
- // When all branches specify NULL, then Derby currently returns NULL
- // with type CHAR(1). It should have raised an error according to the
- // SQL standard. See DERBY-2002.
- String[] allNull = {
+ // Before DERBY-2002, Derby accepted a CASE expression to have an
+ // untyped NULL in all the result branches. Verify that an error
+ // is raised.
+ String[] allUntyped = {
+ // The SQL standard says at least one result should not be an
+ // untyped NULL, so expect these to fail.
"values case when true then null end",
"values case when true then null else null end",
- "values case when true then null when false then null else null end"
+ "values case when true then null "
+ + "when false then null else null end",
+
+ // We're not able to tell the type if we have a mix of untyped
+ // NULLs and untyped parameters.
+ "values case when true then ? end", // implicit ELSE NULL
+ "values case when true then null else ? end",
+ "values case when true then ? when false then ? else null end",
+
+ // These ones failed even before DERBY-2002.
+ "values case when true then ? else ? end",
+ "values case when true then ? when false then ? else ? end",
};
- for (String sql : allNull) {
- JDBC.assertSingleValueResultSet(s.executeQuery(sql), null);
+ for (String sql : allUntyped) {
+ assertCompileError("42X87", sql);
}
// Check that expressions with untyped NULLs compile as long as