db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r1808668 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java
Date Mon, 18 Sep 2017 01:31:08 GMT
Author: rhillegas
Date: Mon Sep 18 01:31:07 2017
New Revision: 1808668

URL: http://svn.apache.org/viewvc?rev=1808668&view=rev
Log:
DERBY-6961: Correctly handle ALTER TABLE...SET [NO] CYCLE on exhausted identity columns; commit
derby-6961-01-aa-recyclingExhaustedIdentityColumns.diff.

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/AlterTableTest.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=1808668&r1=1808667&r2=1808668&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
Mon Sep 18 01:31:07 2017
@@ -80,6 +80,7 @@ import org.apache.derby.iapi.store.acces
 import org.apache.derby.iapi.types.DataTypeDescriptor;
 import org.apache.derby.iapi.types.DataValueDescriptor;
 import org.apache.derby.iapi.types.RowLocation;
+import org.apache.derby.iapi.types.TypeId;
 import org.apache.derby.iapi.util.IdUtil;
 import org.apache.derby.impl.sql.compile.ColumnDefinitionNode;
 import org.apache.derby.impl.sql.compile.StatementNode;
@@ -94,6 +95,8 @@ import org.apache.derby.shared.common.sa
 class AlterTableConstantAction extends DDLSingleTableConstantAction
  implements RowLocationRetRowSource
 {
+    private static final int RANGE_TOP = 0;
+    private static final int RANGE_BOTTOM = 1;
 
     // copied from constructor args and stored locally.
     private	    SchemaDescriptor			sd;
@@ -2230,6 +2233,43 @@ class AlterTableConstantAction extends D
                 {
                     currentValue = dd.peekAtIdentity( td.getSchemaName(), td.getName() );
                 }
+                
+                if (columnInfo[ix].action == ColumnInfo.MODIFY_COLUMN_DEFAULT_CYCLE)
+                {
+                    if (columnInfo[ix].autoincCycle)
+                    {
+                        // ALTER TABLE ALTER COLUMN $columnName SET CYCLE
+                        if (currentValue == null)
+                        {
+                            //
+                            // If the current value is NULL, then the sequence generator
+                            // is exhausted and it must have been a NO CYCLE generator,
+                            // which we are changing to CYCLE.
+                            // According to the 2016 SQL Standard, section 4.27.2
+                            // (Operations involving sequence generators),
+                            // the next value of the sequence generator should be the minimum
value
+                            // (for an ascending sequence generator) or the maximum value
+                            // (for a descending sequence generator). See DERBY-6961.
+                            // This logic will have to change in the future if we
+                            // let users configure the maximum and minimum values of identity
columns.
+                            //
+                            int topOrBottom = (columnInfo[ix].autoincInc > 0) ? RANGE_BOTTOM
: RANGE_TOP;
+                            currentValue = getRangeBound(columnInfo[ix].dataType, topOrBottom);
+                        }
+                    }
+                    else
+                    {
+                        // ALTER TABLE ALTER COLUMN $columnName SET NO CYCLE
+                        //
+                        // If we are just about to issue the rollover value,
+                        // set it to NULL in order to prevent cycling.
+                        int topOrBottom = (columnInfo[ix].autoincInc > 0) ? RANGE_BOTTOM
: RANGE_TOP;
+                        Long rolloverValue = getRangeBound(columnInfo[ix].dataType, topOrBottom);
+
+                        if ((currentValue != null) && (currentValue.equals(rolloverValue)))
+                        { currentValue = null; }
+                    }
+                }
 
                 DropTableConstantAction.dropIdentitySequence( dd, td, activation );
 
@@ -2256,7 +2296,41 @@ class AlterTableConstantAction extends D
             }
         }
 	}
