db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kahat...@apache.org
Subject 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/
Date Wed, 21 May 2014 06:51:40 GMT
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");
     }
 }



Mime
View raw message