Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <20048173.49681275052598545.JavaMail.jira@thor>
Date: Fri, 28 May 2010 09:16:38 -0400 (EDT)
From: "Rick Hillegas (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-4681) Dropping a column in the table drops
 the views that use this table
In-Reply-To: <632446.48521275045340657.JavaMail.jira@thor>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


    [ https://issues.apache.org/jira/browse/DERBY-4681?page=3Dcom.atlassian=
.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D1287=
2963#action_12872963 ]=20

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 dro=
pped column. The correct behavior is to drop only the views which mention t=
he 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 d=
oes not mention the dropped column. The correct behavior is to raise an err=
or 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=3Dtrue';
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         =20
-----------

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

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         =20
-----------

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 =C5=81yp
>            Priority: Critical
>
> If i have a table and a view that uses this table, after dropping a colum=
n from this table the whole view is also dropped.

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