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/05/21 08:51:40 UTC
svn commit: r1596492 - 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: Wed May 21 06:51:40 2014
New Revision: 1596492
URL: http://svn.apache.org/r1596492
Log:
DERBY-1576: Extend the CASE expression syntax for "simple case"
Forbid untyped parameters in the case operand.
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=1596492&r1=1596491&r2=1596492&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 Wed May 21 06:51:40 2014
@@ -307,6 +307,21 @@ class ConditionalNode extends ValueNode
caseOperand = caseOperand.bindExpression(
fromList, subqueryList, aggregates);
+ // For now, let's also forbid untyped parameters as case
+ // operands. The problem is that the current type inference
+ // doesn't handle conflicting types. Take for example
+ // CASE ? WHEN 1 THEN TRUE WHEN 'abc' THEN FALSE END
+ // The type of the parameter would first get set to INTEGER
+ // when binding the first WHEN clause. Later, when binding the
+ // second WHEN clause, the type would be changed to CHAR(3),
+ // without noticing that it already had a type, and that the
+ // previous type was incompatible with the new one. Until the
+ // type inference has improved, forbid such expressions.
+ if (caseOperand.requiresTypeFromContext()) {
+ throw StandardException.newException(
+ SQLState.LANG_CASE_OPERAND_UNTYPED);
+ }
+
cc.setReliability(previousReliability);
}
}
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=1596492&r1=1596491&r2=1596492&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 Wed May 21 06:51:40 2014
@@ -2866,6 +2866,11 @@ Guide.
</msg>
<msg>
+ <name>42Z09</name>
+ <text>The case operand cannot be an untyped parameter.</text>
+ </msg>
+
+ <msg>
<name>42Z15</name>
<text>Invalid type specified for column '{0}'. The type of a column may not be changed. </text>
<arg>columnName</arg>
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=1596492&r1=1596491&r2=1596492&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 Wed May 21 06:51:40 2014
@@ -1049,6 +1049,7 @@ public interface SQLState {
String LANG_USER_AGGREGATE_MULTIPLE_DISTINCTS = "42Z02";
String LANG_NO_AGGREGATES_IN_ON_CLAUSE = "42Z07";
String LANG_NO_BULK_INSERT_REPLACE_WITH_TRIGGER = "42Z08";
+ String LANG_CASE_OPERAND_UNTYPED = "42Z09";
// MORE GENERIC LANGUAGE STUFF
String LANG_UDT_BUILTIN_CONFLICT = "42Z10";
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=1596492&r1=1596491&r2=1596492&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 Wed May 21 06:51:40 2014
@@ -725,5 +725,74 @@ public class CaseExpressionTest extends
"values case cast(1 as bigint)"
+ " when cast(1 as smallint) then 'yes' end"),
"yes");
+
+ // A sequence cannot be accessed anywhere in a CASE expression.
+ s.execute("create sequence d1576_s start with 1");
+ assertCompileError(
+ "42XAH",
+ "values case next value for d1576_s when 1 then 1 else 0 end");
+ assertCompileError(
+ "42XAH",
+ "values case 1 when next value for d1576_s then 1 else 0 end");
+ assertCompileError(
+ "42XAH",
+ "values case 1 when 1 then next value for d1576_s else 0 end");
+
+ // Instead, access the sequence value in a nested query.
+ JDBC.assertSingleValueResultSet(
+ s.executeQuery(
+ "select case x when 1 then 1 else 0 end from "
+ + "(values next value for d1576_s) v(x)"),
+ "1");
+
+ s.execute("drop sequence d1576_s restrict");
+
+ // Window functions are allowed.
+ JDBC.assertFullResultSet(
+ s.executeQuery(
+ "select case row_number() over () when 1 then 'one' "
+ + "when 2 then 'two' end from (values 1, 1, 1) v(x)"),
+ new String[][] { {"one"}, {"two"}, {null} });
+
+ // Parameters in the case operand have to be typed for now, since the
+ // current type inference doesn't handle multiple types very well.
+ PreparedStatement ps = prepareStatement(
+ "values case cast(? as integer) "
+ + "when 1 then 'one' when 2 then 'two' end");
+ ps.setInt(1, 1);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "one");
+ ps.setInt(1, 2);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "two");
+ ps.setInt(1, 3);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), null);
+
+ // This one fails to compile because an integer cannot be checked
+ // with LIKE.
+ assertCompileError("42884",
+ "values case cast(? as integer) "
+ + "when 1 then 1 when like 'abc' then 2 end");
+
+ // Should have been able to infer the type in this case, but
+ // expect failure for now.
+ assertCompileError("42Z09",
+ "values case ? when 1 then 2 when 3 then 4 end");
+
+ // Should have detected that the types in the WHEN clauses are
+ // incompatible (the first requires the operand to be a number, the
+ // second requires it to be a string). Instead, for now, it fails
+ // because untyped parameters have been forbidden in the case operand.
+ // Used to fail with "Invalid character string format for type int"
+ // during execution before it was forbidden.
+ assertCompileError("42Z09",
+ "values case ? when 1 then true when like 'abc' then false end");
+
+ // Should have detected that the types in the WHEN clauses are
+ // incompatible (the first requires the operand to be a string, the
+ // second requires it to be a number). Instead, for now, it fails
+ // because untyped parameters have been forbidden in the case operand.
+ // Used to fail with an assert failure at compile time before it was
+ // forbidden.
+ assertCompileError("42Z09",
+ "values case ? when like 'abc' then true when 1 then false end");
}
}