db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kahat...@apache.org
Subject svn commit: r832379 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ResultSetNode.java engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java testing/org/apache/derbyTesting/functionTests/tests/lang/InsertTest.java
Date Tue, 03 Nov 2009 10:31:40 GMT
Author: kahatlen
Date: Tue Nov  3 10:31:21 2009
New Revision: 832379

URL: http://svn.apache.org/viewvc?rev=832379&view=rev
Log:
DERBY-4420: NullPointerException with INSERT INTO ... from EXCEPT/INTERSECT

The failing code in ResultSetNode.setTableConstructorTypes() was meant
to handle the case where the node represented a table constructor (aka
VALUES clause). UnionNode already had an override to make it a no-op
unless it actually represented a multi-row VALUES clause that had been
rewritten to a union of single-row VALUES clauses.

Since a VALUES clause is never rewritten to EXCEPT or INTERSECT, the
correct handling is to make setTableConstructorTypes() a no-op in
IntersectOrExceptNode. Rather than adding an empty override in
IntersectOrExceptNode, the code was moved from
ResultSetNode.setTableConstructorTypes() to
RowResultSetNode.setTableConstructorTypes(), and the default
implementation in ResultSetNode was left empty.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InsertTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java?rev=832379&r1=832378&r2=832379&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java Tue
Nov  3 10:31:21 2009
@@ -304,7 +304,12 @@
 	}
 
 	/**
-	 * Set the type of each parameter in the result column list for this table constructor.
+	 * Set the type of each parameter in the result column list if this node
+	 * represents a table constructor (aka VALUES clause). Table constructors
+	 * are represented either by a {@code RowResultSetNode} or by a
+	 * {@code UnionNode} with multiple {@code RowResultSetNode} children and
+	 * whose {@code tableConstructor()} method returns {@code true}. For all
+	 * other nodes, this method should be a no-op.
 	 *
 	 * @param typeColumns	The ResultColumnList containing the desired result
 	 *						types.
@@ -314,143 +319,9 @@
 	void setTableConstructorTypes(ResultColumnList typeColumns)
 			throws StandardException
 	{
-		if (SanityManager.DEBUG)
-			SanityManager.ASSERT(resultColumns.visibleSize() <= typeColumns.size(),
-				"More columns in ResultColumnList than in base table");
-
-		/* Look for ? parameters in the result column list */
-		int rclSize = resultColumns.size();
-		for (int index = 0; index < rclSize; index++)
-		{
-			ResultColumn	rc = (ResultColumn) resultColumns.elementAt(index);
-
-			ValueNode re = rc.getExpression();
-
-			if (re.requiresTypeFromContext())
-			{
-				ResultColumn	typeCol =
-					(ResultColumn) typeColumns.elementAt(index);
-
-				/*
-				** We found a ? - set its type to the type of the
-				** corresponding column of the target table.
-				*/
-				re.setType(typeCol.getTypeServices());
-			}
-			else if (re instanceof CharConstantNode)
-			{
-				// Character constants are of type CHAR (fixed length string).
-				// This causes a problem (beetle 5160) when multiple row values are provided
-				// as constants for insertion into a variable length string column.
-				//
-				// This issue is the query expression
-				// VALUES 'abc', 'defghi'
-				// has type of CHAR(6), ie. the length of largest row value for that column.
-				// This is from the UNION defined behaviour.
-				// This causes strings with less than the maximum length to be blank padded
-				// to that length (CHAR semantics). Thus if this VALUES clause is used to
-				// insert into a variable length string column, then these blank padded values
-				// are inserted, which is not what is required ...
-				// 
-				// BECAUSE, when the VALUES is used as a table constructor SQL standard says the
-				// types of the table constructor's columns are set by the table's column types.
-				// Thus, in this case, each of those string constants should be of type VARCHAR
-				// (or the matching string type for the table).
-				//
-				//
-				// This is only an issue for fixed length character (CHAR, BIT) string or
-				// binary consraints being inserted into variable length types.
-				// This is because any other type's fundemental literal value is not affected
-				// by its data type. E.g. Numeric types such as INT, REAL, BIGINT, DECIMAL etc.
-				// do not have their value modifed by the union since even if the type is promoted
-				// to a higher type, its fundemental value remains unchanged. 
-				// values (1.2, 34.4567, 234.47) will be promoted to
-				// values (1.2000, 34.4567, 234.4700)
-				// but their numeric value remains the same.
-				//
-				//
-				//
-				// The fix is to change the base type of the table constructor's value to
-				// match the column type. Its length can be left as-is, because there is
-				// still a normailzation step when the value is inserted into the table.
-				// That will set the correct length and perform truncation checks etc.
-
-				ResultColumn	typeCol =
-					(ResultColumn) typeColumns.elementAt(index);
-
-				TypeId colTypeId = typeCol.getTypeId();
-
-				if (colTypeId.isStringTypeId()) {
-
-					if (colTypeId.getJDBCTypeId() != java.sql.Types.CHAR) {
-
-						int maxWidth = re.getTypeServices().getMaximumWidth();
-
-						re.setType(new DataTypeDescriptor(colTypeId, true, maxWidth));
-					}
-				}
-				else if (colTypeId.isBitTypeId()) {
-					if (colTypeId.getJDBCTypeId() == java.sql.Types.VARBINARY) {
-					// then we're trying to cast a char literal into a
-					// variable bit column.  We can't change the base
-					// type of the table constructor's value from char
-					// to bit, so instead, we just change the base type
-					// of that value from char to varchar--that way,
-					// no padding will be added when we convert to
-					// bits later on (Beetle 5306).
-						TypeId tId = TypeId.getBuiltInTypeId(java.sql.Types.VARCHAR);
-						re.setType(new DataTypeDescriptor(tId, true));
-						typeColumns.setElementAt(typeCol, index);
-					}
-					else if (colTypeId.getJDBCTypeId() == java.sql.Types.LONGVARBINARY) {
-						TypeId tId = TypeId.getBuiltInTypeId(java.sql.Types.LONGVARCHAR);
-						re.setType(new DataTypeDescriptor(tId, true));
-						typeColumns.setElementAt(typeCol, index);
-					}
-				}
-
-			}
-			else if (re instanceof BitConstantNode)
-			{
-				ResultColumn	typeCol =
-					(ResultColumn) typeColumns.elementAt(index);
-
-				TypeId colTypeId = typeCol.getTypeId();
-
-				if (colTypeId.isBitTypeId()) {
-
-					// NOTE: Don't bother doing this if the column type is BLOB,
-					// as we don't allow bit literals to be inserted into BLOB
-					// columns (they have to be explicitly casted first); beetle 5266.
-					if ((colTypeId.getJDBCTypeId() != java.sql.Types.BINARY) &&
-						(colTypeId.getJDBCTypeId() != java.sql.Types.BLOB)) {
-
-						int maxWidth = re.getTypeServices().getMaximumWidth();
-
-						re.setType(new DataTypeDescriptor(colTypeId, true, maxWidth));
-					}
-				}
-				else if (colTypeId.isStringTypeId()) {
-					if (colTypeId.getJDBCTypeId() == java.sql.Types.VARCHAR) {
-					// then we're trying to cast a bit literal into a
-					// variable char column.  We can't change the base
-					// type of the table constructor's value from bit
-					// to char, so instead, we just change the base
-					// type of that value from bit to varbit--that way,
-					// no padding will be added when we convert to
-					// char later on.
-						TypeId tId = TypeId.getBuiltInTypeId(java.sql.Types.VARBINARY);
-						re.setType(new DataTypeDescriptor(tId, true));
-						typeColumns.setElementAt(typeCol, index);
-					}
-					else if (colTypeId.getJDBCTypeId() == java.sql.Types.LONGVARCHAR) {
-						TypeId tId = TypeId.getBuiltInTypeId(java.sql.Types.LONGVARBINARY);
-						re.setType(new DataTypeDescriptor(tId, true));
-						typeColumns.setElementAt(typeCol, index);
-					}
-				}
-			}
-		}
+		// Nothing to be done unless this node represents a VALUES clause, in
+		// which case the overrides in RowResultSetNode or UnionNode will do
+		// the necessary work.
 	}
 
 	/**

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java?rev=832379&r1=832378&r2=832379&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java
Tue Nov  3 10:31:21 2009
@@ -21,14 +21,9 @@
 
 package	org.apache.derby.impl.sql.compile;
 
-import org.apache.derby.iapi.services.context.ContextManager;
-
-import org.apache.derby.iapi.sql.compile.CompilerContext;
 import org.apache.derby.iapi.sql.compile.CostEstimate;
 import org.apache.derby.iapi.sql.compile.Optimizer;
-import org.apache.derby.iapi.sql.compile.OptimizableList;
 import org.apache.derby.iapi.sql.compile.Optimizable;
-import org.apache.derby.iapi.sql.compile.OptimizablePredicate;
 import org.apache.derby.iapi.sql.compile.OptimizablePredicateList;
 import org.apache.derby.iapi.sql.compile.RequiredRowOrdering;
 import org.apache.derby.iapi.sql.compile.RowOrdering;
@@ -38,25 +33,22 @@
 import org.apache.derby.iapi.sql.dictionary.ConglomerateDescriptor;
 import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
 
-import org.apache.derby.iapi.sql.Activation;
-import org.apache.derby.iapi.sql.ResultSet;
-import org.apache.derby.iapi.sql.Row;
 import org.apache.derby.iapi.error.StandardException;
 
-import org.apache.derby.impl.sql.compile.ActivationClassBuilder;
 import org.apache.derby.iapi.services.compiler.MethodBuilder;
 
 import org.apache.derby.iapi.store.access.Qualifier;
 
 import org.apache.derby.iapi.services.sanity.SanityManager;
 
+import org.apache.derby.iapi.types.DataTypeDescriptor;
+import org.apache.derby.iapi.types.TypeId;
+
 import org.apache.derby.iapi.util.JBitSet;
 import org.apache.derby.iapi.reference.SQLState;
 import org.apache.derby.iapi.reference.ClassName;
 import org.apache.derby.iapi.services.classfile.VMOpcode;
 
-import java.util.Enumeration;
-import java.util.Properties;
 import java.util.Vector;
 
 /**
@@ -640,6 +632,156 @@
 		return true;
 	}
 
+	/**
+	 * Set the type of each parameter in the result column list for this table constructor.
+	 *
+	 * @param typeColumns	The ResultColumnList containing the desired result
+	 *						types.
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	void setTableConstructorTypes(ResultColumnList typeColumns)
+			throws StandardException
+	{
+		if (SanityManager.DEBUG)
+			SanityManager.ASSERT(resultColumns.visibleSize() <= typeColumns.size(),
+				"More columns in ResultColumnList than in base table");
+
+		/* Look for ? parameters in the result column list */
+		int rclSize = resultColumns.size();
+		for (int index = 0; index < rclSize; index++)
+		{
+			ResultColumn	rc = (ResultColumn) resultColumns.elementAt(index);
+
+			ValueNode re = rc.getExpression();
+
+			if (re.requiresTypeFromContext())
+			{
+				ResultColumn	typeCol =
+					(ResultColumn) typeColumns.elementAt(index);
+
+				/*
+				** We found a ? - set its type to the type of the
+				** corresponding column of the target table.
+				*/
+				re.setType(typeCol.getTypeServices());
+			}
+			else if (re instanceof CharConstantNode)
+			{
+				// Character constants are of type CHAR (fixed length string).
+				// This causes a problem (beetle 5160) when multiple row values are provided
+				// as constants for insertion into a variable length string column.
+				//
+				// This issue is the query expression
+				// VALUES 'abc', 'defghi'
+				// has type of CHAR(6), ie. the length of largest row value for that column.
+				// This is from the UNION defined behaviour.
+				// This causes strings with less than the maximum length to be blank padded
+				// to that length (CHAR semantics). Thus if this VALUES clause is used to
+				// insert into a variable length string column, then these blank padded values
+				// are inserted, which is not what is required ...
+				//
+				// BECAUSE, when the VALUES is used as a table constructor SQL standard says the
+				// types of the table constructor's columns are set by the table's column types.
+				// Thus, in this case, each of those string constants should be of type VARCHAR
+				// (or the matching string type for the table).
+				//
+				//
+				// This is only an issue for fixed length character (CHAR, BIT) string or
+				// binary consraints being inserted into variable length types.
+				// This is because any other type's fundemental literal value is not affected
+				// by its data type. E.g. Numeric types such as INT, REAL, BIGINT, DECIMAL etc.
+				// do not have their value modifed by the union since even if the type is promoted
+				// to a higher type, its fundemental value remains unchanged.
+				// values (1.2, 34.4567, 234.47) will be promoted to
+				// values (1.2000, 34.4567, 234.4700)
+				// but their numeric value remains the same.
+				//
+				//
+				//
+				// The fix is to change the base type of the table constructor's value to
+				// match the column type. Its length can be left as-is, because there is
+				// still a normailzation step when the value is inserted into the table.
+				// That will set the correct length and perform truncation checks etc.
+
+				ResultColumn	typeCol =
+					(ResultColumn) typeColumns.elementAt(index);
+
+				TypeId colTypeId = typeCol.getTypeId();
+
+				if (colTypeId.isStringTypeId()) {
+
+					if (colTypeId.getJDBCTypeId() != java.sql.Types.CHAR) {
+
+						int maxWidth = re.getTypeServices().getMaximumWidth();
+
+						re.setType(new DataTypeDescriptor(colTypeId, true, maxWidth));
+					}
+				}
+				else if (colTypeId.isBitTypeId()) {
+					if (colTypeId.getJDBCTypeId() == java.sql.Types.VARBINARY) {
+					// then we're trying to cast a char literal into a
+					// variable bit column.  We can't change the base
+					// type of the table constructor's value from char
+					// to bit, so instead, we just change the base type
+					// of that value from char to varchar--that way,
+					// no padding will be added when we convert to
+					// bits later on (Beetle 5306).
+						TypeId tId = TypeId.getBuiltInTypeId(java.sql.Types.VARCHAR);
+						re.setType(new DataTypeDescriptor(tId, true));
+						typeColumns.setElementAt(typeCol, index);
+					}
+					else if (colTypeId.getJDBCTypeId() == java.sql.Types.LONGVARBINARY) {
+						TypeId tId = TypeId.getBuiltInTypeId(java.sql.Types.LONGVARCHAR);
+						re.setType(new DataTypeDescriptor(tId, true));
+						typeColumns.setElementAt(typeCol, index);
+					}
+				}
+
+			}
+			else if (re instanceof BitConstantNode)
+			{
+				ResultColumn	typeCol =
+					(ResultColumn) typeColumns.elementAt(index);
+
+				TypeId colTypeId = typeCol.getTypeId();
+
+				if (colTypeId.isBitTypeId()) {
+
+					// NOTE: Don't bother doing this if the column type is BLOB,
+					// as we don't allow bit literals to be inserted into BLOB
+					// columns (they have to be explicitly casted first); beetle 5266.
+					if ((colTypeId.getJDBCTypeId() != java.sql.Types.BINARY) &&
+						(colTypeId.getJDBCTypeId() != java.sql.Types.BLOB)) {
+
+						int maxWidth = re.getTypeServices().getMaximumWidth();
+
+						re.setType(new DataTypeDescriptor(colTypeId, true, maxWidth));
+					}
+				}
+				else if (colTypeId.isStringTypeId()) {
+					if (colTypeId.getJDBCTypeId() == java.sql.Types.VARCHAR) {
+					// then we're trying to cast a bit literal into a
+					// variable char column.  We can't change the base
+					// type of the table constructor's value from bit
+					// to char, so instead, we just change the base
+					// type of that value from bit to varbit--that way,
+					// no padding will be added when we convert to
+					// char later on.
+						TypeId tId = TypeId.getBuiltInTypeId(java.sql.Types.VARBINARY);
+						re.setType(new DataTypeDescriptor(tId, true));
+						typeColumns.setElementAt(typeCol, index);
+					}
+					else if (colTypeId.getJDBCTypeId() == java.sql.Types.LONGVARCHAR) {
+						TypeId tId = TypeId.getBuiltInTypeId(java.sql.Types.LONGVARBINARY);
+						re.setType(new DataTypeDescriptor(tId, true));
+						typeColumns.setElementAt(typeCol, index);
+					}
+				}
+			}
+		}
+	}
+
     /**
      * The generated ResultSet will be:
      *

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InsertTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InsertTest.java?rev=832379&r1=832378&r2=832379&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InsertTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InsertTest.java
Tue Nov  3 10:31:21 2009
@@ -86,4 +86,52 @@
                     "select * from t2 order by int(cast (a as varchar(10)))"),
                 data);
     }
+
+    /**
+     * INSERT used to fail with a NullPointerException if the source was an
+     * EXCEPT operation or an INTERSECT operation. DERBY-4420.
+     */
+    public void testInsertFromExceptOrIntersect() throws SQLException {
+        setAutoCommit(false);
+        Statement s = createStatement();
+
+        // Create tables to fetch data from
+        s.execute("create table t1(x int)");
+        s.execute("insert into t1 values 1,2,3");
+        s.execute("create table t2(x int)");
+        s.execute("insert into t2 values 2,3,4");
+
+        // Create table to insert into
+        s.execute("create table t3(x int)");
+
+        // INTERSECT (used to cause NullPointerException)
+        s.execute("insert into t3 select * from t1 intersect select * from t2");
+        JDBC.assertFullResultSet(
+                s.executeQuery("select * from t3 order by x"),
+                new String[][]{{"2"}, {"3"}});
+        s.execute("delete from t3");
+
+        // INTERSECT ALL (used to cause NullPointerException)
+        s.execute("insert into t3 select * from t1 " +
+                  "intersect all select * from t2");
+        JDBC.assertFullResultSet(
+                s.executeQuery("select * from t3 order by x"),
+                new String[][]{{"2"}, {"3"}});
+        s.execute("delete from t3");
+
+        // EXCEPT (used to cause NullPointerException)
+        s.execute("insert into t3 select * from t1 except select * from t2");
+        JDBC.assertSingleValueResultSet(
+                s.executeQuery("select * from t3 order by x"),
+                "1");
+        s.execute("delete from t3");
+
+        // EXCEPT ALL (used to cause NullPointerException)
+        s.execute("insert into t3 select * from t1 " +
+                  "except all select * from t2");
+        JDBC.assertSingleValueResultSet(
+                s.executeQuery("select * from t3 order by x"),
+                "1");
+        s.execute("delete from t3");
+    }
 }



Mime
View raw message