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 mi...@apache.org on 2010/07/06 01:15:44 UTC
svn commit: r960731 - in /db/derby/code/branches/10.5: ./
java/engine/org/apache/derby/impl/sql/compile/
java/testing/org/apache/derbyTesting/functionTests/master/
java/testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: mikem
Date: Mon Jul 5 23:15:43 2010
New Revision: 960731
URL: http://svn.apache.org/viewvc?rev=960731&view=rev
Log:
DERBY-4442: Default value and identity in an INSERT result set evaluated too early
backporting change #885421 from trunk to 10.5 branch.
Always add a ProjectRestrictNode on top of the source result set for an
insert if the source columns are not ordered the same way as the target
columns, or if inserting into a subset of the columns, and the source is not
a table constructor. Then the adding of default values to the source result
column list happens on top of the original source result node, and not
inside that node. This means that the source result is produced before the
default values are added, and the generation of the defaults happens at the
correct time.
As a side-effect, these other issues are fixed:
DERBY-3 Identity column not filled consecutively with "insert ... select distinct"
DERBY-4433 Cannot insert from EXCEPT/INTERSECT when target table has more columns than the source
Much of the added code is actually old code revived from the Subversion
history. Before DERBY-1644, we used to put a ProjectRestrictNode on top of
the source result if it was a SetOperatorNode. The code that inserted the
ProjectRestrictNode was added back and changed to operate on ResultSetNode
instead of SetOperatorNode.
Modified:
db/derby/code/branches/10.5/ (props changed)
db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java
db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java
db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java
db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/master/intersect.out
db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DistinctTest.java
db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql
Propchange: db/derby/code/branches/10.5/
------------------------------------------------------------------------------
--- svn:mergeinfo (original)
+++ svn:mergeinfo Mon Jul 5 23:15:43 2010
@@ -1,2 +1,2 @@
/db/derby/code/branches/10.6:957000
-/db/derby/code/trunk:757811,769596,769602,769606,769962,772090,772337,772449,772534,774281,777105,779681,782991,785131,785139,785163,785570,785662,788369,788670,788674,788968,789264,790218,791027,792434,793089,793588,794106,794303,794955,795166,795459,796020,796027,796316,796372,797147,798347,798742,800523,803548,803948,805696,808494,808850,809643,810860,812669,816531,816536,819006,822289,823659,824694,829022,829410,831304,831319,832379,833430,835286,881074,881444,882732,884163,887246,892912,897161,898635,901165,901648,901760,903108,908418,909176,911315,915733,916075,916897,918359,921028,927430,928065,942286,942476,942480,942587,946794,948045,948069,951346,954748,955001,955634,956075,956445,956659,958163,959550
+/db/derby/code/trunk:757811,769596,769602,769606,769962,772090,772337,772449,772534,774281,777105,779681,782991,785131,785139,785163,785570,785662,788369,788670,788674,788968,789264,790218,791027,792434,793089,793588,794106,794303,794955,795166,795459,796020,796027,796316,796372,797147,798347,798742,800523,803548,803948,805696,808494,808850,809643,810860,812669,816531,816536,819006,822289,823659,824694,829022,829410,831304,831319,832379,833430,835286,881074,881444,882732,884163,885421,887246,892912,897161,898635,901165,901648,901760,903108,908418,909176,911315,915733,916075,916897,918359,921028,927430,928065,942286,942476,942480,942587,946794,948045,948069,951346,954748,955001,955634,956075,956445,956659,958163,959550
Modified: db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java?rev=960731&r1=960730&r2=960731&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java (original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java Mon Jul 5 23:15:43 2010
@@ -411,9 +411,7 @@ public final class InsertNode extends DM
}
}
- enhanceAndCheckForAutoincrement(resultSet, inOrder,
- numTableColumns, colMap, dataDictionary,
- targetTableDescriptor, targetVTI );
+ resultSet = enhanceAndCheckForAutoincrement(resultSet, inOrder, colMap);
resultColumnList.checkStorableExpressions(resultSet.getResultColumns());
/* Insert a NormalizeResultSetNode above the source if the source
@@ -529,19 +527,13 @@ public final class InsertNode extends DM
*
* @param resultSet current node in the result set tree
* @param inOrder FALSE if the column list needs reordering
- * @param numTableColumns # of columns in target RCL
* @param colMap correspondence between RCLs
- * @param dataDictionary DataDictionary to use
- * @param targetTableDescriptor Table Descriptor for target
- * @param targetVTI Target description if it is a VTI
+ * @return a node representing the source for the insert
*
* @exception StandardException Thrown on error
*/
- private void enhanceAndCheckForAutoincrement(ResultSetNode resultSet,
- boolean inOrder, int numTableColumns, int []colMap,
- DataDictionary dataDictionary,
- TableDescriptor targetTableDescriptor,
- FromVTI targetVTI)
+ ResultSetNode enhanceAndCheckForAutoincrement(
+ ResultSetNode resultSet, boolean inOrder, int[] colMap)
throws StandardException
{
/*
@@ -565,40 +557,24 @@ public final class InsertNode extends DM
* value expressions.
*/
- if (resultSet instanceof SingleChildResultSetNode)
- {
- enhanceAndCheckForAutoincrement(
- ((SingleChildResultSetNode)resultSet).getChildResult(),
- inOrder, numTableColumns, colMap, dataDictionary,
- targetTableDescriptor, targetVTI);
- if (! inOrder || resultSet.resultColumns.size() < numTableColumns)
- resultSet.enhanceRCLForInsert(
- numTableColumns, colMap, dataDictionary,
- targetTableDescriptor, targetVTI);
- }
- else if (resultSet instanceof UnionNode)
- {
- enhanceAndCheckForAutoincrement(
- ((TableOperatorNode)resultSet).getLeftResultSet(),
- inOrder, numTableColumns, colMap, dataDictionary,
- targetTableDescriptor, targetVTI);
- enhanceAndCheckForAutoincrement(
- ((TableOperatorNode)resultSet).getRightResultSet(),
- inOrder, numTableColumns, colMap, dataDictionary,
- targetTableDescriptor, targetVTI);
- if (! inOrder || resultSet.resultColumns.size() < numTableColumns)
- resultSet.enhanceRCLForInsert(
- numTableColumns, colMap, dataDictionary,
- targetTableDescriptor,targetVTI);
- }
- else
- {
- if (! inOrder || resultSet.resultColumns.size() < numTableColumns)
- resultSet.enhanceRCLForInsert(
- numTableColumns, colMap, dataDictionary,
- targetTableDescriptor, targetVTI);
+ resultSet = resultSet.enhanceRCLForInsert(this, inOrder, colMap);
+
+ // Forbid overrides for generated columns and identity columns that
+ // are defined as GENERATED ALWAYS.
+ if ((resultSet instanceof UnionNode) &&
+ ((UnionNode) resultSet).tableConstructor()) {
+ // If this is a multi-row table constructor, we are not really
+ // interested in the result column list of the top-level UnionNode.
+ // The interesting RCLs are those of the RowResultSetNode children
+ // of the UnionNode, and they have already been checked from
+ // UnionNode.enhanceRCLForInsert(). Since the RCL of the UnionNode
+ // doesn't tell whether or not DEFAULT is specified at the leaf
+ // level, we need to skip it here to avoid false positives.
+ } else {
resultColumnList.forbidOverrides(resultSet.getResultColumns());
}
+
+ return resultSet;
}
int getPrivType()
Modified: db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java?rev=960731&r1=960730&r2=960731&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java (original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java Mon Jul 5 23:15:43 2010
@@ -918,24 +918,42 @@ public abstract class ResultSetNode exte
/**
* This ResultSet is the source for an Insert. The target RCL
* is in a different order and/or a superset of this RCL. In most cases
- * we will reorder and/or add defaults to the current RCL so that is
+ * we will add a ProjectRestrictNode on top of the source with an RCL that
* matches the target RCL.
* NOTE - The new or enhanced RCL will be fully bound.
*
- * @param numTargetColumns # of columns in target RCL
+ * @param target the target node for the insert
+ * @param inOrder are source cols in same order as target cols?
* @param colMap int array representation of correspondence between
* RCLs - colmap[i] = -1 -> missing in current RCL
* colmap[i] = j -> targetRCL(i) <-> thisRCL(j+1)
- * @param dataDictionary DataDictionary to use
- * @param targetTD TableDescriptor for target if the target is not a VTI, null if a VTI
- * @param targetVTI Target description if it is a VTI, null if not a VTI
+ * @return a node that replaces this node and whose RCL matches the target
+ * RCL. May return this node if no changes to the RCL are needed, or if the
+ * RCL is modified in-place.
*
* @exception StandardException Thrown on error
*/
- public void enhanceRCLForInsert(int numTargetColumns, int[] colMap,
- DataDictionary dataDictionary,
- TableDescriptor targetTD,
- FromVTI targetVTI)
+ ResultSetNode enhanceRCLForInsert(
+ InsertNode target, boolean inOrder, int[] colMap)
+ throws StandardException
+ {
+ if (!inOrder || resultColumns.size() < target.resultColumnList.size()) {
+ return generateProjectRestrictForInsert(target, colMap);
+ }
+ return this;
+ }
+
+ /**
+ * Generate an RCL that can replace the original RCL of this node to
+ * match the RCL of the target for the insert.
+ *
+ * @param target the target node for the insert
+ * @param colMap int array representation of correspondence between
+ * RCLs - colmap[i] = -1 -> missing in current RCL
+ * colmap[i] = j -> targetRCL(i) <-> thisRCL(j+1)
+ * @return an RCL that matches the target RCL
+ */
+ ResultColumnList getRCLForInsert(InsertNode target, int[] colMap)
throws StandardException
{
// our newResultCols are put into the bound form straight away.
@@ -948,10 +966,10 @@ public abstract class ResultSetNode exte
* (Much simpler to build new list and then assign to source,
* rather than massage the source list in place.)
*/
+ int numTargetColumns = target.resultColumnList.size();
for (int index = 0; index < numTargetColumns; index++)
{
ResultColumn newResultColumn = null;
- ColumnReference newColumnReference;
if (colMap[index] != -1)
{
@@ -960,14 +978,17 @@ public abstract class ResultSetNode exte
}
else
{
- newResultColumn = genNewRCForInsert(targetTD, targetVTI, index + 1, dataDictionary);
+ newResultColumn = genNewRCForInsert(
+ target.targetTableDescriptor,
+ target.targetVTI,
+ index + 1,
+ target.getDataDictionary());
}
newResultCols.addResultColumn(newResultColumn);
}
- /* Set the source RCL to the massaged version */
- resultColumns = newResultCols;
+ return newResultCols;
}
/**
@@ -1074,6 +1095,103 @@ public abstract class ResultSetNode exte
}
/**
+ * Generate a ProjectRestrictNode to put on top of this node if it's the
+ * source for an insert, and the RCL needs reordering and/or addition of
+ * columns in order to match the target RCL.
+ *
+ * @param target the target node for the insert
+ * @param colMap int array representation of correspondence between
+ * RCLs - colmap[i] = -1 -> missing in current RCL
+ * colmap[i] = j -> targetRCL(i) <-> thisRCL(j+1)
+ * @return a ProjectRestrictNode whos RCL matches the target RCL
+ */
+ private ResultSetNode generateProjectRestrictForInsert(
+ InsertNode target, int[] colMap)
+ throws StandardException
+ {
+ // our newResultCols are put into the bound form straight away.
+ ResultColumnList newResultCols =
+ (ResultColumnList) getNodeFactory().getNode(
+ C_NodeTypes.RESULT_COLUMN_LIST,
+ getContextManager());
+
+ int numTargetColumns = target.resultColumnList.size();
+
+ /* Create a massaged version of the source RCL.
+ * (Much simpler to build new list and then assign to source,
+ * rather than massage the source list in place.)
+ */
+ for (int index = 0; index < numTargetColumns; index++)
+ {
+ ResultColumn newResultColumn;
+ ResultColumn oldResultColumn;
+ ColumnReference newColumnReference;
+
+ if (colMap[index] != -1)
+ {
+ // getResultColumn uses 1-based positioning, so offset the
+ // colMap entry appropriately
+ oldResultColumn =
+ resultColumns.getResultColumn(colMap[index] + 1);
+
+ newColumnReference = (ColumnReference) getNodeFactory().getNode(
+ C_NodeTypes.COLUMN_REFERENCE,
+ oldResultColumn.getName(),
+ null,
+ getContextManager());
+ /* The ColumnReference points to the source of the value */
+ newColumnReference.setSource(oldResultColumn);
+ // colMap entry is 0-based, columnId is 1-based.
+ newColumnReference.setType(oldResultColumn.getType());
+
+ // Source of an insert, so nesting levels must be 0
+ newColumnReference.setNestingLevel(0);
+ newColumnReference.setSourceLevel(0);
+
+ // because the insert already copied the target table's
+ // column descriptors into the result, we grab it from there.
+ // alternatively, we could do what the else clause does,
+ // and look it up in the DD again.
+ newResultColumn = (ResultColumn) getNodeFactory().getNode(
+ C_NodeTypes.RESULT_COLUMN,
+ oldResultColumn.getType(),
+ newColumnReference,
+ getContextManager());
+ }
+ else
+ {
+ newResultColumn = genNewRCForInsert(
+ target.targetTableDescriptor,
+ target.targetVTI,
+ index + 1,
+ target.getDataDictionary());
+ }
+
+ newResultCols.addResultColumn(newResultColumn);
+ }
+
+ /* The generated ProjectRestrictNode now has the ResultColumnList
+ * in the order that the InsertNode expects.
+ * NOTE: This code here is an exception to several "rules":
+ * o This is the only ProjectRestrictNode that is currently
+ * generated outside of preprocess().
+ * o The UnionNode is the only node which is not at the
+ * top of the query tree which has ColumnReferences under
+ * its ResultColumnList prior to expression push down.
+ */
+ return (ResultSetNode) getNodeFactory().getNode(
+ C_NodeTypes.PROJECT_RESTRICT_NODE,
+ this,
+ newResultCols,
+ null,
+ null,
+ null,
+ null,
+ null,
+ getContextManager());
+ }
+
+ /**
* Create a ResultColumn for a column with a generation clause.
*/
private ResultColumn createGeneratedColumn
Modified: db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java?rev=960731&r1=960730&r2=960731&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java (original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java Mon Jul 5 23:15:43 2010
@@ -123,6 +123,18 @@ public class RowResultSetNode extends Fr
}
}
+ /**
+ * Modify the RCL of this node to match the target of the insert.
+ */
+ ResultSetNode enhanceRCLForInsert(
+ InsertNode target, boolean inOrder, int[] colMap)
+ throws StandardException
+ {
+ if (!inOrder || resultColumns.size() < target.resultColumnList.size()) {
+ resultColumns = getRCLForInsert(target, colMap);
+ }
+ return this;
+ }
/*
* Optimizable interface
Modified: db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java?rev=960731&r1=960730&r2=960731&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java (original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java Mon Jul 5 23:15:43 2010
@@ -184,6 +184,40 @@ public class UnionNode extends SetOperat
}
}
+ /**
+ * Make the RCL of this node match the target node for the insert. If this
+ * node represents a table constructor (a VALUES clause), we replace the
+ * RCL with an enhanced one if necessary, and recursively enhance the RCL
+ * of each child node. For table constructors, we also need to check that
+ * we don't attempt to override auto-increment columns in each child node
+ * (checking the top-level RCL isn't sufficient since a table constructor
+ * may contain the DEFAULT keyword, which makes it possible to specify a
+ * column without overriding its value).
+ *
+ * If this node represents a regular UNION, put a ProjectRestrictNode on
+ * top of this node and enhance the RCL in that node.
+ */
+ ResultSetNode enhanceRCLForInsert(
+ InsertNode target, boolean inOrder, int[] colMap)
+ throws StandardException
+ {
+ if (tableConstructor()) {
+ leftResultSet = target.enhanceAndCheckForAutoincrement(
+ leftResultSet, inOrder, colMap);
+ rightResultSet = target.enhanceAndCheckForAutoincrement(
+ rightResultSet, inOrder, colMap);
+ if (!inOrder ||
+ resultColumns.size() < target.resultColumnList.size()) {
+ resultColumns = getRCLForInsert(target, colMap);
+ }
+ return this;
+ } else {
+ // This is a regular UNION, so fall back to the default
+ // implementation that adds a ProjectRestrictNode on top.
+ return super.enhanceRCLForInsert(target, inOrder, colMap);
+ }
+ }
+
/*
* Optimizable interface
*/
Modified: db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/master/intersect.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/master/intersect.out?rev=960731&r1=960730&r2=960731&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/master/intersect.out (original)
+++ db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/master/intersect.out Mon Jul 5 23:15:43 2010
@@ -406,4 +406,31 @@ ID |2 |3
-----------------------------------
1 |1 |1
5 |NULL |NULL
+ij> -- DERBY-4433: Insert from INTERSECT/EXCEPT into subset of columns
+create table d4433_t1(x int);
+0 rows inserted/updated/deleted
+ij> insert into d4433_t1 values 1,2,3,4;
+4 rows inserted/updated/deleted
+ij> create table d4433_t2(x int);
+0 rows inserted/updated/deleted
+ij> insert into d4433_t2 values 3,4,5,6;
+4 rows inserted/updated/deleted
+ij> create table d4433_t3(x int, y int);
+0 rows inserted/updated/deleted
+ij> insert into d4433_t3(x) select x from d4433_t1 intersect select x from d4433_t2;
+2 rows inserted/updated/deleted
+ij> select * from d4433_t3 order by x, y;
+X |Y
+-----------------------
+3 |NULL
+4 |NULL
+ij> insert into d4433_t3(x) select x from d4433_t1 except select x from d4433_t2;
+2 rows inserted/updated/deleted
+ij> select * from d4433_t3 order by x, y;
+X |Y
+-----------------------
+1 |NULL
+2 |NULL
+3 |NULL
+4 |NULL
ij>
Modified: db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DistinctTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DistinctTest.java?rev=960731&r1=960730&r2=960731&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DistinctTest.java (original)
+++ db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DistinctTest.java Mon Jul 5 23:15:43 2010
@@ -434,10 +434,8 @@ public class DistinctTest extends BaseJD
/**
* This test demonstrates that using distinct in a query for insert
- * generates gaps in numbering in autoincremented columns.
- *
- * See DERBY-3. If that bug is fixed, the first query after the comment
- * below will fail.
+ * does not cause gaps in numbering in autoincremented columns. Before
+ * DERBY-3 was fixed, there could be gaps.
*
* @throws SQLException
*/
@@ -458,9 +456,9 @@ public class DistinctTest extends BaseJD
assertRowCount(2, s.executeQuery("select distinct(c31) from source"));
assertEquals(2, s.executeUpdate("insert into destWithAI(c12) select distinct(c31) from source"));
- //we will see gaps in the autoincrement column for all the duplicate rows from source
+ // we should not see gaps in the autoincrement column
String [][] expected = { {"1", "1"},
- {"3", "2"} };
+ {"2", "2"} };
JDBC.assertFullResultSet(s.executeQuery("select * from destWithAI"), expected);
assertEquals(2, s.executeUpdate("insert into destWithNoAI(c22) select distinct(c31) from source"));
Modified: db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql?rev=960731&r1=960730&r2=960731&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql (original)
+++ db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql Mon Jul 5 23:15:43 2010
@@ -176,3 +176,14 @@ select * from view_ex_all order by 1 DES
-- intersect joins
select t1.id,t1.i1,t2.i1 from t1 join t2 on t1.id = t2.id
intersect select t1.id,t1.i2,t2.i2 from t1 join t2 on t1.id = t2.id;
+
+-- DERBY-4433: Insert from INTERSECT/EXCEPT into subset of columns
+create table d4433_t1(x int);
+insert into d4433_t1 values 1,2,3,4;
+create table d4433_t2(x int);
+insert into d4433_t2 values 3,4,5,6;
+create table d4433_t3(x int, y int);
+insert into d4433_t3(x) select x from d4433_t1 intersect select x from d4433_t2;
+select * from d4433_t3 order by x, y;
+insert into d4433_t3(x) select x from d4433_t1 except select x from d4433_t2;
+select * from d4433_t3 order by x, y;