-	
+
+    /**
+     * Get the ran max or min range bound for an autoincrement column.
+     *
+     * @param dtd The type of the autoincrement column.
+     * @param topOrBottom RANGE_TOP or RANGE_BOTTOM
+     *
+     * @returns the top or bottom of the range
+     */
+    private long getRangeBound(DataTypeDescriptor dtd, int topOrBottom)
+        throws StandardException
+    {
+        TypeId typeId = dtd.getTypeId();
+        boolean bottom = (topOrBottom == RANGE_BOTTOM);
+        if (typeId == TypeId.SMALLINT_ID)
+        {
+            return (bottom ? Long.valueOf(Short.MIN_VALUE) : Long.valueOf(Short.MAX_VALUE));
+        }
+        else if (typeId == TypeId.INTEGER_ID)
+        {
+            return (bottom ? Long.valueOf(Integer.MIN_VALUE) : Long.valueOf(Integer.MAX_VALUE));
+        }
+        else // must be BIGINT
+        {
+            // but verify this is BIGINT in case someone adds
+            // a new numeric type in the future
+            if (typeId != TypeId.BIGINT_ID)
+            {
+                throw StandardException.newException( SQLState.NOT_IMPLEMENTED );
+            }
+
+            return (bottom ? Long.MIN_VALUE : Long.MAX_VALUE);
+        }
+    }
+  
 	/**
 	 * Change an identity from ALWAYS to BY DEFAULT (or vice versa)
 	 * 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java?rev=1808668&r1=1808667&r2=1808668&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java
Mon Sep 18 01:31:07 2017
@@ -44,6 +44,7 @@ public final class AlterTableTest extend
     private static final String CANNOT_ALTER_NON_IDENTITY_COLUMN = "42Z29";
     private static final String CANNOT_MODIFY_ALWAYS_IDENTITY_COLUMN = "42Z23";
     private static final String DUPLICATE_KEY = "23505";
+    private static final String EXHAUSTED_IDENTITY_COLUMN = "2200H";
 
     /**
      * Public constructor required for running test as standalone JUnit.
@@ -399,7 +400,7 @@ public final class AlterTableTest extend
         // Generated identity values cannot grow beyond the limits of
         // the data type.
         s.execute("insert into t0 values 1,2,3,4");
-        assertStatementError("2200H", s,
+        assertStatementError(EXHAUSTED_IDENTITY_COLUMN, s,
                 "alter table t0 add column id smallint generated always as "
                 + "identity (start with 30000, increment by 1000)");
         rollback();
@@ -414,7 +415,7 @@ public final class AlterTableTest extend
                     { "2", "31000" },
                     { "3", "32000" },
                 });
-        assertStatementError("2200H", s, "insert into t0(c1) values 4");
+        assertStatementError(EXHAUSTED_IDENTITY_COLUMN, s, "insert into t0(c1) values 4");
         rollback();
 
         // Drop an identity column that was added with ALTER TABLE.
@@ -710,7 +711,7 @@ public final class AlterTableTest extend
         // Generated identity values cannot grow beyond the limits of
         // the data type.
         s.execute("insert into t0 values 1,2,3,4");
-        assertStatementError("2200H", s,
+        assertStatementError(EXHAUSTED_IDENTITY_COLUMN, s,
                 "alter table t0 add column id smallint generated always as "
                 + "identity (start with 30000, increment by 1000)");
         rollback();
@@ -725,7 +726,7 @@ public final class AlterTableTest extend
                     { "2", "31000" },
                     { "3", "32000" },
                 });
-        assertStatementError("2200H", s, "insert into t0(c1) values 4");
+        assertStatementError(EXHAUSTED_IDENTITY_COLUMN, s, "insert into t0(c1) values 4");
         rollback();
 
         // Drop an identity column that was added with ALTER TABLE.
@@ -893,7 +894,7 @@ public final class AlterTableTest extend
         // Generated identity values cannot grow beyond the limits of
         // the data type.
         s.execute("insert into t0 values 1,2,3,4");
-        assertStatementError("2200H", s,
+        assertStatementError(EXHAUSTED_IDENTITY_COLUMN, s,
                 "alter table t0 add column id smallint generated always as "
                 + "identity (start with 30000, increment by 1000)");
         rollback();
@@ -908,7 +909,7 @@ public final class AlterTableTest extend
                     { "2", "31000" },
                     { "3", "32000" },
                 });
-        assertStatementError("2200H", s, "insert into t0(c1) values 4");
+        assertStatementError(EXHAUSTED_IDENTITY_COLUMN, s, "insert into t0(c1) values 4");
         rollback();
 
         // Drop an identity column that was added with ALTER TABLE.
@@ -4847,4 +4848,321 @@ public final class AlterTableTest extend
         goodStatement(conn, "drop table source_6882");
     }
     
+    /**
+     * Test the cycling behavior fixed by DERBY-6961:
+     * Exhausted identity columns should revive at the
+     * correct end of the legal range.
+     */
+    public void test_6961_setCycle() throws Exception
+    {
+        Connection conn = getConnection();
+
+        // SMALLINT
+        vet_6961_cycling
+          (
+           conn,
+           "t_nc_smallint_asc",
+           "(\n" +
+           "  a smallint generated always as identity (start with 32766 no cycle),\n" +
+           "  b int\n" +
+           ")\n",
+           new String[][]
+           {
+             {"32766", "1"},
+             {"32767", "2"},
+             {"-32768", "3"},
+           }
+           );
+        vet_6961_cycling
+          (
+           conn,
+           "t_nc_smallint_desc",
+           "(\n" +
+           "  a smallint generated always as identity (start with -32767 increment by -1
no cycle),\n" +
+           "  b int\n" +
+           ")\n",
+           new String[][]
+           {
+             {"-32767", "1"},
+             {"-32768", "2"},
+             {"32767", "3"},
+           }
+           );
+
+        // INT
+        vet_6961_cycling
+          (
+           conn,
+           "t_nc_int_asc",
+           "(\n" +
+           "  a int generated always as identity (start with 2147483646 no cycle),\n" +
+           "  b int\n" +
+           ")\n",
+           new String[][]
+           {
+             {"2147483646", "1"},
+             {"2147483647", "2"},
+             {"-2147483648", "3"},
+           }
+           );
+        vet_6961_cycling
+          (
+           conn,
+           "t_nc_int_desc",
+           "(\n" +
+           "  a int generated always as identity (start with -2147483647 increment by -1
no cycle),\n" +
+           "  b int\n" +
+           ")\n",
+           new String[][]
+           {
+             {"-2147483647", "1"},
+             {"-2147483648", "2"},
+             {"2147483647", "3"},
+           }
+           );
+        
+        // BIGINT
+        vet_6961_cycling
+          (
+           conn,
+           "t_nc_bigint_asc",
+           "(\n" +
+           "  a bigint generated always as identity (start with 9223372036854775806 no cycle),\n"
+
+           "  b int\n" +
+           ")\n",
+           new String[][]
+           {
+             {"9223372036854775806", "1"},
+             {"9223372036854775807", "2"},
+             {"-9223372036854775808", "3"},
+           }
+           );
+        vet_6961_cycling
+          (
+           conn,
+           "t_nc_bigint_desc",
+           "(\n" +
+           "  a bigint generated always as identity (start with -9223372036854775807 increment
by -1 no cycle),\n" +
+           "  b int\n" +
+           ")\n",
+           new String[][]
+           {
+             {"-9223372036854775807", "1"},
+             {"-9223372036854775808", "2"},
+             {"9223372036854775807", "3"},
+           }
+           );
+    }
+    private void vet_6961_cycling
+      (
+       Connection conn,
+       String tableName,
+       String tableDefinition,
+       String[][] expectedFinalResults
+       )
+      throws Exception
+    {
+        goodStatement(conn, "create table " + tableName + " " + tableDefinition);
+        goodStatement(conn, "insert into " + tableName + "(b) values (1)");
+        goodStatement(conn, "insert into " + tableName + "(b) values (2)");
+
+        String nextInsert = "insert into " + tableName + "(b) values (3)";
+
+        // at first the insert fails because the sequence is exhausted
+        try (PreparedStatement ps = conn.prepareStatement(nextInsert))
+        {
+            assertPreparedStatementError
+              (
+               EXHAUSTED_IDENTITY_COLUMN,
+               ps
+               );
+        }
+
+        // now change the cycling behavior
+        goodStatement(conn, "alter table " + tableName + " alter column a set cycle");
+
+        // the insert should succeed
+        goodStatement(conn, nextInsert);
+
+        // verify the results
+        assertResults
+          (conn, "select * from " + tableName + " order by b", expectedFinalResults, false);
+    }
+
+    /**
+     * Test the cycling behavior fixed by DERBY-6961:
+     * Identity columns which are about to rollover
+     * should be arrested if the column is set to NO CYCLE.
+     */
+    public void test_6961_setNoCycle() throws Exception
+    {
+        Connection conn = getConnection();
+
+        // SMALLINT
+        vet_6961_noCycling
+          (
+           conn,
+           "t_c_smallint_asc",
+           "(\n" +
+           "  a smallint generated always as identity (start with 32766 cycle),\n" +
+           "  b int\n" +
+           ")\n",
+           new String[][]
+           {
+             {"32766", "1"},
+             {"32767", "2"},
+           }
+           );
+        vet_6961_noCycling
+          (
+           conn,
+           "t_c_smallint_desc",
+           "(\n" +
+           "  a smallint generated always as identity (start with -32767 increment by -1
cycle),\n" +
+           "  b int\n" +
+           ")\n",
+           new String[][]
+           {
+             {"-32767", "1"},
+             {"-32768", "2"},
+           }
+           );
+
+        // INT
+        vet_6961_noCycling
+          (
+           conn,
+           "t_c_int_asc",
+           "(\n" +
+           "  a int generated always as identity (start with 2147483646 cycle),\n" +
+           "  b int\n" +
+           ")\n",
+           new String[][]
+           {
+             {"2147483646", "1"},
+             {"2147483647", "2"},
+           }
+           );
+        vet_6961_noCycling
+          (
+           conn,
+           "t_c_int_desc",
+           "(\n" +
+           "  a int generated always as identity (start with -2147483647 increment by -1
cycle),\n" +
+           "  b int\n" +
+           ")\n",
+           new String[][]
+           {
+             {"-2147483647", "1"},
+             {"-2147483648", "2"},
+           }
+           );
+        
+        // BIGINT
+        vet_6961_noCycling
+          (
+           conn,
+           "t_c_bigint_asc",
+           "(\n" +
+           "  a bigint generated always as identity (start with 9223372036854775806 cycle),\n"
+
+           "  b int\n" +
+           ")\n",
+           new String[][]
+           {
+             {"9223372036854775806", "1"},
+             {"9223372036854775807", "2"},
+           }
+           );
+        vet_6961_noCycling
+          (
+           conn,
+           "t_c_bigint_desc",
+           "(\n" +
+           "  a bigint generated always as identity (start with -9223372036854775807 increment
by -1 cycle),\n" +
+           "  b int\n" +
+           ")\n",
+           new String[][]
+           {
+             {"-9223372036854775807", "1"},
+             {"-9223372036854775808", "2"},
+           }
+           );
+    }
+
+    private void vet_6961_noCycling
+      (
+       Connection conn,
+       String tableName,
+       String tableDefinition,
+       String[][] expectedFinalResults
+       )
+      throws Exception
+    {
+        goodStatement(conn, "create table " + tableName + " " + tableDefinition);
+        goodStatement(conn, "insert into " + tableName + "(b) values (1)");
+        goodStatement(conn, "insert into " + tableName + "(b) values (2)");
+
+        // we are now at the rollover point.
+        // change the cycling behavior
+        goodStatement(conn, "alter table " + tableName + " alter column a set no cycle");
+
+        // the insert should now fail
+        String nextInsert = "insert into " + tableName + "(b) values (3)";
+        try (PreparedStatement ps = conn.prepareStatement(nextInsert))
+        {
+            assertPreparedStatementError
+              (
+               EXHAUSTED_IDENTITY_COLUMN,
+               ps
+               );
+        }
+
+        // verify the results
+        assertResults
+          (conn, "select * from " + tableName + " order by b", expectedFinalResults, false);
+    }
+
+    /**
+     * Test that rolling back an ALTER TABLE...SET NO CYCLE
+     * command allows the identity column to continue cycling.
+     */
+    public void test_6961_rollback() throws Exception
+    {
+        Connection conn = getConnection();
+        boolean originalAutocommit = conn.getAutoCommit();
+        conn.setAutoCommit(false);
+
+        goodStatement
+          (
+           conn,
+           "create table t_c_6961_rollback\n" +
+           "(a int generated always as identity (start with 2147483646 cycle), b int)"
+           );
+        conn.commit();
+
+        goodStatement(conn, "insert into t_c_6961_rollback(b) values (1)");
+        goodStatement(conn, "insert into t_c_6961_rollback(b) values (2)");
+        conn.commit();
+
+        goodStatement(conn, "alter table t_c_6961_rollback alter column a set no cycle");
+        conn.rollback();
+
+        goodStatement(conn, "insert into t_c_6961_rollback(b) values (3)");
+
+        assertResults
+          (
+           conn,
+           "select * from t_c_6961_rollback order by b",
+           new String[][]
+           {
+             {"2147483646", "1"},
+             {"2147483647", "2"},
+             {"-2147483648", "3"},
+           },
+           false
+           );
+        conn.commit();
+        
+        conn.setAutoCommit(originalAutocommit);
+    }
 }



Mime
View raw message