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/30 09:13:23 UTC
svn commit: r1598472 - 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: Fri May 30 07:13:22 2014
New Revision: 1598472
URL: http://svn.apache.org/r1598472
Log:
DERBY-1576: Extend the CASE expression syntax for "simple case"
Allow untyped parameters in the case operand.
Added:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ReplaceNodeVisitor.java (with props)
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ValueNodeList.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=1598472&r1=1598471&r2=1598472&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 Fri May 30 07:13:22 2014
@@ -32,6 +32,7 @@ import org.apache.derby.iapi.services.co
import org.apache.derby.iapi.services.loader.ClassInspector;
import org.apache.derby.shared.common.sanity.SanityManager;
import org.apache.derby.iapi.sql.compile.CompilerContext;
+import org.apache.derby.iapi.sql.compile.Visitable;
import org.apache.derby.iapi.sql.compile.Visitor;
import org.apache.derby.iapi.types.DataTypeDescriptor;
import org.apache.derby.iapi.types.TypeId;
@@ -195,12 +196,41 @@ class ConditionalNode extends ValueNode
int previousReliability = orReliability( CompilerContext.CONDITIONAL_RESTRICTION );
- bindCaseOperand(cc, fromList, subqueryList, aggregates);
+ ValueNodeList caseOperandParameters =
+ bindCaseOperand(cc, fromList, subqueryList, aggregates);
testConditions.bindExpression(fromList,
subqueryList,
aggregates);
+ // If we have a simple case expression in which the case operand
+ // requires type from context (typically because it's an untyped
+ // parameter), find out which type best describes it.
+ if (caseOperandParameters != null) {
+
+ // Go through all the dummy parameter nodes that bindCaseOperand()
+ // inserted into the synthetic test conditions. Each of them will
+ // have been given the type of the corresponding when operand
+ // when testConditions was bound.
+ for (ValueNode vn : caseOperandParameters) {
+ // Check that this parameter is comparable to all the other
+ // parameters in the list. This indirectly checks whether
+ // all when operands have compatible types.
+ caseOperandParameters.comparable(vn);
+
+ // Replace the dummy parameter node with the actual case
+ // operand.
+ testConditions.accept(new ReplaceNodeVisitor(vn, caseOperand));
+ }
+
+ // Finally, after we have determined that all the when operands
+ // are compatible, and we have reinserted the case operand into
+ // the tree, set the type of the case operand to the dominant
+ // type of all the when operands.
+ caseOperand.setType(
+ 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.
@@ -291,38 +321,99 @@ class ConditionalNode extends ValueNode
}
/**
+ * <p>
* Bind the case operand, if there is one, and check that it doesn't
* contain anything that's illegal in a case operand (such as calls to
- * routines that are non-deterministic or modifies SQL).
+ * routines that are non-deterministic or modify SQL).
+ * </p>
+ *
+ * <p>
+ * Also, if the type of the case operand needs to be inferred, insert
+ * dummy parameter nodes into {@link #testConditions} instead of the
+ * case operand, so that the type can be inferred individually for each
+ * test condition. Later, {@link #bindExpression} will find a common
+ * type for all of them, use that type for the case operand, and reinsert
+ * the case operand into the test conditions.
+ * </p>
+ *
+ * @return a list of dummy parameter nodes that have been inserted into
+ * the tree instead of the case operand, if such a replacement has
+ * happened; otherwise, {@code null} is returned
*/
- private void bindCaseOperand(CompilerContext cc, FromList fromList,
- SubqueryList subqueryList,
- List<AggregateNode> aggregates)
+ private ValueNodeList bindCaseOperand(
+ CompilerContext cc, FromList fromList,
+ SubqueryList subqueryList, List<AggregateNode> aggregates)
throws StandardException {
+ ValueNodeList replacements = null;
+
if (caseOperand != null) {
int previousReliability = orReliability(
CompilerContext.CASE_OPERAND_RESTRICTION);
+ // If the case operand requires type from context (typically,
+ // because it is an untyped parameter), we need to find a type
+ // that is comparable with all the when operands.
+ //
+ // To find the types of the when operands, temporarily replace
+ // all occurrences of the case operand with dummy parameter nodes.
+ // Later, after binding testConditions, those dummy nodes will
+ // have their types set to the types of the when operands. At that
+ // time, we will be able to find a common type, set the type of the
+ // case operand to that type, and reinsert the case operand into
+ // the tree.
+ if (caseOperand.requiresTypeFromContext()) {
+ replacements = new ValueNodeList(getContextManager());
+ testConditions.accept(
+ new ReplaceCaseOperandVisitor(replacements));
+ }
+
caseOperand = (CachedValueNode) 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);
+ }
+
+ return replacements;
+ }
+
+ /**
+ * A visitor that replaces all occurrences of the {@link caseOperand} node
+ * in a tree with dummy parameter nodes. It also fills a supplied list
+ * with the parameter nodes that have been inserted into the tree.
+ */
+ private class ReplaceCaseOperandVisitor implements Visitor {
+ private final ValueNodeList replacements;
+
+ private ReplaceCaseOperandVisitor(ValueNodeList replacements) {
+ this.replacements = replacements;
+ }
+
+ @Override
+ public Visitable visit(Visitable node) throws StandardException {
+ if (node == caseOperand) {
+ ParameterNode pn = new ParameterNode(
+ 0, null, getContextManager());
+ replacements.addElement(pn);
+ return pn;
+ } else {
+ return node;
}
+ }
- cc.setReliability(previousReliability);
+ @Override
+ public boolean visitChildrenFirst(Visitable node) {
+ return false;
+ }
+
+ @Override
+ public boolean stopTraversal() {
+ return false;
+ }
+
+ @Override
+ public boolean skipChildren(Visitable node) throws StandardException {
+ return false;
}
}
Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ReplaceNodeVisitor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ReplaceNodeVisitor.java?rev=1598472&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ReplaceNodeVisitor.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ReplaceNodeVisitor.java Fri May 30 07:13:22 2014
@@ -0,0 +1,60 @@
+/*
+
+ Derby - Class org.apache.derby.impl.sql.compile.ReplaceNodeVisitor
+
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to you under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+
+ */
+
+package org.apache.derby.impl.sql.compile;
+
+import org.apache.derby.iapi.error.StandardException;
+import org.apache.derby.iapi.sql.compile.Visitable;
+import org.apache.derby.iapi.sql.compile.Visitor;
+
+/**
+ * Replace all occurrences of a specific node with another node.
+ */
+class ReplaceNodeVisitor implements Visitor {
+
+ private final Visitable nodeToReplace;
+ private final Visitable replacement;
+
+ ReplaceNodeVisitor(Visitable nodeToReplace, Visitable replacement) {
+ this.nodeToReplace = nodeToReplace;
+ this.replacement = replacement;
+ }
+
+ @Override
+ public Visitable visit(Visitable node) throws StandardException {
+ return (node == nodeToReplace) ? replacement : node;
+ }
+
+ @Override
+ public boolean visitChildrenFirst(Visitable node) {
+ return false;
+ }
+
+ @Override
+ public boolean stopTraversal() {
+ return false;
+ }
+
+ @Override
+ public boolean skipChildren(Visitable node) throws StandardException {
+ return false;
+ }
+}
Propchange: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ReplaceNodeVisitor.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ValueNodeList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ValueNodeList.java?rev=1598472&r1=1598471&r2=1598472&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ValueNodeList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ValueNodeList.java Fri May 30 07:13:22 2014
@@ -166,8 +166,14 @@ class ValueNodeList extends QueryTreeNod
ValueNode valueNode;
valueNode = elementAt(index);
- if (valueNode.requiresTypeFromContext())
+
+ // Skip nodes that take their type from the context, if they
+ // haven't already been bound to a type.
+ if (valueNode.requiresTypeFromContext()
+ && valueNode.getTypeServices() == null) {
continue;
+ }
+
DataTypeDescriptor valueNodeDTS = valueNode.getTypeServices();
if (valueNodeDTS.getTypeId().isStringTypeId())
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=1598472&r1=1598471&r2=1598472&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 Fri May 30 07:13:22 2014
@@ -2866,11 +2866,6 @@ 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=1598472&r1=1598471&r2=1598472&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 Fri May 30 07:13:22 2014
@@ -1049,7 +1049,6 @@ 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=1598472&r1=1598471&r2=1598472&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 Fri May 30 07:13:22 2014
@@ -21,6 +21,7 @@
package org.apache.derbyTesting.functionTests.tests.lang;
+import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
@@ -757,8 +758,7 @@ public class CaseExpressionTest extends
+ "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.
+ // Test that you can have a typed parameter in the case operand.
PreparedStatement ps = prepareStatement(
"values case cast(? as integer) "
+ "when 1 then 'one' when 2 then 'two' end");
@@ -775,29 +775,168 @@ public class CaseExpressionTest extends
"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",
+ // Untyped parameter in the case operand. Should be able to infer
+ // the type from the WHEN clauses.
+ ps = prepareStatement("values case ? when 1 then 2 when 3 then 4 end");
+ ParameterMetaData pmd = ps.getParameterMetaData();
+ assertEquals(Types.INTEGER, pmd.getParameterType(1));
+ assertEquals(ParameterMetaData.parameterNullable, pmd.isNullable(1));
+
+ ps.setInt(1, 1);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "2");
+
+ ps.setInt(1, 2);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), null);
+
+ ps.setInt(1, 3);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "4");
+
+ ps = prepareStatement(
+ "values case ? when cast(1.1 as double) then true "
+ + "when cast(1.2 as double) then false end");
+ pmd = ps.getParameterMetaData();
+ assertEquals(Types.DOUBLE, pmd.getParameterType(1));
+ assertEquals(ParameterMetaData.parameterNullable, pmd.isNullable(1));
+
+ ps.setDouble(1, 1.1);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "true");
+ ps.setDouble(1, 1.2);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "false");
+ ps.setDouble(1, 1.3);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), null);
+
+ // Mixed types are accepted, as long as they are compatible.
+ ps = prepareStatement(
+ "values case ? when 1 then 'one' when 2.1 then 'two' end");
+ pmd = ps.getParameterMetaData();
+ assertEquals(Types.DECIMAL, pmd.getParameterType(1));
+ assertEquals(ParameterMetaData.parameterNullable, pmd.isNullable(1));
+
+ ps.setInt(1, 1);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "one");
+ ps.setInt(1, 2);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), null);
+
+ ps.setDouble(1, 1.1);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), null);
+ ps.setDouble(1, 2.1);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "two");
+
+ ps = prepareStatement(
+ "values case ? when 1 then 'one' when 2.1 then 'two'"
+ + " when cast(3 as bigint) then 'three' end");
+ assertEquals(Types.DECIMAL, pmd.getParameterType(1));
+ assertEquals(ParameterMetaData.parameterNullable, pmd.isNullable(1));
+
+ ps.setInt(1, 1);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "one");
+ ps.setInt(1, 2);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), null);
+ ps.setInt(1, 3);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "three");
+
+ ps.setDouble(1, 1.1);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), null);
+ ps.setDouble(1, 2.1);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "two");
+ ps.setDouble(1, 3.1);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), null);
+
+ ps = prepareStatement(
+ "values case ? when 'abcdef' then 1 "
+ + "when cast('abcd' as varchar(4)) then 2 end");
+ pmd = ps.getParameterMetaData();
+ assertEquals(Types.VARCHAR, pmd.getParameterType(1));
+ assertEquals(6, pmd.getPrecision(1));
+ assertEquals(ParameterMetaData.parameterNullable, pmd.isNullable(1));
+
+ ps.setString(1, "abcdef");
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "1");
+ ps.setString(1, "abcd");
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "2");
+ ps.setString(1, "ab");
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), null);
+ ps.setString(1, "abcdefghi");
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), null);
+
+ // The types in the WHEN clauses are incompatible, so the type of
+ // the case operand cannot be inferred.
+ assertCompileError("42818",
"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",
+ assertCompileError("42818",
"values case ? when like 'abc' then true when 1 then false end");
+ // BLOB and CLOB are not comparable with anything.
+ assertCompileError("42818",
+ "values case ? when cast(x'abcd' as blob) then true end");
+ assertCompileError("42818",
+ "values case ? when cast('abcd' as clob) then true end");
+
+ // Cannot infer type if both sides of the comparison are untyped.
+ assertCompileError("42X35", "values case ? when ? then true end");
+ assertCompileError("42X35", "values case ? when ? then true "
+ + "when 1 then false end");
+
+ // Should be able to infer type when the untyped parameter is prefixed
+ // with plus or minus.
+ ps = prepareStatement(
+ "values (case +? when 1 then 1 when 2.1 then 2 end, "
+ + "case -? when 1 then 1 when 2.1 then 2 end)");
+ pmd = ps.getParameterMetaData();
+ assertEquals(Types.DECIMAL, pmd.getParameterType(1));
+ assertEquals(ParameterMetaData.parameterNullable, pmd.isNullable(1));
+ assertEquals(Types.DECIMAL, pmd.getParameterType(2));
+ assertEquals(ParameterMetaData.parameterNullable, pmd.isNullable(2));
+
+ ps.setInt(1, 1);
+ ps.setInt(2, -1);
+ JDBC.assertFullResultSet(ps.executeQuery(),
+ new String[][] {{ "1", "1" }});
+
+ ps.setInt(1, 2);
+ ps.setInt(2, -2);
+ JDBC.assertFullResultSet(ps.executeQuery(),
+ new String[][] {{ null, null }});
+
+ ps.setDouble(1, 1.1);
+ ps.setDouble(2, -1.1);
+ JDBC.assertFullResultSet(ps.executeQuery(),
+ new String[][] {{ null, null }});
+
+ ps.setDouble(1, 2.1);
+ ps.setDouble(2, -2.1);
+ JDBC.assertFullResultSet(ps.executeQuery(),
+ new String[][] {{ "2", "2" }});
+
+ // If the untyped parameter is part of an arithmetic expression, its
+ // type is inferred from that expression and not from the WHEN clause.
+ ps = prepareStatement(
+ "values case 2*? when 2 then true when 3.0 then false end");
+ pmd = ps.getParameterMetaData();
+ assertEquals(Types.INTEGER, pmd.getParameterType(1));
+ assertEquals(ParameterMetaData.parameterNullable, pmd.isNullable(1));
+
+ ps.setInt(1, 1);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "true");
+ ps.setDouble(1, 1.5);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "true");
+ ps.setInt(1, 2);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), null);
+
+ ps = prepareStatement(
+ "values case 2.0*? when 2 then true when 3.0 then false end");
+ pmd = ps.getParameterMetaData();
+ assertEquals(Types.DECIMAL, pmd.getParameterType(1));
+ assertEquals(ParameterMetaData.parameterNullable, pmd.isNullable(1));
+
+ ps.setInt(1, 1);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "true");
+ ps.setDouble(1, 1.5);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "false");
+ ps.setInt(1, 2);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), null);
+
// The EXISTS predicate can only be used in the WHEN operand if
// the CASE operand is a BOOLEAN.
JDBC.assertSingleValueResultSet(