Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: 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/
Date: Thu, 05 Dec 2013 13:26:42 -0000
To: derby-commits@db.apache.org
From: kahatlen@apache.org
Message-Id: <20131205132642.F2AD42388A38@eris.apache.org>

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");
+    }
 }