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 bp...@apache.org on 2007/02/05 05:58:53 UTC
svn commit: r503550 - in /db/derby/code/trunk/java:
engine/org/apache/derby/iapi/sql/dictionary/
engine/org/apache/derby/impl/sql/catalog/
engine/org/apache/derby/impl/sql/compile/
engine/org/apache/derby/impl/sql/execute/ storeless/org/apache/derby/im...
Author: bpendleton
Date: Sun Feb 4 20:58:52 2007
New Revision: 503550
URL: http://svn.apache.org/viewvc?view=rev&rev=503550
Log:
DERBY-1909: ALTER TABLE DROP COLUMN needs to update GRANTed privileges
When ALTER TABLE DROP COLUMN is used to drop a column from a table, it needs to update the GRANTed column privileges on that table.
The core of this proposed patch involves refactoring and reusing the
DERBY-1847 method which knows how to rewrite SYSCOLPERMS rows
to update the COLUMNS column. The DERBY-1847 version of that code
only handled the case of adding a bit to the COLUMNS column; this patch
extends that method to support removing a bit from the COLUMNS
column as well, then calls the method from the AlterTable execution logic.
Removed:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertableDropColumn.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertableDropColumn.sql
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java?view=diff&rev=503550&r1=503549&r2=503550
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java Sun Feb 4 20:58:52 2007
@@ -539,6 +539,25 @@
public void updateSYSCOLPERMSforAddColumnToUserTable(UUID tableID, TransactionController tc)
throws StandardException;
+ /**
+ * Update SYSCOLPERMS to reflect the dropping of a column from a table.
+ *
+ * This method rewrites SYSCOLPERMS rows to update the COLUMNS bitmap
+ * to reflect the removal of a column from a table.
+ *
+ * Currently, this code gets called during execution phase of
+ * ALTER TABLE .. DROP COLUMN ..
+ *
+ * @param tableID The UUID of the table whose column has been dropped
+ * @param tc TransactionController for the transaction
+ * @param columnDescriptor Info about the dropped column
+ *
+ * @exception StandardException Thrown on error
+ */
+ public void updateSYSCOLPERMSforDropColumn(UUID tableID,
+ TransactionController tc, ColumnDescriptor columnDescriptor)
+ throws StandardException;
+
/**
* Drops all routine permission descriptors for the given routine.
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?view=diff&rev=503550&r1=503549&r2=503550
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Sun Feb 4 20:58:52 2007
@@ -2364,6 +2364,45 @@
public void updateSYSCOLPERMSforAddColumnToUserTable(UUID tableID, TransactionController tc)
throws StandardException
{
+ rewriteSYSCOLPERMSforAlterTable(tableID, tc, null);
+ }
+ /**
+ * Update SYSCOLPERMS due to dropping a column from a table.
+ *
+ * Since ALTER TABLE .. DROP COLUMN .. has removed a column from the
+ * table, we need to shrink COLUMNS by removing the corresponding bit
+ * position, and shifting all the subsequent bits "left" one position.
+ *
+ * @param tableID The UUID of the table from which a col has been dropped
+ * @param tc TransactionController for the transaction
+ * @param columnDescriptor Information about the dropped column
+ *
+ * @exception StandardException Thrown on error
+ */
+ public void updateSYSCOLPERMSforDropColumn(UUID tableID,
+ TransactionController tc, ColumnDescriptor columnDescriptor)
+ throws StandardException
+ {
+ rewriteSYSCOLPERMSforAlterTable(tableID, tc, columnDescriptor);
+ }
+ /**
+ * Workhorse for ALTER TABLE-driven mods to SYSCOLPERMS
+ *
+ * This method finds all the SYSCOLPERMS rows for this table. Then it
+ * iterates through each row, either adding a new column to the end of
+ * the table, or dropping a column from the table, as appropriate. It
+ * updates each SYSCOLPERMS row to store the new COLUMNS value.
+ *
+ * @param tableID The UUID of the table being altered
+ * @param tc TransactionController for the transaction
+ * @param columnDescriptor Dropped column info, or null if adding
+ *
+ * @exception StandardException Thrown on error
+ */
+ private void rewriteSYSCOLPERMSforAlterTable(UUID tableID,
+ TransactionController tc, ColumnDescriptor columnDescriptor)
+ throws StandardException
+ {
// In Derby authorization mode, permission catalogs may not be present
if (!usesSqlAuthorization)
return;
@@ -2395,8 +2434,8 @@
false);
/* Next, using each of the ColPermDescriptor's uuid, get the unique row
- in SYSCOLPERMS and expand the "COLUMNS" column in SYSCOLPERMS to
- accomodate the newly added column to the tableid*/
+ in SYSCOLPERMS and adjust the "COLUMNS" column in SYSCOLPERMS to
+ accomodate the added or dropped column in the tableid*/
ColPermsDescriptor colPermsDescriptor;
ExecRow curRow;
ExecIndexRow uuidKey;
@@ -2411,8 +2450,34 @@
curRow=ti.getRow(tc, uuidKey, rf.COLPERMSID_INDEX_NUM);
FormatableBitSet columns = (FormatableBitSet) curRow.getColumn(
SYSCOLPERMSRowFactory.COLUMNS_COL_NUM).getObject();
- int currentLength = columns.getLength();
- columns.grow(currentLength+1);
+ // See whether this is ADD COLUMN or DROP COLUMN. If ADD, then
+ // add a new bit to the bit set. If DROP, then remove the bit
+ // for the dropped column.
+ if (columnDescriptor == null)
+ {
+ int currentLength = columns.getLength();
+ columns.grow(currentLength+1);
+ }
+ else
+ {
+ FormatableBitSet modifiedColumns=new FormatableBitSet(columns);
+ modifiedColumns.shrink(columns.getLength()-1);
+ // All the bits from 0 ... colPosition-2 are OK. The bits from
+ // colPosition to the end need to be shifted 1 to the left.
+ // The bit for colPosition-1 simply disappears from COLUMNS.
+ // ColumnPosition values count from 1, while bits in the
+ // FormatableBitSet count from 0.
+ for (int i = columnDescriptor.getPosition()-1;
+ i < modifiedColumns.getLength();
+ i++)
+ {
+ if (columns.isSet(i+1))
+ modifiedColumns.set(i);
+ else
+ modifiedColumns.clear(i);
+ }
+ columns = modifiedColumns;
+ }
curRow.setColumn(SYSCOLPERMSRowFactory.COLUMNS_COL_NUM,
dvf.getDataValue((Object) columns));
ti.updateRow(uuidKey, curRow,
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?view=diff&rev=503550&r1=503549&r2=503550
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Sun Feb 4 20:58:52 2007
@@ -12005,10 +12005,6 @@
[ <COLUMN> ] columnName = identifier(Limits.MAX_IDENTIFIER_LENGTH, true)
dropColumnReferentialAction(behavior)
{
- if( getLanguageConnectionContext().usesSqlAuthorization())
- throw StandardException.newException(
- SQLState.NOT_IMPLEMENTED,
- "ALTER TABLE DROP COLUMN (sqlAuthorization=true)");
return (TableElementNode) nodeFactory.getNode(
C_NodeTypes.DROP_COLUMN_NODE,
columnName, null,
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?view=diff&rev=503550&r1=503549&r2=503550
==============================================================================
--- 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 Sun Feb 4 20:58:52 2007
@@ -673,17 +673,6 @@
* fixup of schema objects (such as triggers and column
* privileges) which refer to columns by column position number.
*
- * Currently, column privileges are not repaired when
- * dropping a column. This is bug DERBY-1909, and for the
- * time being we simply reject DROP COLUMN if it is specified
- * when sqlAuthorization is true (that check occurs in the
- * parser, not here). When DERBY-1909 is fixed:
- * - Update this comment
- * - Remove the check in dropColumnDefinition() in the parser
- * - consolidate all the tests in altertableDropColumn.sql
- * back into altertable.sql and remove the separate
- * altertableDropColumn files
- *
* Indexes are a bit interesting. The official SQL spec
* doesn't talk about indexes; they are considered to be
* an imlementation-specific performance optimization.
@@ -957,6 +946,9 @@
true, tc);
}
}
+ // Adjust the column permissions rows in SYSCOLPERMS to reflect the
+ // changed column positions due to the dropped column:
+ dd.updateSYSCOLPERMSforDropColumn(td.getUUID(), tc, columnDescriptor);
}
private void modifyColumnType(Activation activation,
Modified: db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java?view=diff&rev=503550&r1=503549&r2=503550
==============================================================================
--- db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java (original)
+++ db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java Sun Feb 4 20:58:52 2007
@@ -225,6 +225,12 @@
}
+ public void updateSYSCOLPERMSforDropColumn(UUID tableID,
+ TransactionController tc, ColumnDescriptor columnDescriptor)
+ throws StandardException
+ {
+ }
+
public void dropAllRoutinePermDescriptors(UUID routineID,
TransactionController tc) throws StandardException {
// TODO Auto-generated method stub
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out?view=diff&rev=503550&r1=503549&r2=503550
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out Sun Feb 4 20:58:52 2007
@@ -974,13 +974,6 @@
-1 |b
3 |three
42 |forty two
-ij> -- Demonstrate that ALTER TABLE DROP COLUMN doesnt work in sqlAuthorization
--- mode. This is because of bug DERBY-1909, which involves how to fix up
--- the GRANTed column permissions following a DROP COLUMN.
-create table atdc_1 (a integer, b integer);
-0 rows inserted/updated/deleted
-ij> alter table atdc_1 drop column b;
-ERROR 0A000: Feature not implemented: ALTER TABLE DROP COLUMN (sqlAuthorization=true).
ij> -- Tests for renaming a column. These tests are in altertable.sql because
-- renaming a column is closely linked, conseptually, to other table
-- alterations. However, the actual syntax is:
@@ -1219,4 +1212,434 @@
A |INTEGER |0 |10 |10 |NULL |NULL |YES
B2 |INTEGER |0 |10 |10 |NULL |NULL |YES
C |INTEGER |0 |10 |10 |NULL |NULL |YES
+ij> -- alter table tests for ALTER TABLE DROP COLUMN.
+-- The overall syntax is:
+-- ALTER TABLE tablename DROP [ COLUMN ] columnname [ CASCADE | RESTRICT ]
+--
+create table atdc_0 (a integer);
+0 rows inserted/updated/deleted
+ij> create table atdc_1 (a integer, b integer);
+0 rows inserted/updated/deleted
+ij> insert into atdc_1 values (1, 1);
+1 row inserted/updated/deleted
+ij> select * from atdc_1;
+A |B
+-----------------------
+1 |1
+ij> select columnname,columnnumber,columndatatype
+ from sys.syscolumns where referenceid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE
+------------------------------------------------------------------------------------------------------------------------------------------------------------
+A |1 |INTEGER
+B |2 |INTEGER
+ij> alter table atdc_1 drop column b;
+0 rows inserted/updated/deleted
+ij> select * from atdc_1;
+A
+-----------
+1
+ij> select columnname,columnnumber,columndatatype
+ from sys.syscolumns where referenceid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE
+------------------------------------------------------------------------------------------------------------------------------------------------------------
+A |1 |INTEGER
+ij> alter table atdc_1 add column b varchar (20);
+0 rows inserted/updated/deleted
+ij> insert into atdc_1 values (1, 'new val');
+1 row inserted/updated/deleted
+ij> insert into atdc_1 (a, b) values (2, 'two val');
+1 row inserted/updated/deleted
+ij> select * from atdc_1;
+A |B
+--------------------------------
+1 |NULL
+1 |new val
+2 |two val
+ij> select columnname,columnnumber,columndatatype
+ from sys.syscolumns where referenceid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE
+------------------------------------------------------------------------------------------------------------------------------------------------------------
+A |1 |INTEGER
+B |2 |VARCHAR(20)
+ij> alter table atdc_1 add column c integer;
+0 rows inserted/updated/deleted
+ij> insert into atdc_1 values (3, null, 3);
+1 row inserted/updated/deleted
+ij> select * from atdc_1;
+A |B |C
+--------------------------------------------
+1 |NULL |NULL
+1 |new val |NULL
+2 |two val |NULL
+3 |NULL |3
+ij> alter table atdc_1 drop b;
+0 rows inserted/updated/deleted
+ij> select * from atdc_1;
+A |C
+-----------------------
+1 |NULL
+1 |NULL
+2 |NULL
+3 |3
+ij> select columnname,columnnumber,columndatatype
+ from sys.syscolumns where referenceid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE
+------------------------------------------------------------------------------------------------------------------------------------------------------------
+A |1 |INTEGER
+C |2 |INTEGER
+ij> -- Demonstrate that we can drop a column which is the primary key. Also
+-- demonstrate that when we drop a column which is the primary key, that
+-- cascade processing will drop the corresponding foreign key constraint
+create table atdc_1_01 (a int, b int, c int not null primary key);
+0 rows inserted/updated/deleted
+ij> alter table atdc_1_01 drop column c cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_1_01 has been dropped.
+ij> create table atdc_1_02 (a int not null primary key, b int);
+0 rows inserted/updated/deleted
+ij> create table atdc_1_03 (a03 int,
+ constraint a03_fk foreign key (a03) references atdc_1_02(a));
+0 rows inserted/updated/deleted
+ij> alter table atdc_1_02 drop column a cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_1_02 has been dropped.
+WARNING 01500: The constraint A03_FK on table ATDC_1_03 has been dropped.
+ij> -- drop column restrict should fail because column is used in a constraint:
+alter table atdc_1 add constraint atdc_constraint_1 check (a > 0);
+0 rows inserted/updated/deleted
+ij> select * from sys.sysconstraints where tableid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+CONSTRAINTID |TABLEID |CONSTRAINTNAME |&|SCHEMAID |&|REFERENCEC&
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|ATDC_CONSTRAINT_1 |C|xxxxFILTERED-UUIDxxxx|E|0
+ij> select sc.* from sys.syschecks sc,sys.sysconstraints con, sys.systables st
+ where sc.constraintid = con.constraintid and con.tableid = st.tableid
+ and st.tablename = 'ATDC_1';
+CONSTRAINTID |CHECKDEFINITION |REFERENCEDCOLU&
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|(a > 0) |(1)
+ij> alter table atdc_1 drop column a restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'A' because CONSTRAINT 'ATDC_CONSTRAINT_1' is dependent on that object.
+ij> -- drop column cascade should also drop the check constraint:
+alter table atdc_1 drop column a cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint ATDC_CONSTRAINT_1 on table ATDC_1 has been dropped.
+ij> select * from sys.sysconstraints where tableid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+CONSTRAINTID |TABLEID |CONSTRAINTNAME |&|SCHEMAID |&|REFERENCEC&
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ij> -- Verify the behavior of the various constraint types:
+-- check, primary key, foreign key, unique, not null
+create table atdc_1_constraints (a int not null primary key,
+ b int not null,
+ c int constraint atdc_1_c_chk check (c is not null),
+ d int not null unique,
+ e int,
+ f int,
+ constraint atdc_1_e_fk foreign key (e) references atdc_1_constraints(a));
+0 rows inserted/updated/deleted
+ij> -- In restrict mode, none of the columns a, c, d, or e should be droppable,
+-- but in cascade mode each of them should be droppable, and at the end
+-- we should have only column f
+-- column b is droppable because an unnamed NOT NULL constraint doesn't
+-- prevent DROP COLUMN, only an explicit CHECK constraint does.
+describe atdc_1_constraints;
+COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
+------------------------------------------------------------------------------
+A |INTEGER |0 |10 |10 |NULL |NULL |NO
+B |INTEGER |0 |10 |10 |NULL |NULL |NO
+C |INTEGER |0 |10 |10 |NULL |NULL |YES
+D |INTEGER |0 |10 |10 |NULL |NULL |NO
+E |INTEGER |0 |10 |10 |NULL |NULL |YES
+F |INTEGER |0 |10 |10 |NULL |NULL |YES
+ij> alter table atdc_1_constraints drop column a restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'A' because CONSTRAINT 'xxxxGENERATED-IDxxxx' is dependent on that object.
+ij> alter table atdc_1_constraints drop column b restrict;
+0 rows inserted/updated/deleted
+ij> alter table atdc_1_constraints drop column c restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'C' because CONSTRAINT 'ATDC_1_C_CHK' is dependent on that object.
+ij> alter table atdc_1_constraints drop column d restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'D' because CONSTRAINT 'xxxxGENERATED-IDxxxx' is dependent on that object.
+ij> alter table atdc_1_constraints drop column e restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'E' because CONSTRAINT 'ATDC_1_E_FK' is dependent on that object.
+ij> describe atdc_1_constraints;
+COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
+------------------------------------------------------------------------------
+A |INTEGER |0 |10 |10 |NULL |NULL |NO
+C |INTEGER |0 |10 |10 |NULL |NULL |YES
+D |INTEGER |0 |10 |10 |NULL |NULL |NO
+E |INTEGER |0 |10 |10 |NULL |NULL |YES
+F |INTEGER |0 |10 |10 |NULL |NULL |YES
+ij> alter table atdc_1_constraints drop column a cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_1_CONSTRAINTS has been dropped.
+WARNING 01500: The constraint ATDC_1_E_FK on table ATDC_1_CONSTRAINTS has been dropped.
+ij> alter table atdc_1_constraints drop column c cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint ATDC_1_C_CHK on table ATDC_1_CONSTRAINTS has been dropped.
+ij> alter table atdc_1_constraints drop column d cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_1_CONSTRAINTS has been dropped.
+ij> alter table atdc_1_constraints drop column e cascade;
+0 rows inserted/updated/deleted
+ij> describe atdc_1_constraints;
+COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
+------------------------------------------------------------------------------
+F |INTEGER |0 |10 |10 |NULL |NULL |YES
+ij> -- Some negative testing of ALTER TABLE DROP COLUMN
+-- Table does not exist:
+alter table atdc_nosuch drop column a;
+ERROR 42Y55: 'ALTER TABLE' cannot be performed on 'ATDC_NOSUCH' because it does not exist.
+ij> -- Table exists, but column does not exist:
+create table atdc_2 (a integer);
+0 rows inserted/updated/deleted
+ij> alter table atdc_2 drop column b;
+ERROR 42X14: 'B' is not a column in table or VTI 'ATDC_2'.
+ij> alter table atdc_2 drop b;
+ERROR 42X14: 'B' is not a column in table or VTI 'ATDC_2'.
+ij> -- Column name is spelled incorrectly (wrong case)
+alter table atdc_2 drop column 'a';
+ERROR 42X01: Syntax error: Encountered "\'a\'" at line 2, column 32.
+ij> -- Some special reserved words to cause parser errors
+alter table atdc_2 drop column column;
+ERROR 42X01: Syntax error: Encountered "column" at line 2, column 32.
+ij> alter table atdc_2 drop column;
+ERROR 42X01: Syntax error: Encountered "<EOF>" at line 1, column 30.
+ij> alter table atdc_2 drop column constraint;
+ERROR 42X01: Syntax error: Encountered "constraint" at line 1, column 32.
+ij> alter table atdc_2 drop column primary;
+ERROR 42X01: Syntax error: Encountered "primary" at line 1, column 32.
+ij> alter table atdc_2 drop column foreign;
+ERROR 42X01: Syntax error: Encountered "foreign" at line 1, column 32.
+ij> alter table atdc_2 drop column check;
+ERROR 42X01: Syntax error: Encountered "check" at line 1, column 32.
+ij> create table atdc_3 (a integer);
+0 rows inserted/updated/deleted
+ij> create index atdc_3_idx_1 on atdc_3 (a);
+0 rows inserted/updated/deleted
+ij> -- This fails because a is the only column in the table.
+alter table atdc_3 drop column a restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'THE *LAST* COLUMN A' because TABLE 'RENC_SCHEMA_1.ATDC_3' is dependent on that object.
+ij> drop index atdc_3_idx_1;
+0 rows inserted/updated/deleted
+ij> -- cascade/restrict processing doesn't currently consider indexes.
+-- The column being dropped is automatically dropped from all indexes
+-- as well. If that was the only (last) column in the index, then the
+-- index is dropped, too.
+create table atdc_4 (a int, b int, c int, d int, e int);
+0 rows inserted/updated/deleted
+ij> insert into atdc_4 values (1,2,3,4,5);
+1 row inserted/updated/deleted
+ij> create index atdc_4_idx_1 on atdc_4 (a);
+0 rows inserted/updated/deleted
+ij> create index atdc_4_idx_2 on atdc_4 (b, c, d);
+0 rows inserted/updated/deleted
+ij> create index atdc_4_idx_3 on atdc_4 (c, a);
+0 rows inserted/updated/deleted
+ij> select conglomeratename,isindex from sys.sysconglomerates where tableid in
+ (select tableid from sys.systables where tablename = 'ATDC_4');
+CONGLOMERATENAME |ISIN&
+--------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx |false
+ATDC_4_IDX_1 |true
+ATDC_4_IDX_2 |true
+ATDC_4_IDX_3 |true
+ij> show indexes from atdc_4;
+TABLE_NAME |COLUMN_NAME |NON_U&|TYPE|ASC&|CARDINA&|PAGES
+----------------------------------------------------------------------------
+ATDC_4 |A |true |3 |A |NULL |NULL
+ATDC_4 |B |true |3 |A |NULL |NULL
+ATDC_4 |C |true |3 |A |NULL |NULL
+ATDC_4 |D |true |3 |A |NULL |NULL
+ATDC_4 |C |true |3 |A |NULL |NULL
+ATDC_4 |A |true |3 |A |NULL |NULL
+ij> -- This succeeds, because cascade/restrict doesn't matter for indexes. The
+-- effect of dropping column a is that:
+-- index atdc_4_idx_1 is entirely dropped
+-- index atdc_4_idx_2 is left alone but the column positions are fixed up
+-- index atdc_4_idx_3 is modified to refer only to column c
+alter table atdc_4 drop column a restrict;
+0 rows inserted/updated/deleted
+ij> select conglomeratename,isindex from sys.sysconglomerates where tableid in
+ (select tableid from sys.systables where tablename = 'ATDC_4');
+CONGLOMERATENAME |ISIN&
+--------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx |false
+ATDC_4_IDX_2 |true
+ATDC_4_IDX_3 |true
+ij> show indexes from atdc_4;
+TABLE_NAME |COLUMN_NAME |NON_U&|TYPE|ASC&|CARDINA&|PAGES
+----------------------------------------------------------------------------
+ATDC_4 |B |true |3 |A |NULL |NULL
+ATDC_4 |C |true |3 |A |NULL |NULL
+ATDC_4 |D |true |3 |A |NULL |NULL
+ATDC_4 |C |true |3 |A |NULL |NULL
+ij> describe atdc_4;
+COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
+------------------------------------------------------------------------------
+B |INTEGER |0 |10 |10 |NULL |NULL |YES
+C |INTEGER |0 |10 |10 |NULL |NULL |YES
+D |INTEGER |0 |10 |10 |NULL |NULL |YES
+E |INTEGER |0 |10 |10 |NULL |NULL |YES
+ij> -- The effect of dropping column c is that:
+-- index atdc_4_idx_2 is modified to refer to columns b and d
+-- index atdc_4_idx_3 is entirely dropped
+alter table atdc_4 drop column c restrict;
+0 rows inserted/updated/deleted
+ij> show indexes from atdc_4;
+TABLE_NAME |COLUMN_NAME |NON_U&|TYPE|ASC&|CARDINA&|PAGES
+----------------------------------------------------------------------------
+ATDC_4 |B |true |3 |A |NULL |NULL
+ATDC_4 |D |true |3 |A |NULL |NULL
+ij> select * from atdc_4 where c = 3;
+ERROR 42X04: Column 'C' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'C' is not a column in the target table.
+ij> select count(*) from sys.sysconglomerates where conglomeratename='ATDC_4_IDX_2';
+1
+-----------
+1
+ij> select conglomeratename, isindex from sys.sysconglomerates
+ where conglomeratename like 'ATDC_4%';
+CONGLOMERATENAME |ISIN&
+--------------------------------------------------------------------------------------------------------------------------------------
+ATDC_4_IDX_2 |true
+ij> drop index atdc_4_idx_2;
+0 rows inserted/updated/deleted
+ij> -- drop column restrict should fail becuase column is used in a view:
+create table atdc_5 (a int, b int);
+0 rows inserted/updated/deleted
+ij> create view atdc_vw_1 (vw_b) as select b from atdc_5;
+0 rows inserted/updated/deleted
+ij> alter table atdc_5 drop column b restrict;
+ERROR X0Y23: Operation 'DROP COLUMN RESTRICT' cannot be performed on object 'ATDC_5(B)' because VIEW 'ATDC_VW_1' is dependent on that object.
+ij> select * from atdc_vw_1;
+VW_B
+-----------
+ij> -- drop column cascade drops the column, and also drops the dependent view:
+alter table atdc_5 drop column b cascade;
+0 rows inserted/updated/deleted
+WARNING 01501: The view ATDC_VW_1 has been dropped.
+ij> select * from atdc_vw_1;
+ERROR 42X05: Table/View 'ATDC_VW_1' does not exist.
+ij> -- cascade processing should transitively drop a view dependent on a view
+-- dependent in turn on the column being dropped:
+create table atdc_5a (a int, b int, c int);
+0 rows inserted/updated/deleted
+ij> create view atdc_vw_5a_1 (vw_5a_b, vw_5a_c) as select b,c from atdc_5a;
+0 rows inserted/updated/deleted
+ij> create view atdc_vw_5a_2 (vw_5a_c_2) as select vw_5a_c from atdc_vw_5a_1;
+0 rows inserted/updated/deleted
+ij> alter table atdc_5a drop column b cascade;
+0 rows inserted/updated/deleted
+WARNING 01501: The view ATDC_VW_5A_2 has been dropped.
+WARNING 01501: The view ATDC_VW_5A_1 has been dropped.
+ij> select * from atdc_vw_5a_1;
+ERROR 42X05: Table/View 'ATDC_VW_5A_1' does not exist.
+ij> select * from atdc_vw_5a_2;
+ERROR 42X05: Table/View 'ATDC_VW_5A_2' does not exist.
+ij> -- drop column restrict should fail because column is used in a trigger:
+create table atdc_6 (a integer, b integer);
+0 rows inserted/updated/deleted
+ij> create trigger atdc_6_trigger_1 after update of b on atdc_6
+ for each row values current_date;
+0 rows inserted/updated/deleted
+ij> alter table atdc_6 drop column b restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'B' because TRIGGER 'ATDC_6_TRIGGER_1' is dependent on that object.
+ij> select triggername from sys.systriggers where triggername='ATDC_6_TRIGGER_1';
+TRIGGERNAME
+--------------------------------------------------------------------------------------------------------------------------------
+ATDC_6_TRIGGER_1
+ij> alter table atdc_6 drop column b cascade;
+0 rows inserted/updated/deleted
+WARNING 01502: The trigger ATDC_6_TRIGGER_1 on table ATDC_6 has been dropped.
+ij> select triggername from sys.systriggers where triggername='ATDC_6_TRIGGER_1';
+TRIGGERNAME
+--------------------------------------------------------------------------------------------------------------------------------
+ij> create table atdc_7 (a int, b int, c int, primary key (a));
+0 rows inserted/updated/deleted
+ij> alter table atdc_7 drop column a restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'A' because CONSTRAINT 'xxxxGENERATED-IDxxxx' is dependent on that object.
+ij> alter table atdc_7 drop column a cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_7 has been dropped.
+ij> create table atdc_8 (a int, b int, c int, primary key (b, c));
+0 rows inserted/updated/deleted
+ij> alter table atdc_8 drop column c restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'C' because CONSTRAINT 'xxxxGENERATED-IDxxxx' is dependent on that object.
+ij> alter table atdc_8 drop column c cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_8 has been dropped.
+ij> create table atdc_9 (a int not null, b int);
+0 rows inserted/updated/deleted
+ij> alter table atdc_9 drop column a restrict;
+0 rows inserted/updated/deleted
+ij> -- Verify that a GRANTED privilege fails a drop column in RESTRICT mode,
+-- and verify that the privilege is dropped in CASCADE mode:
+create table atdc_10 (a int, b int, c int);
+0 rows inserted/updated/deleted
+ij> grant select(a, b, c) on atdc_10 to bryan;
+0 rows inserted/updated/deleted
+ij> select * from sys.syscolperms;
+COLPERMSID |GRANTEE |GRANTOR |TABLEID |&|COLUMNS
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|BRYAN |APP |xxxxFILTERED-UUIDxxxx|s|{0, 1}
+xxxxFILTERED-UUIDxxxx|BRYAN |APP |xxxxFILTERED-UUIDxxxx|s|{0, 1, 2}
+ij> alter table atdc_10 drop column b restrict;
+0 rows inserted/updated/deleted
+ij> select * from sys.syscolperms;
+COLPERMSID |GRANTEE |GRANTOR |TABLEID |&|COLUMNS
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|BRYAN |APP |xxxxFILTERED-UUIDxxxx|s|{0, 1}
+xxxxFILTERED-UUIDxxxx|BRYAN |APP |xxxxFILTERED-UUIDxxxx|s|{0, 1}
+ij> alter table atdc_10 drop column b cascade;
+ERROR 42X14: 'B' is not a column in table or VTI 'ATDC_10'.
+ij> select * from sys.syscolperms;
+COLPERMSID |GRANTEE |GRANTOR |TABLEID |&|COLUMNS
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|BRYAN |APP |xxxxFILTERED-UUIDxxxx|s|{0, 1}
+xxxxFILTERED-UUIDxxxx|BRYAN |APP |xxxxFILTERED-UUIDxxxx|s|{0, 1}
+ij> -- Include the test from the DERBY-1909 report:
+drop table d1909;
+ERROR 42Y55: 'DROP TABLE' cannot be performed on 'D1909' because it does not exist.
+ij> create table d1909 (a int, b int, c int);
+0 rows inserted/updated/deleted
+ij> grant select (a) on d1909 to user1;
+0 rows inserted/updated/deleted
+ij> grant select (a,b) on d1909 to user2;
+0 rows inserted/updated/deleted
+ij> grant update(c) on d1909 to super_user;
+0 rows inserted/updated/deleted
+ij> select c.grantee, c.type, c.columns from sys.syscolperms c, sys.systables t
+ where c.tableid = t.tableid and t.tablename='D1909';
+GRANTEE |&|COLUMNS
+--------------------------------------------------------------------------------------------------------------------------------------------------
+USER1 |s|{0}
+USER2 |s|{0, 1}
+SUPER_USER |u|{2}
+ij> alter table d1909 drop column a;
+0 rows inserted/updated/deleted
+ij> select c.grantee, c.type, c.columns from sys.syscolperms c, sys.systables t
+ where c.tableid = t.tableid and t.tablename='D1909';
+GRANTEE |&|COLUMNS
+--------------------------------------------------------------------------------------------------------------------------------------------------
+USER1 |s|{}
+USER2 |s|{0}
+SUPER_USER |u|{1}
+ij> grant update(b) on d1909 to user1;
+0 rows inserted/updated/deleted
+ij> grant select(c) on d1909 to user1;
+0 rows inserted/updated/deleted
+ij> grant select(c) on d1909 to user2;
+0 rows inserted/updated/deleted
+ij> select c.grantee, c.type, c.columns from sys.syscolperms c, sys.systables t
+ where c.tableid = t.tableid and t.tablename='D1909';
+GRANTEE |&|COLUMNS
+--------------------------------------------------------------------------------------------------------------------------------------------------
+USER1 |s|{1}
+USER2 |s|{0, 1}
+SUPER_USER |u|{1}
+USER1 |u|{0}
ij>
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql?view=diff&rev=503550&r1=503549&r2=503550
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql Sun Feb 4 20:58:52 2007
@@ -611,12 +611,6 @@
select * from atmod_1;
--- Demonstrate that ALTER TABLE DROP COLUMN doesnt work in sqlAuthorization
--- mode. This is because of bug DERBY-1909, which involves how to fix up
--- the GRANTed column permissions following a DROP COLUMN.
-create table atdc_1 (a integer, b integer);
-alter table atdc_1 drop column b;
-
-- Tests for renaming a column. These tests are in altertable.sql because
-- renaming a column is closely linked, conseptually, to other table
-- alterations. However, the actual syntax is:
@@ -744,4 +738,194 @@
-- But this should work, and should find the table in the other schema
rename column renc_schema_2.renc_8.b to b2;
describe renc_schema_2.renc_8;
+
+-- alter table tests for ALTER TABLE DROP COLUMN.
+
+-- The overall syntax is:
+-- ALTER TABLE tablename DROP [ COLUMN ] columnname [ CASCADE | RESTRICT ]
+--
+create table atdc_0 (a integer);
+create table atdc_1 (a integer, b integer);
+insert into atdc_1 values (1, 1);
+select * from atdc_1;
+select columnname,columnnumber,columndatatype
+ from sys.syscolumns where referenceid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+alter table atdc_1 drop column b;
+select * from atdc_1;
+select columnname,columnnumber,columndatatype
+ from sys.syscolumns where referenceid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+alter table atdc_1 add column b varchar (20);
+insert into atdc_1 values (1, 'new val');
+insert into atdc_1 (a, b) values (2, 'two val');
+select * from atdc_1;
+select columnname,columnnumber,columndatatype
+ from sys.syscolumns where referenceid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+alter table atdc_1 add column c integer;
+insert into atdc_1 values (3, null, 3);
+select * from atdc_1;
+alter table atdc_1 drop b;
+select * from atdc_1;
+select columnname,columnnumber,columndatatype
+ from sys.syscolumns where referenceid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+-- Demonstrate that we can drop a column which is the primary key. Also
+-- demonstrate that when we drop a column which is the primary key, that
+-- cascade processing will drop the corresponding foreign key constraint
+create table atdc_1_01 (a int, b int, c int not null primary key);
+alter table atdc_1_01 drop column c cascade;
+create table atdc_1_02 (a int not null primary key, b int);
+create table atdc_1_03 (a03 int,
+ constraint a03_fk foreign key (a03) references atdc_1_02(a));
+alter table atdc_1_02 drop column a cascade;
+-- drop column restrict should fail because column is used in a constraint:
+alter table atdc_1 add constraint atdc_constraint_1 check (a > 0);
+select * from sys.sysconstraints where tableid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+select sc.* from sys.syschecks sc,sys.sysconstraints con, sys.systables st
+ where sc.constraintid = con.constraintid and con.tableid = st.tableid
+ and st.tablename = 'ATDC_1';
+alter table atdc_1 drop column a restrict;
+-- drop column cascade should also drop the check constraint:
+alter table atdc_1 drop column a cascade;
+select * from sys.sysconstraints where tableid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+-- Verify the behavior of the various constraint types:
+-- check, primary key, foreign key, unique, not null
+create table atdc_1_constraints (a int not null primary key,
+ b int not null,
+ c int constraint atdc_1_c_chk check (c is not null),
+ d int not null unique,
+ e int,
+ f int,
+ constraint atdc_1_e_fk foreign key (e) references atdc_1_constraints(a));
+-- In restrict mode, none of the columns a, c, d, or e should be droppable,
+-- but in cascade mode each of them should be droppable, and at the end
+-- we should have only column f
+-- column b is droppable because an unnamed NOT NULL constraint doesn't
+-- prevent DROP COLUMN, only an explicit CHECK constraint does.
+describe atdc_1_constraints;
+alter table atdc_1_constraints drop column a restrict;
+alter table atdc_1_constraints drop column b restrict;
+alter table atdc_1_constraints drop column c restrict;
+alter table atdc_1_constraints drop column d restrict;
+alter table atdc_1_constraints drop column e restrict;
+describe atdc_1_constraints;
+alter table atdc_1_constraints drop column a cascade;
+alter table atdc_1_constraints drop column c cascade;
+alter table atdc_1_constraints drop column d cascade;
+alter table atdc_1_constraints drop column e cascade;
+describe atdc_1_constraints;
+-- Some negative testing of ALTER TABLE DROP COLUMN
+-- Table does not exist:
+alter table atdc_nosuch drop column a;
+-- Table exists, but column does not exist:
+create table atdc_2 (a integer);
+alter table atdc_2 drop column b;
+alter table atdc_2 drop b;
+-- Column name is spelled incorrectly (wrong case)
+alter table atdc_2 drop column 'a';
+-- Some special reserved words to cause parser errors
+alter table atdc_2 drop column column;
+alter table atdc_2 drop column;
+alter table atdc_2 drop column constraint;
+alter table atdc_2 drop column primary;
+alter table atdc_2 drop column foreign;
+alter table atdc_2 drop column check;
+create table atdc_3 (a integer);
+create index atdc_3_idx_1 on atdc_3 (a);
+-- This fails because a is the only column in the table.
+alter table atdc_3 drop column a restrict;
+drop index atdc_3_idx_1;
+-- cascade/restrict processing doesn't currently consider indexes.
+-- The column being dropped is automatically dropped from all indexes
+-- as well. If that was the only (last) column in the index, then the
+-- index is dropped, too.
+create table atdc_4 (a int, b int, c int, d int, e int);
+insert into atdc_4 values (1,2,3,4,5);
+create index atdc_4_idx_1 on atdc_4 (a);
+create index atdc_4_idx_2 on atdc_4 (b, c, d);
+create index atdc_4_idx_3 on atdc_4 (c, a);
+select conglomeratename,isindex from sys.sysconglomerates where tableid in
+ (select tableid from sys.systables where tablename = 'ATDC_4');
+show indexes from atdc_4;
+-- This succeeds, because cascade/restrict doesn't matter for indexes. The
+-- effect of dropping column a is that:
+-- index atdc_4_idx_1 is entirely dropped
+-- index atdc_4_idx_2 is left alone but the column positions are fixed up
+-- index atdc_4_idx_3 is modified to refer only to column c
+alter table atdc_4 drop column a restrict;
+select conglomeratename,isindex from sys.sysconglomerates where tableid in
+ (select tableid from sys.systables where tablename = 'ATDC_4');
+show indexes from atdc_4;
+describe atdc_4;
+-- The effect of dropping column c is that:
+-- index atdc_4_idx_2 is modified to refer to columns b and d
+-- index atdc_4_idx_3 is entirely dropped
+alter table atdc_4 drop column c restrict;
+show indexes from atdc_4;
+select * from atdc_4 where c = 3;
+select count(*) from sys.sysconglomerates where conglomeratename='ATDC_4_IDX_2';
+select conglomeratename, isindex from sys.sysconglomerates
+ where conglomeratename like 'ATDC_4%';
+drop index atdc_4_idx_2;
+-- drop column restrict should fail becuase column is used in a view:
+create table atdc_5 (a int, b int);
+create view atdc_vw_1 (vw_b) as select b from atdc_5;
+alter table atdc_5 drop column b restrict;
+select * from atdc_vw_1;
+-- drop column cascade drops the column, and also drops the dependent view:
+alter table atdc_5 drop column b cascade;
+select * from atdc_vw_1;
+-- cascade processing should transitively drop a view dependent on a view
+-- dependent in turn on the column being dropped:
+create table atdc_5a (a int, b int, c int);
+create view atdc_vw_5a_1 (vw_5a_b, vw_5a_c) as select b,c from atdc_5a;
+create view atdc_vw_5a_2 (vw_5a_c_2) as select vw_5a_c from atdc_vw_5a_1;
+alter table atdc_5a drop column b cascade;
+select * from atdc_vw_5a_1;
+select * from atdc_vw_5a_2;
+-- drop column restrict should fail because column is used in a trigger:
+create table atdc_6 (a integer, b integer);
+create trigger atdc_6_trigger_1 after update of b on atdc_6
+ for each row values current_date;
+alter table atdc_6 drop column b restrict;
+select triggername from sys.systriggers where triggername='ATDC_6_TRIGGER_1';
+alter table atdc_6 drop column b cascade;
+select triggername from sys.systriggers where triggername='ATDC_6_TRIGGER_1';
+create table atdc_7 (a int, b int, c int, primary key (a));
+alter table atdc_7 drop column a restrict;
+alter table atdc_7 drop column a cascade;
+create table atdc_8 (a int, b int, c int, primary key (b, c));
+alter table atdc_8 drop column c restrict;
+alter table atdc_8 drop column c cascade;
+create table atdc_9 (a int not null, b int);
+alter table atdc_9 drop column a restrict;
+-- Verify that a GRANTED privilege fails a drop column in RESTRICT mode,
+-- and verify that the privilege is dropped in CASCADE mode:
+create table atdc_10 (a int, b int, c int);
+grant select(a, b, c) on atdc_10 to bryan;
+select * from sys.syscolperms;
+alter table atdc_10 drop column b restrict;
+select * from sys.syscolperms;
+alter table atdc_10 drop column b cascade;
+select * from sys.syscolperms;
+-- Include the test from the DERBY-1909 report:
+drop table d1909;
+create table d1909 (a int, b int, c int);
+grant select (a) on d1909 to user1;
+grant select (a,b) on d1909 to user2;
+grant update(c) on d1909 to super_user;
+select c.grantee, c.type, c.columns from sys.syscolperms c, sys.systables t
+ where c.tableid = t.tableid and t.tablename='D1909';
+alter table d1909 drop column a;
+select c.grantee, c.type, c.columns from sys.syscolperms c, sys.systables t
+ where c.tableid = t.tableid and t.tablename='D1909';
+grant update(b) on d1909 to user1;
+grant select(c) on d1909 to user1;
+grant select(c) on d1909 to user2;
+select c.grantee, c.type, c.columns from sys.syscolperms c, sys.systables t
+ where c.tableid = t.tableid and t.tablename='D1909';