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 2009/11/03 11:31:40 UTC
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
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");
+ }
}