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 2013/12/05 14:26:42 UTC
svn commit: r1548132 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/catalog/
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: kahatlen
Date: Thu Dec 5 13:26:42 2013
New Revision: 1548132
URL: http://svn.apache.org/r1548132
Log:
DERBY-6362: CHECK constraint uses wrong schema for unqualified routine invocations
Part 3:
- Refactor code that rewrites trigger actions so that it can be reused
for rewriting CHECK constraints.
- Rewrite CHECK constraints and make all identifiers schema qualified
before storing them in the dictionary.
- Make the parser preserve the TableName node for the target type in
CAST expressions for user-defined types, so that they get detected
by the rewrite logic.
Added:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OffsetOrderVisitor.java (with props)
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CastNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConstraintDefinitionNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CheckConstraintTest.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?rev=1548132&r1=1548131&r2=1548132&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Thu Dec 5 13:26:42 2013
@@ -32,7 +32,6 @@ import java.sql.Types;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
-import java.util.Comparator;
import java.util.Dictionary;
import java.util.Enumeration;
import java.util.GregorianCalendar;
@@ -44,6 +43,7 @@ import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Properties;
+import java.util.SortedSet;
import org.apache.derby.catalog.AliasInfo;
import org.apache.derby.catalog.DefaultInfo;
import org.apache.derby.catalog.DependableFinder;
@@ -152,8 +152,8 @@ import org.apache.derby.iapi.types.UserT
import org.apache.derby.iapi.util.IdUtil;
import org.apache.derby.impl.services.daemon.IndexStatisticsDaemonImpl;
import org.apache.derby.impl.services.locks.Timeout;
-import org.apache.derby.impl.sql.compile.CollectNodesVisitor;
import org.apache.derby.impl.sql.compile.ColumnReference;
+import org.apache.derby.impl.sql.compile.OffsetOrderVisitor;
import org.apache.derby.impl.sql.compile.TableName;
import org.apache.derby.impl.sql.depend.BasicDependencyManager;
import org.apache.derby.impl.sql.execute.JarUtil;
@@ -4746,19 +4746,6 @@ public final class DataDictionaryImpl
return list;
}
- /**
- * Comparator that can be used for sorting lists of column references
- * on the position they have in the SQL query string.
- */
- private static final Comparator<ColumnReference> OFFSET_COMPARATOR = new Comparator<ColumnReference>() {
- public int compare(ColumnReference o1, ColumnReference o2) {
- // Return negative int, zero, or positive int if the first column
- // reference has an offset which is smaller than, equal to, or
- // greater than the offset of the second column reference.
- return o1.getBeginOffset() - o2.getBeginOffset();
- }
- };
-
/**
* Get the trigger action string associated with the trigger after the
* references to old/new transition tables/variables in trigger action
@@ -4888,12 +4875,14 @@ public final class DataDictionaryImpl
}
}
- CollectNodesVisitor<ColumnReference> visitor = new CollectNodesVisitor<ColumnReference>(ColumnReference.class);
- actionStmt.accept(visitor);
- List<ColumnReference> refs = visitor.getList();
/* we need to sort on position in string, beetle 4324
*/
- Collections.sort(refs, OFFSET_COMPARATOR);
+ OffsetOrderVisitor<ColumnReference> visitor =
+ new OffsetOrderVisitor<ColumnReference>(ColumnReference.class,
+ actionOffset,
+ actionOffset + triggerDefinition.length());
+ actionStmt.accept(visitor);
+ SortedSet<ColumnReference> refs = visitor.getNodes();
if (createTriggerTime) {
//The purpose of following array(triggerActionColsOnly) is to
@@ -4951,33 +4940,8 @@ public final class DataDictionaryImpl
//in next version of 10.7 and 10.8. In 10.9, DERBY-1482 was
//reimplemented correctly and we started doing the collection and
//usage of trigger action columns again in 10.9
- for (int i = 0; i < refs.size(); i++)
+ for (ColumnReference ref : refs)
{
- ColumnReference ref = (ColumnReference) refs.get(i);
- /*
- ** Only occurrences of those OLD/NEW transition tables/variables
- ** are of interest here. There may be intermediate nodes in the
- ** parse tree that have its own RCL which contains copy of
- ** column references(CR) from other nodes. e.g.:
- **
- ** CREATE TRIGGER tt
- ** AFTER INSERT ON x
- ** REFERENCING NEW AS n
- ** FOR EACH ROW
- ** INSERT INTO y VALUES (n.i), (999), (333);
- **
- ** The above trigger action will result in InsertNode that
- ** contains a UnionNode of RowResultSetNodes. The UnionNode
- ** will have a copy of the CRs from its left child and those CRs
- ** will not have its beginOffset set which indicates they are
- ** not relevant for the conversion processing here, so we can
- ** safely skip them.
- */
- if (ref.getBeginOffset() == -1)
- {
- continue;
- }
-
TableName tableName = ref.getTableNameNode();
if ((tableName == null) ||
((oldReferencingName == null || !oldReferencingName.equals(tableName.getTableName())) &&
@@ -5067,33 +5031,8 @@ public final class DataDictionaryImpl
// turns into
// DELETE FROM t WHERE c in
// (SELECT c FROM new TriggerOldTransitionTable OLD)
- for (int i = 0; i < refs.size(); i++)
+ for (ColumnReference ref : refs)
{
- ColumnReference ref = (ColumnReference) refs.get(i);
- /*
- ** Only occurrences of those OLD/NEW transition tables/variables
- ** are of interest here. There may be intermediate nodes in the
- ** parse tree that have its own RCL which contains copy of
- ** column references(CR) from other nodes. e.g.:
- **
- ** CREATE TRIGGER tt
- ** AFTER INSERT ON x
- ** REFERENCING NEW AS n
- ** FOR EACH ROW
- ** INSERT INTO y VALUES (n.i), (999), (333);
- **
- ** The above trigger action will result in InsertNode that
- ** contains a UnionNode of RowResultSetNodes. The UnionNode
- ** will have a copy of the CRs from its left child and those CRs
- ** will not have its beginOffset set which indicates they are
- ** not relevant for the conversion processing here, so we can
- ** safely skip them.
- */
- if (ref.getBeginOffset() == -1)
- {
- continue;
- }
-
TableName tableName = ref.getTableNameNode();
if ((tableName == null) ||
((oldReferencingName == null || !oldReferencingName.equals(tableName.getTableName())) &&
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CastNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CastNode.java?rev=1548132&r1=1548131&r2=1548132&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CastNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CastNode.java Thu Dec 5 13:26:42 2013
@@ -87,6 +87,17 @@ class CastNode extends ValueNode
*/
private boolean assignmentSemantics = false;
+ /**
+ * The name of the target type if it's a UDT. It is partly redundant, as
+ * the name can also be retrieved from the type descriptor. Additionally,
+ * it contains information about the location of the UDT name in the
+ * query text, which is useful if the query text needs to be rewritten.
+ * (Useful for example when rewriting a CHECK constraint definition to
+ * have fully qualified names before storing it in the dictionary.) This
+ * field is only set for <b>explicit</b> casts to a UDT.
+ */
+ private TableName udtTargetName;
+
/**
* Constructor for a CastNode
*
@@ -403,6 +414,12 @@ class CastNode extends ValueNode
verifyClassExist(className);
}
+ // Set the schema name of the UDT target type.
+ if (udtTargetName != null) {
+ udtTargetName.setSchemaName(
+ getTypeId().getBaseTypeId().getSchemaName());
+ }
+
// Obviously the type of a parameter that
// requires its type from context (a parameter)
// gets its type from the type of the CAST.
@@ -1005,6 +1022,11 @@ class CastNode extends ValueNode
{
castOperand = (ValueNode)castOperand.accept(v);
}
+
+ if (udtTargetName != null)
+ {
+ udtTargetName = (TableName) udtTargetName.accept(v);
+ }
}
/** This method gets called by the parser to indiciate that this CAST node
@@ -1053,6 +1075,12 @@ class CastNode extends ValueNode
return false;
}
-}
-
+ /**
+ * Set the target type name if this is a cast to a UDT.
+ * @param name the name of the target type
+ */
+ void setTargetUDTName(TableName name) {
+ udtTargetName = name;
+ }
+}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConstraintDefinitionNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConstraintDefinitionNode.java?rev=1548132&r1=1548131&r2=1548132&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConstraintDefinitionNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConstraintDefinitionNode.java Thu Dec 5 13:26:42 2013
@@ -510,4 +510,33 @@ public class ConstraintDefinitionNode ex
constraintName = (TableName) constraintName.accept(v);
}
}
+
+ /**
+ * Qualify all SQL object names in a CHECK constraint with schema name.
+ * @throws StandardException if an error occurs
+ */
+ void qualifyNames() throws StandardException {
+ // Get all references to SQL object names in the CHECK constraint,
+ // ordered as they appear in the constraint definition.
+ OffsetOrderVisitor<TableName> visitor =
+ new OffsetOrderVisitor<TableName>(TableName.class,
+ checkCondition.getBeginOffset(),
+ checkCondition.getEndOffset() + 1);
+ checkCondition.accept(visitor);
+
+ StringBuilder sb = new StringBuilder();
+ int pos = 0;
+ int offset = checkCondition.getBeginOffset();
+
+ // Replace all names with fully qualified names.
+ for (TableName tableName : visitor.getNodes()) {
+ sb.append(constraintText, pos, tableName.getBeginOffset() - offset);
+ sb.append(tableName.getFullSQLName());
+ pos = tableName.getEndOffset() + 1 - offset;
+ }
+
+ sb.append(constraintText, pos, constraintText.length());
+
+ constraintText = sb.toString();
+ }
}
Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OffsetOrderVisitor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OffsetOrderVisitor.java?rev=1548132&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OffsetOrderVisitor.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OffsetOrderVisitor.java Thu Dec 5 13:26:42 2013
@@ -0,0 +1,109 @@
+/*
+
+ Derby - Class org.apache.derby.impl.sql.compile.OffsetOrderVisitor
+
+ 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 java.util.Comparator;
+import java.util.SortedSet;
+import java.util.TreeSet;
+import org.apache.derby.iapi.error.StandardException;
+import org.apache.derby.iapi.sql.compile.Visitable;
+import org.apache.derby.iapi.sql.compile.Visitor;
+import org.apache.derby.shared.common.sanity.SanityManager;
+
+/**
+ * Get all nodes of a certain type in a query tree, and return them in
+ * the order in which they appear in the original SQL text. This visitor
+ * is useful when rewriting SQL queries by replacing certain tokens in
+ * the original query.
+ *
+ * @param <T> the type of nodes to collect
+ */
+public class OffsetOrderVisitor<T extends QueryTreeNode> implements Visitor {
+
+ /** Comparator that orders nodes by ascending begin offset. */
+ private static final Comparator<QueryTreeNode>
+ COMPARATOR = new Comparator<QueryTreeNode>() {
+ public int compare(QueryTreeNode node1, QueryTreeNode node2) {
+ return node1.getBeginOffset() - node2.getBeginOffset();
+ }
+ };
+
+ private final Class<T> nodeClass;
+ private final TreeSet<T> nodes = new TreeSet<T>(COMPARATOR);
+ private final int lowOffset;
+ private final int highOffset;
+
+ /**
+ * Create a new {@code OffsetOrderVisitor} that collects nodes of the
+ * specified type. The nodes must have begin offset and end offset in
+ * the range given by the {@code low} and {@code high} parameters.
+ *
+ * @param nodeClass the type of nodes to collect
+ * @param low the lowest begin offset to accept (inclusive)
+ * @param high the highest end offset to accept (exclusive)
+ */
+ public OffsetOrderVisitor(Class<T> nodeClass, int low, int high) {
+ this.nodeClass = nodeClass;
+ this.lowOffset = low;
+ this.highOffset = high;
+
+ if (SanityManager.DEBUG) {
+ // We should only collect nodes with non-negative offset. Nodes
+ // with negative offset are synthetic and did not exist as tokens
+ // in the original query text.
+ SanityManager.ASSERT(lowOffset >= 0 && highOffset >= 0,
+ "offsets should be non-negative");
+ }
+ }
+
+ @Override
+ public Visitable visit(Visitable node) throws StandardException {
+ if (nodeClass.isInstance(node)) {
+ T qtn = nodeClass.cast(node);
+ if (qtn.getBeginOffset() >= lowOffset
+ && qtn.getEndOffset() < highOffset) {
+ nodes.add(qtn);
+ }
+ }
+
+ return 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;
+ }
+
+ public SortedSet<T> getNodes() {
+ return nodes;
+ }
+}
Propchange: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OffsetOrderVisitor.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java?rev=1548132&r1=1548131&r2=1548132&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java Thu Dec 5 13:26:42 2013
@@ -301,7 +301,13 @@ class StaticMethodCallNode extends Metho
throw StandardException.newException(
SQLState.LANG_NO_SUCH_METHOD_ALIAS, procedureName);
}
-
+
+ if (noSchema) {
+ // If no schema was specified, register where we found the
+ // routine.
+ procedureName.setSchemaName(sd.getSchemaName());
+ }
+
if ( !routineInfo.isDeterministic() )
{
checkReliability( getMethodName(), CompilerContext.NON_DETERMINISTIC_ILLEGAL );
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java?rev=1548132&r1=1548131&r2=1548132&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java Thu Dec 5 13:26:42 2013
@@ -655,6 +655,9 @@ class TableElementList extends QueryTree
* starts with a clean list.
*/
rcl.clearColumnReferences();
+
+ // Make sure all names are schema qualified (DERBY-6362)
+ cdn.qualifyNames();
}
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=1548132&r1=1548131&r2=1548132&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Thu Dec 5 13:26:42 2013
@@ -4049,7 +4049,7 @@ dataTypeDDL() throws StandardException :
}
|
LOOKAHEAD ( { getToken(1).kind != GENERATED } )
- typeDescriptor = javaType()
+ typeDescriptor = javaType(new TableName[1])
{
return typeDescriptor;
}
@@ -4076,7 +4076,7 @@ catalogType() throws StandardException :
* <A NAME="dataTypeCast">dataTypeCast</A>
*/
DataTypeDescriptor
-dataTypeCast() throws StandardException :
+dataTypeCast(TableName[] udtName) throws StandardException :
{
DataTypeDescriptor typeDescriptor;
}
@@ -4089,7 +4089,7 @@ dataTypeCast() throws StandardException
return typeDescriptor;
}
|
- typeDescriptor = javaType()
+ typeDescriptor = javaType(udtName)
{
return typeDescriptor;
}
@@ -4604,13 +4604,14 @@ xmlDocOrContent() throws StandardExcepti
* <A NAME="javaType">javaType</A>
*/
DataTypeDescriptor
-javaType() throws StandardException :
+javaType(TableName[] udtName) throws StandardException :
{
TableName typeName;
}
{
typeName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH)
{
+ udtName[0] = typeName;
return getJavaClassDataTypeDescriptor(typeName);
}
}
@@ -9741,20 +9742,26 @@ ValueNode
castSpecification() throws StandardException :
{
DataTypeDescriptor dts;
- ValueNode treeTop;
ValueNode value;
int charType;
int length = -1;
+ TableName[] udtName = new TableName[1];
}
{
- <CAST> <LEFT_PAREN> value = castOperand() <AS> dts = dataTypeCast() <RIGHT_PAREN>
+ <CAST> <LEFT_PAREN> value = castOperand() <AS> dts = dataTypeCast(udtName) <RIGHT_PAREN>
{
- treeTop = new CastNode(value, dts, getContextManager());
- ((CastNode) treeTop).setForExternallyGeneratedCASTnode();
+ CastNode castNode = new CastNode(value, dts, getContextManager());
+ castNode.setForExternallyGeneratedCASTnode();
+
+ // Register the name token if it's a UDT in case we need to
+ // rewrite the original statement text later (for example when
+ // storing a CHECK constraint).
+ castNode.setTargetUDTName(udtName[0]);
/* We need to generate a SQL->Java conversion tree above us if
* the dataTypeCast is a user type.
*/
+ ValueNode treeTop = castNode;
if (dts.getTypeId().userType())
{
treeTop = new JavaToSQLValueNode(
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CheckConstraintTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CheckConstraintTest.java?rev=1548132&r1=1548131&r2=1548132&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CheckConstraintTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CheckConstraintTest.java Thu Dec 5 13:26:42 2013
@@ -932,4 +932,88 @@ public final class CheckConstraintTest e
}
rs.close();
}
+
+ /**
+ * Test that CHECK constraint works if it contains unqualified names and
+ * the current schema when the constraint is defined is different from the
+ * schema in which the table lives. Regression test case for DERBY-6362.
+ */
+ public void testDerby6362() throws SQLException {
+ setAutoCommit(false);
+ Statement s = createStatement();
+ s.execute("create schema d6362_s1");
+ s.execute("create schema d6362_s2");
+
+ s.execute("set schema d6362_s1");
+ s.execute("create function f(x int) returns int deterministic "
+ + "language java parameter style java external name "
+ + "'java.lang.Math.abs' no sql");
+ s.execute("create type typ "
+ + "external name 'java.util.ArrayList' language java");
+
+ // Create the table with the constraints in a different schema than
+ // the current schema. Before DERBY-6362, unqualified names would be
+ // resolved to the current schema at definition time and to the
+ // table's schema during execution, which made them behave unreliably
+ // if the schemas differed.
+ s.execute("create table d6362_s2.t(x int, "
+ + "constraint c001 check(f(x) < 3))");
+ s.execute("alter table d6362_s2.t "
+ + "add constraint c002 check(f(x) >= 0)");
+ s.execute("alter table d6362_s2.t "
+ + "add constraint c003 check(cast(null as typ) is null)");
+
+ // Use a function that lives in the SYSFUN schema.
+ s.execute("alter table d6362_s2.t add constraint c004 "
+ + "check(f(x) > cos(pi()))");
+
+ // ABS is an operator, not a function, so it will not be qualified.
+ s.execute("alter table d6362_s2.t add constraint c005 "
+ + "check(abs(f(x)) < pi())");
+
+ // Add some constraints that reference the table. See that table
+ // names are qualified. Unqualified column names will not be qualified
+ // with schema and table.
+ s.execute("set schema d6362_s2");
+ s.execute("alter table t add constraint c101 check(x < 3)");
+ s.execute("alter table t add constraint c102 check(t.x < 4)");
+ s.execute("alter table t add constraint c103 "
+ + "check(x <= d6362_s1.f(t.x))");
+
+ // Add some fully qualified names to see that they still work.
+ s.execute("alter table t add constraint c201 check(d6362_s2.t.x < 5)");
+ s.execute("alter table t add constraint c202 check(d6362_s1.f(x) < 5)");
+ s.execute("alter table t add constraint c203 "
+ + "check(cast(null as d6362_s1.typ) is null)");
+
+ // Verify that the constraints were stored with fully qualified names.
+ String[][] expectedConstraints = {
+ {"C001", "(\"D6362_S1\".\"F\"(x) < 3)"},
+ {"C002", "(\"D6362_S1\".\"F\"(x) >= 0)"},
+ {"C003", "(cast(null as \"D6362_S1\".\"TYP\") is null)"},
+ {"C004", "(\"D6362_S1\".\"F\"(x) > \"SYSFUN\".\"COS\"(\"SYSFUN\".\"PI\"()))"},
+ {"C005", "(abs(\"D6362_S1\".\"F\"(x)) < \"SYSFUN\".\"PI\"())"},
+ {"C101", "(x < 3)"},
+ {"C102", "(\"D6362_S2\".\"T\".x < 4)"},
+ {"C103", "(x <= \"D6362_S1\".\"F\"(\"D6362_S2\".\"T\".x))"},
+ {"C201", "(\"D6362_S2\".\"T\".x < 5)"},
+ {"C202", "(\"D6362_S1\".\"F\"(x) < 5)"},
+ {"C203", "(cast(null as \"D6362_S1\".\"TYP\") is null)"},
+ };
+
+ JDBC.assertFullResultSet(
+ s.executeQuery(
+ "select constraintname, checkdefinition from sys.syschecks "
+ + "natural join sys.sysconstraints natural join sys.sysschemas "
+ + "where schemaname = 'D6362_S2' and type = 'C' "
+ + "order by constraintname"),
+ expectedConstraints);
+
+ // Verify that constraints can be executed. Used to fail because
+ // unqualified functions and types were resolved to the table's schema
+ // instead of the current schema at the time the constraint was defined.
+ s.execute("insert into t values 1,2");
+ assertStatementError("23513", s, "insert into t values -10");
+ assertStatementError("23513", s, "insert into t values 10");
+ }
}