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 ma...@apache.org on 2012/05/10 05:41:22 UTC
svn commit: r1336474 - in /db/derby/code/branches/10.8: ./
java/engine/org/apache/derby/iapi/sql/dictionary/ConglomerateDescriptor.java
java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java
Author: mamta
Date: Thu May 10 03:41:22 2012
New Revision: 1336474
URL: http://svn.apache.org/viewvc?rev=1336474&view=rev
Log:
DERBY-5681 When a foreign key constraint on a table is dropped, the associated statistics row for the conglomerate is not removed
Backporting to 10.8
Modified:
db/derby/code/branches/10.8/ (props changed)
db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/sql/dictionary/ConglomerateDescriptor.java
db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java
Propchange: db/derby/code/branches/10.8/
------------------------------------------------------------------------------
Merged /db/derby/code/trunk:r1329359
Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/sql/dictionary/ConglomerateDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/sql/dictionary/ConglomerateDescriptor.java?rev=1336474&r1=1336473&r2=1336474&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/sql/dictionary/ConglomerateDescriptor.java (original)
+++ db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/sql/dictionary/ConglomerateDescriptor.java Thu May 10 03:41:22 2012
@@ -474,11 +474,11 @@ public final class ConglomerateDescripto
physicalCD = null;
}
+ /* DERBY-5681 Drop statistics */
+ dd.dropStatisticsDescriptors(td.getUUID(), getUUID(), tc);
+
if (dropConglom)
{
- /* Drop statistics */
- dd.dropStatisticsDescriptors(td.getUUID(), getUUID(), tc);
-
/* Drop the physical conglomerate */
tc.dropConglomerate(getConglomerateNumber());
}
Modified: db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java?rev=1336474&r1=1336473&r2=1336474&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java (original)
+++ db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java Thu May 10 03:41:22 2012
@@ -269,6 +269,123 @@ public class UpdateStatisticsTest extend
dropTable("derby5153");
commit();
}
+
+ /**
+ * Fixed DERBY-5681(When a foreign key constraint on a table is dropped,
+ * the associated statistics row for the conglomerate is not removed.)
+ * @throws Exception
+ */
+ public void testDERBY5681() throws Exception {
+ // Helper object to obtain information about index statistics.
+ IndexStatsUtil stats = new IndexStatsUtil(openDefaultConnection());
+ Statement s = createStatement();
+
+ //Test - primary key constraint
+ s.executeUpdate("CREATE TABLE TEST_TAB_1 (c11 int not null,"+
+ "c12 int not null, c13 int)");
+ stats.assertNoStatsTable("TEST_TAB_1");
+ //Insert data into table with no constraint and there will be no stat
+ // for that table at this point
+ s.executeUpdate("INSERT INTO TEST_TAB_1 VALUES(1,1,1),(2,2,2)");
+ s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_1', null)");
+ stats.assertNoStatsTable("TEST_TAB_1");
+ //Add primary key constraint to the table and now we should find a
+ // statistics row for it
+ s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
+ "ADD CONSTRAINT TEST_TAB_1_PK_1 "+
+ "PRIMARY KEY (c11)");
+ stats.assertTableStats("TEST_TAB_1",1);
+ //Dropping primary key constraint will drop the corresponding
+ // statistics
+ s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
+ "DROP CONSTRAINT TEST_TAB_1_PK_1");
+ stats.assertNoStatsTable("TEST_TAB_1");
+ s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_1', null)");
+ stats.assertNoStatsTable("TEST_TAB_1");
+ //Add the primary key constraint back since it will be used by the next
+ // test to create foreign key constraint
+ s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
+ "ADD CONSTRAINT TEST_TAB_1_PK_1 "+
+ "PRIMARY KEY (c11)");
+ //The statistics for primary key constraint has been added
+ stats.assertTableStats("TEST_TAB_1",1);
+
+ //Test - unique key constraint
+ s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
+ "ADD CONSTRAINT TEST_TAB_1_UNQ_1 "+
+ "UNIQUE (c12)");
+ stats.assertTableStats("TEST_TAB_1",2);
+ s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
+ "DROP CONSTRAINT TEST_TAB_1_UNQ_1");
+ stats.assertTableStats("TEST_TAB_1",1);
+ s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
+ "DROP CONSTRAINT TEST_TAB_1_PK_1");
+ stats.assertNoStatsTable("TEST_TAB_1");
+ s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
+ "ADD CONSTRAINT TEST_TAB_1_PK_1 "+
+ "PRIMARY KEY (c11)");
+ stats.assertTableStats("TEST_TAB_1",1);
+
+ //Test - unique key constraint on nullable column & non-nullable column
+ s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
+ "ADD CONSTRAINT TEST_TAB_1_UNQ_2 "+
+ "UNIQUE (c12, c13)");
+ stats.assertTableStats("TEST_TAB_1",3);
+ s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
+ "DROP CONSTRAINT TEST_TAB_1_UNQ_2");
+ stats.assertTableStats("TEST_TAB_1",1);
+ s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
+ "DROP CONSTRAINT TEST_TAB_1_PK_1");
+ stats.assertNoStatsTable("TEST_TAB_1");
+ s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
+ "ADD CONSTRAINT TEST_TAB_1_PK_1 "+
+ "PRIMARY KEY (c11)");
+ stats.assertTableStats("TEST_TAB_1",1);
+
+ //Test - foreign key but no primary key constraint
+ s.executeUpdate("CREATE TABLE TEST_TAB_3 (c31 int not null)");
+ s.executeUpdate("INSERT INTO TEST_TAB_3 VALUES(1),(2)");
+ s.executeUpdate("ALTER TABLE TEST_TAB_3 "+
+ "ADD CONSTRAINT TEST_TAB_3_FK_1 "+
+ "FOREIGN KEY(c31) REFERENCES TEST_TAB_1(c11)");
+ stats.assertTableStats("TEST_TAB_3",1);
+ s.executeUpdate("ALTER TABLE TEST_TAB_3 "+
+ "DROP CONSTRAINT TEST_TAB_3_FK_1");
+ stats.assertNoStatsTable("TEST_TAB_3");
+
+ //Test - foreign key and primary key constraint
+ s.executeUpdate("CREATE TABLE TEST_TAB_2 (c21 int not null)");
+ s.executeUpdate("INSERT INTO TEST_TAB_2 VALUES(1),(2)");
+ s.executeUpdate("ALTER TABLE TEST_TAB_2 "+
+ "ADD CONSTRAINT TEST_TAB_2_PK_1 "+
+ "PRIMARY KEY (c21)");
+ stats.assertTableStats("TEST_TAB_2",1);
+ //Add a foreign key constraint and now we should find 2 rows of
+ // statistics for TEST_TAB_2 - 1 for primary key and other for
+ // foreign key constraint
+ s.executeUpdate("ALTER TABLE TEST_TAB_2 "+
+ "ADD CONSTRAINT TEST_TAB_2_FK_1 "+
+ "FOREIGN KEY(c21) REFERENCES TEST_TAB_1(c11)");
+ //Like primary key earlier, adding foreign key constraint didn't
+ // automatically add a statistics row for it. Have to run update
+ // statistics manually to get a row added for it's stat
+ stats.assertTableStats("TEST_TAB_2",1);
+ s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_2', null)");
+ stats.assertTableStats("TEST_TAB_2",2);
+ //Number of statistics row for TEST_TAB_1 will remain unchanged since
+ // it has only primary key defined on it
+ stats.assertTableStats("TEST_TAB_1",1);
+ s.executeUpdate("ALTER TABLE TEST_TAB_2 "+
+ "DROP CONSTRAINT TEST_TAB_2_FK_1");
+ //Dropping the foreign key constraint should remove one of the
+ // statistics row for TEST_TAB_2.
+ stats.assertTableStats("TEST_TAB_2",1);
+ s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_2', null)");
+ stats.assertTableStats("TEST_TAB_2",1);
+ s.execute("drop table TEST_TAB_2");
+ s.execute("drop table TEST_TAB_1");
+ stats.release();
+ }
/**
* A thread class that repeatedly calls SYSCS_UTIL.SYSCS_UPDATE_STATISTICS