svn commit: r1550284 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java testing/org/apache/derbyTesting/functionTests/tests/lang/ConstraintCharacteristicsTest.java

[da...@apache.org]

Author: dag
Date: Wed Dec 11 22:07:15 2013
New Revision: 1550284

URL: http://svn.apache.org/r1550284
Log:
DERBY-532 Support deferrable constraints

Patch derby-532-fix-drop-not-nullable. Fixes a broken predicate when
recreating the index when going from UNIQUE NOT NULL to plain UNIQUE:
the existing predicate missed the deferrable case, so the index was not
recreated.

Added a test case.


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConstraintCharacteristicsTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java?rev=1550284&r1=1550283&r2=1550284&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java Wed Dec 11 22:07:15 2013
@@ -2048,8 +2048,10 @@ class AlterTableConstantAction extends D
 
                     //check if the backing index was created when the column
                     //not null ie is backed by unique index
-                    if (!desc.getIndexDescriptor().isUnique())
+                    if (! (desc.getIndexDescriptor().isUnique() ||
+                           desc.getIndexDescriptor().hasDeferrableChecking())) {
                         break;
+                    }
 
                     // replace backing index with a unique when not null index.
                     recreateUniqueConstraintBackingIndexAsUniqueWhenNotNull(

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConstraintCharacteristicsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConstraintCharacteristicsTest.java?rev=1550284&r1=1550283&r2=1550284&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConstraintCharacteristicsTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConstraintCharacteristicsTest.java Wed Dec 11 22:07:15 2013
@@ -47,7 +47,6 @@ import org.apache.derby.iapi.sql.conn.La
 import org.apache.derby.impl.jdbc.EmbedConnection;
 import org.apache.derby.impl.sql.GenericPreparedStatement;
 import org.apache.derbyTesting.junit.BaseJDBCTestCase;
-import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
 import org.apache.derbyTesting.junit.J2EEDataSource;
 import org.apache.derbyTesting.junit.JDBC;
 import org.apache.derbyTesting.junit.SupportFilesSetup;
@@ -95,6 +94,8 @@ public class ConstraintCharacteristicsTe
         TestSuite s = new TestSuite("WithLenientChecking");
 
         s.addTest(new ConstraintCharacteristicsTest(
+                      "testDropNotNullOnUniqueColumn"));
+        s.addTest(new ConstraintCharacteristicsTest(
                       "testCompressTable"));
         s.addTest(new ConstraintCharacteristicsTest(
                       "testLocking"));
@@ -897,6 +898,40 @@ public class ConstraintCharacteristicsTe
         }
     }
 
+    // Adapted from UniqueConstraintSetNullTest which exposed an error
+    // when we ran all regressions with default deferrable: when a NOT NULL
+    // clause was dropped, the test used to drop and recreate the index to
+    // be non-unique was incomplete in the deferrable case.
+    public void testDropNotNullOnUniqueColumn() throws SQLException {
+        Statement s = createStatement();
+
+        s.executeUpdate("create table constraintest (" +
+                "val1 varchar (20) not null, " +
+                "val2 varchar (20))");
+
+        s.executeUpdate("alter table constraintest add constraint " +
+                "u_con unique (val1) deferrable initially immediate");
+
+        s.executeUpdate("alter table constraintest alter column val1 null");
+
+        s.executeUpdate("insert into constraintest(val1) values 'name1'");
+
+        try {
+            s.executeUpdate("insert into constraintest(val1) values 'name1'");
+            fail("expected duplicate error");
+        } catch (SQLException e) {
+            assertSQLState(LANG_DUPLICATE_KEY_CONSTRAINT, e);
+        }
+
+        PreparedStatement ps = prepareStatement(
+                "insert into constraintest(val1) values (?)");
+        ps.setString(1, null);
+        ps.executeUpdate();
+        ps.setString(1, null);
+        ps.executeUpdate();
+    }
+
+
     public void testDerby6374() throws SQLException {
         Statement s = createStatement();