[jira] Commented: (DERBY-4681) Dropping a column in the table drops the views that use this table

["Rick Hillegas (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-4681?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12872963#action_12872963 ] 

Rick Hillegas commented on DERBY-4681:
--------------------------------------

Derby drops dependent objects over aggressively. I can verify this behavior as far back as 10.3, the release which introduced DROP COLUMN. To clarify:

1) If you drop a column in a base table with CASCADE semantics (the default), Derby will drop views which mention that table but don't mention the dropped column. The correct behavior is to drop only the views which mention the column.

2) If you drop a column in a base table with RESTRICT semantics, Derby will raise an error if there is a view defined on the table, even if the view does not mention the dropped column. The correct behavior is to raise an error only if there are dependent objects which mention the dropped column.

The correct behavior is defined in the SQL Standard, part 2, section 11.19 (<drop column definition>). The Derby Reference Guide incorrectly says that Derby behaves in the Standard way (see the section on ALTER TABLE).

Here is script output demonstrating the problem:

ij version 10.3
ij> connect 'jdbc:derby:db;create=true';
ij> create table t( a int, b int );
0 rows inserted/updated/deleted
ij> create view v1( a ) as select a from t;
0 rows inserted/updated/deleted
ij> create view v2( b ) as select b from t;
0 rows inserted/updated/deleted
ij> select * from v1;
A          
-----------

0 rows selected
ij> select * from v2;
B          
-----------

0 rows selected
ij> --
-- The default drop semantics are CASCADE. View v1
-- should be dropped, but not view v2.
--
alter table t drop column a;
0 rows inserted/updated/deleted
WARNING 01501: The view V2 has been dropped.
WARNING 01501: The view V1 has been dropped.
ij> select * from v1;
ERROR 42X05: Table/View 'V1' does not exist.
ij> select * from v2;
ERROR 42X05: Table/View 'V2' does not exist.
ij> create table t2( a int, b int );
0 rows inserted/updated/deleted
ij> create view v4( b ) as select b from t2;
0 rows inserted/updated/deleted
ij> --
-- View v4 should not be dropped because it does
-- not mention the deprecated column.
--
alter table t2 drop column a restrict;
ERROR X0Y23: Operation 'DROP COLUMN RESTRICT' cannot be performed on object 'T2(A)' because VIEW 'V4' is dependent on that object.
ij> select * from v4;
B          
-----------

0 rows selected

> Dropping a column in the table drops the views that use this table
> ------------------------------------------------------------------
>
>                 Key: DERBY-4681
>                 URL: https://issues.apache.org/jira/browse/DERBY-4681
>             Project: Derby
>          Issue Type: Bug
>    Affects Versions: 10.6.1.0
>         Environment: Windows 7 Enterprise, JDK SE 1.6 u20
>            Reporter: Grzegorz Łyp
>            Priority: Critical
>
> If i have a table and a view that uses this table, after dropping a column from this table the whole view is also dropped.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.