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 rh...@apache.org on 2017/09/18 01:31:08 UTC
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
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);
+ }
}