You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Grzegorz Łyp (JIRA)" <ji...@apache.org> on 2010/05/28 13:15:40 UTC
[jira] Created: (DERBY-4681) Dropping a column in the table drops
the views that use this table
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.
[jira] Commented: (DERBY-4681) Dropping a column in the table drops
the views that use this table
Posted by "Grzegorz Łyp (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4681?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12872972#action_12872972 ]
Grzegorz Łyp commented on DERBY-4681:
-------------------------------------
My problem came out when I wanted to reduce the proecision of a numeric column from decimal(10,4) to decimal(10,2).
Because derby does not allow to alter such columns, I created a column with new data type, copied the data. Next I dropped the original column and created it again, then copied the data from temporary column, and dropped temporary column.
alter table My_Tab add column tmp_col decimal(10,2)
update My_Tab set tmp_col = original_column
alter table My_Tab drop column original_column
alter table My_Tab add column original_column decimal(10,2)
update My_Tab set original_column = tmp_col
alter table My_Tab drop column tmp_col
such operations resulted in droping views dependant od table My_Tab.
> 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.
[jira] Commented: (DERBY-4681) Dropping a column in the table drops
the views that use this table
Posted by "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.
[jira] Updated: (DERBY-4681) Dropping a column in the table drops
the views that use this table
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4681?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-4681:
---------------------------------
Bug behavior facts: [Deviation from standard, Seen in production]
Affects Version/s: 10.5.3.0
10.5.2.0
10.5.1.1
10.4.2.0
10.4.1.3
10.3.3.0
10.3.2.1
10.3.1.4
Issue & fix info: [Repro attached]
Component/s: SQL
> 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
> Components: SQL
> Affects Versions: 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 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.