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 "Mamta A. Satoor (JIRA)" <ji...@apache.org> on 2011/01/25 23:49:45 UTC
[jira] Created: (DERBY-4984) ALTER TABLE DROP COLUMN may leave
triggers invalid even if they are not using the column getting dropped.
ALTER TABLE DROP COLUMN may leave triggers invalid even if they are not using the column getting dropped.
---------------------------------------------------------------------------------------------------------
Key: DERBY-4984
URL: https://issues.apache.org/jira/browse/DERBY-4984
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.7.1.1, 10.6.2.1, 10.6.1.0, 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
Reporter: Mamta A. Satoor
While doing testing for DERBY-4887, I found a case where ALTER TABLE DROP COLUMN will leave triggers in invalid state even if those triggers are not using the column getting dropped. eg
CREATE TABLE tab (
element_id INTEGER NOT NULL,
altered_id VARCHAR(30) NOT NULL
);
insert into tab values(1,'aa');
-- Create a trigger against the table
CREATE TRIGGER mytrig
AFTER UPDATE OF altered_id ON tab
REFERENCING NEW AS newt OLD AS oldt
FOR EACH ROW MODE DB2SQL
SELECT newt.altered_id from tab;
--Drop the first column in the table. This will cause the column positions to be recalculated within the table
alter table tab drop column element_id;
--mytrig is still looking for column altered_id at position 2 but drop column has changed it's position within the table to 1
update tab set altered_id='bb';
As shown in the example above, table "TAB" only has 2 columns. The trigger "MYTRIG" uses the 2nd column in it's trigger action through the REFERENCING clause. During trigger action sql parsing, every column referenced through REFERENCING clause gets transformed into a reference to the column through it's column position in the trigger table(this change to look for columns based on their column positions rather than the name went in as revision 397959 with following commit comments DERBY-1258 Change the generated code for a new/old column in a row trigger to access columns by position and not name to avoid the case-insensitive name lookup specified by JDBC.) When in the script above, we drop the column in position 1, the trigger "MYTRIG" ends up becoming invalid because column being used in the trigger action is no more in column position 2.
One possible solution is to regenerate the SPSDescriptor associated with the trigger action for all the triggers defined on the table whose column is getting dropped. We could be little smarter and only regenerate the SPSDescriptor for the triggers who use the REFERENCING clause. But we need to do more testing to make sure that triggers without REFERENCING clause do not get impacted by a drop of column which is not the last column of the table. This optimization of recognizing the right triggers may not be worth it since performance may not be that big a criteria for an ALTER TABLE DROP COLUMN which should be a rare operation in a production system.
An interim solution to this problem is obviously to drop and recreate the triggers
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4984) ALTER TABLE DROP COLUMN may leave
triggers invalid even if they are not using the column getting dropped.
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4984?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12995100#comment-12995100 ]
Dag H. Wanvik commented on DERBY-4984:
--------------------------------------
What happens presently? Does the trigger with the wrong code cause a crash down the line, or ?
> ALTER TABLE DROP COLUMN may leave triggers invalid even if they are not using the column getting dropped.
> ---------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4984
> URL: https://issues.apache.org/jira/browse/DERBY-4984
> 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, 10.6.2.1, 10.7.1.1
> Reporter: Mamta A. Satoor
> Labels: derby_triage10_8
>
> While doing testing for DERBY-4887, I found a case where ALTER TABLE DROP COLUMN will leave triggers in invalid state even if those triggers are not using the column getting dropped. eg
> CREATE TABLE tab (
> element_id INTEGER NOT NULL,
> altered_id VARCHAR(30) NOT NULL
> );
> insert into tab values(1,'aa');
> -- Create a trigger against the table
> CREATE TRIGGER mytrig
> AFTER UPDATE OF altered_id ON tab
> REFERENCING NEW AS newt OLD AS oldt
> FOR EACH ROW MODE DB2SQL
> SELECT newt.altered_id from tab;
> --Drop the first column in the table. This will cause the column positions to be recalculated within the table
> alter table tab drop column element_id;
> --mytrig is still looking for column altered_id at position 2 but drop column has changed it's position within the table to 1
> update tab set altered_id='bb';
> As shown in the example above, table "TAB" only has 2 columns. The trigger "MYTRIG" uses the 2nd column in it's trigger action through the REFERENCING clause. During trigger action sql parsing, every column referenced through REFERENCING clause gets transformed into a reference to the column through it's column position in the trigger table(this change to look for columns based on their column positions rather than the name went in as revision 397959 with following commit comments DERBY-1258 Change the generated code for a new/old column in a row trigger to access columns by position and not name to avoid the case-insensitive name lookup specified by JDBC.) When in the script above, we drop the column in position 1, the trigger "MYTRIG" ends up becoming invalid because column being used in the trigger action is no more in column position 2.
> One possible solution is to regenerate the SPSDescriptor associated with the trigger action for all the triggers defined on the table whose column is getting dropped. We could be little smarter and only regenerate the SPSDescriptor for the triggers who use the REFERENCING clause. But we need to do more testing to make sure that triggers without REFERENCING clause do not get impacted by a drop of column which is not the last column of the table. This optimization of recognizing the right triggers may not be worth it since performance may not be that big a criteria for an ALTER TABLE DROP COLUMN which should be a rare operation in a production system.
> An interim solution to this problem is obviously to drop and recreate the triggers
--
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-4984) ALTER TABLE DROP COLUMN may leave
triggers invalid even if they are not using the column getting dropped.
Posted by "Mamta A. Satoor (Commented) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4984?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13232703#comment-13232703 ]
Mamta A. Satoor commented on DERBY-4984:
----------------------------------------
If we do not have a customer request for backporting DERBY-4984, then I would rather us not backport this jira. The primary reason is we did work on several trigger related jiras around the same time and some of those jiras depended on changes to system tables. Those different jiras worked on quite a few common classes and around the same code and hence I am worried that we might backport something which depends on codes which should not be backported.
> ALTER TABLE DROP COLUMN may leave triggers invalid even if they are not using the column getting dropped.
> ---------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4984
> URL: https://issues.apache.org/jira/browse/DERBY-4984
> 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, 10.6.2.1, 10.7.1.1
> Reporter: Mamta A. Satoor
> Assignee: Mamta A. Satoor
> Labels: derby_backport_reject_10_5, derby_backport_reject_10_6, derby_backport_reject_10_7, derby_triage10_8
> Fix For: 10.7.1.4, 10.8.2.2
>
>
> While doing testing for DERBY-4887, I found a case where ALTER TABLE DROP COLUMN will leave triggers in invalid state even if those triggers are not using the column getting dropped. eg
> CREATE TABLE tab (
> element_id INTEGER NOT NULL,
> altered_id VARCHAR(30) NOT NULL
> );
> insert into tab values(1,'aa');
> -- Create a trigger against the table
> CREATE TRIGGER mytrig
> AFTER UPDATE OF altered_id ON tab
> REFERENCING NEW AS newt OLD AS oldt
> FOR EACH ROW MODE DB2SQL
> SELECT newt.altered_id from tab;
> --Drop the first column in the table. This will cause the column positions to be recalculated within the table
> alter table tab drop column element_id;
> --mytrig is still looking for column altered_id at position 2 but drop column has changed it's position within the table to 1
> update tab set altered_id='bb';
> As shown in the example above, table "TAB" only has 2 columns. The trigger "MYTRIG" uses the 2nd column in it's trigger action through the REFERENCING clause. During trigger action sql parsing, every column referenced through REFERENCING clause gets transformed into a reference to the column through it's column position in the trigger table(this change to look for columns based on their column positions rather than the name went in as revision 397959 with following commit comments DERBY-1258 Change the generated code for a new/old column in a row trigger to access columns by position and not name to avoid the case-insensitive name lookup specified by JDBC.) When in the script above, we drop the column in position 1, the trigger "MYTRIG" ends up becoming invalid because column being used in the trigger action is no more in column position 2.
> One possible solution is to regenerate the SPSDescriptor associated with the trigger action for all the triggers defined on the table whose column is getting dropped. We could be little smarter and only regenerate the SPSDescriptor for the triggers who use the REFERENCING clause. But we need to do more testing to make sure that triggers without REFERENCING clause do not get impacted by a drop of column which is not the last column of the table. This optimization of recognizing the right triggers may not be worth it since performance may not be that big a criteria for an ALTER TABLE DROP COLUMN which should be a rare operation in a production system.
> An interim solution to this problem is obviously to drop and recreate the triggers
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Assigned] (DERBY-4984) ALTER TABLE DROP COLUMN may leave
triggers invalid even if they are not using the column getting dropped.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4984?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor reassigned DERBY-4984:
--------------------------------------
Assignee: Mamta A. Satoor
> ALTER TABLE DROP COLUMN may leave triggers invalid even if they are not using the column getting dropped.
> ---------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4984
> URL: https://issues.apache.org/jira/browse/DERBY-4984
> 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, 10.6.2.1, 10.7.1.1
> Reporter: Mamta A. Satoor
> Assignee: Mamta A. Satoor
> Labels: derby_triage10_8
> Fix For: 10.7.1.4, 10.8.1.3
>
>
> While doing testing for DERBY-4887, I found a case where ALTER TABLE DROP COLUMN will leave triggers in invalid state even if those triggers are not using the column getting dropped. eg
> CREATE TABLE tab (
> element_id INTEGER NOT NULL,
> altered_id VARCHAR(30) NOT NULL
> );
> insert into tab values(1,'aa');
> -- Create a trigger against the table
> CREATE TRIGGER mytrig
> AFTER UPDATE OF altered_id ON tab
> REFERENCING NEW AS newt OLD AS oldt
> FOR EACH ROW MODE DB2SQL
> SELECT newt.altered_id from tab;
> --Drop the first column in the table. This will cause the column positions to be recalculated within the table
> alter table tab drop column element_id;
> --mytrig is still looking for column altered_id at position 2 but drop column has changed it's position within the table to 1
> update tab set altered_id='bb';
> As shown in the example above, table "TAB" only has 2 columns. The trigger "MYTRIG" uses the 2nd column in it's trigger action through the REFERENCING clause. During trigger action sql parsing, every column referenced through REFERENCING clause gets transformed into a reference to the column through it's column position in the trigger table(this change to look for columns based on their column positions rather than the name went in as revision 397959 with following commit comments DERBY-1258 Change the generated code for a new/old column in a row trigger to access columns by position and not name to avoid the case-insensitive name lookup specified by JDBC.) When in the script above, we drop the column in position 1, the trigger "MYTRIG" ends up becoming invalid because column being used in the trigger action is no more in column position 2.
> One possible solution is to regenerate the SPSDescriptor associated with the trigger action for all the triggers defined on the table whose column is getting dropped. We could be little smarter and only regenerate the SPSDescriptor for the triggers who use the REFERENCING clause. But we need to do more testing to make sure that triggers without REFERENCING clause do not get impacted by a drop of column which is not the last column of the table. This optimization of recognizing the right triggers may not be worth it since performance may not be that big a criteria for an ALTER TABLE DROP COLUMN which should be a rare operation in a production system.
> An interim solution to this problem is obviously to drop and recreate the triggers
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Resolved] (DERBY-4984) ALTER TABLE DROP COLUMN may leave
triggers invalid even if they are not using the column getting dropped.
Posted by "Mamta A. Satoor (Resolved) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4984?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor resolved DERBY-4984.
------------------------------------
Resolution: Fixed
> ALTER TABLE DROP COLUMN may leave triggers invalid even if they are not using the column getting dropped.
> ---------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4984
> URL: https://issues.apache.org/jira/browse/DERBY-4984
> 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, 10.6.2.1, 10.7.1.1
> Reporter: Mamta A. Satoor
> Assignee: Mamta A. Satoor
> Labels: derby_backport_reject_10_5, derby_backport_reject_10_6
> Fix For: 10.7.1.4, 10.8.2.2
>
>
> While doing testing for DERBY-4887, I found a case where ALTER TABLE DROP COLUMN will leave triggers in invalid state even if those triggers are not using the column getting dropped. eg
> CREATE TABLE tab (
> element_id INTEGER NOT NULL,
> altered_id VARCHAR(30) NOT NULL
> );
> insert into tab values(1,'aa');
> -- Create a trigger against the table
> CREATE TRIGGER mytrig
> AFTER UPDATE OF altered_id ON tab
> REFERENCING NEW AS newt OLD AS oldt
> FOR EACH ROW MODE DB2SQL
> SELECT newt.altered_id from tab;
> --Drop the first column in the table. This will cause the column positions to be recalculated within the table
> alter table tab drop column element_id;
> --mytrig is still looking for column altered_id at position 2 but drop column has changed it's position within the table to 1
> update tab set altered_id='bb';
> As shown in the example above, table "TAB" only has 2 columns. The trigger "MYTRIG" uses the 2nd column in it's trigger action through the REFERENCING clause. During trigger action sql parsing, every column referenced through REFERENCING clause gets transformed into a reference to the column through it's column position in the trigger table(this change to look for columns based on their column positions rather than the name went in as revision 397959 with following commit comments DERBY-1258 Change the generated code for a new/old column in a row trigger to access columns by position and not name to avoid the case-insensitive name lookup specified by JDBC.) When in the script above, we drop the column in position 1, the trigger "MYTRIG" ends up becoming invalid because column being used in the trigger action is no more in column position 2.
> One possible solution is to regenerate the SPSDescriptor associated with the trigger action for all the triggers defined on the table whose column is getting dropped. We could be little smarter and only regenerate the SPSDescriptor for the triggers who use the REFERENCING clause. But we need to do more testing to make sure that triggers without REFERENCING clause do not get impacted by a drop of column which is not the last column of the table. This optimization of recognizing the right triggers may not be worth it since performance may not be that big a criteria for an ALTER TABLE DROP COLUMN which should be a rare operation in a production system.
> An interim solution to this problem is obviously to drop and recreate the triggers
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-4984) ALTER TABLE DROP COLUMN may leave
triggers invalid even if they are not using the column getting dropped.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4984?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12995671#comment-12995671 ]
Mamta A. Satoor commented on DERBY-4984:
----------------------------------------
The trigger with the wrong column number in it's generated trigger action will cause the trigger to error out when it gets fired next time around after ALTER TABLE DROP COLUMN. I have committed changes today for this jira which will fix the generated trigger action to use the correct column positions for columns used through the REFERENCING clause.
> ALTER TABLE DROP COLUMN may leave triggers invalid even if they are not using the column getting dropped.
> ---------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4984
> URL: https://issues.apache.org/jira/browse/DERBY-4984
> 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, 10.6.2.1, 10.7.1.1
> Reporter: Mamta A. Satoor
> Labels: derby_triage10_8
> Fix For: 10.8.0.0
>
>
> While doing testing for DERBY-4887, I found a case where ALTER TABLE DROP COLUMN will leave triggers in invalid state even if those triggers are not using the column getting dropped. eg
> CREATE TABLE tab (
> element_id INTEGER NOT NULL,
> altered_id VARCHAR(30) NOT NULL
> );
> insert into tab values(1,'aa');
> -- Create a trigger against the table
> CREATE TRIGGER mytrig
> AFTER UPDATE OF altered_id ON tab
> REFERENCING NEW AS newt OLD AS oldt
> FOR EACH ROW MODE DB2SQL
> SELECT newt.altered_id from tab;
> --Drop the first column in the table. This will cause the column positions to be recalculated within the table
> alter table tab drop column element_id;
> --mytrig is still looking for column altered_id at position 2 but drop column has changed it's position within the table to 1
> update tab set altered_id='bb';
> As shown in the example above, table "TAB" only has 2 columns. The trigger "MYTRIG" uses the 2nd column in it's trigger action through the REFERENCING clause. During trigger action sql parsing, every column referenced through REFERENCING clause gets transformed into a reference to the column through it's column position in the trigger table(this change to look for columns based on their column positions rather than the name went in as revision 397959 with following commit comments DERBY-1258 Change the generated code for a new/old column in a row trigger to access columns by position and not name to avoid the case-insensitive name lookup specified by JDBC.) When in the script above, we drop the column in position 1, the trigger "MYTRIG" ends up becoming invalid because column being used in the trigger action is no more in column position 2.
> One possible solution is to regenerate the SPSDescriptor associated with the trigger action for all the triggers defined on the table whose column is getting dropped. We could be little smarter and only regenerate the SPSDescriptor for the triggers who use the REFERENCING clause. But we need to do more testing to make sure that triggers without REFERENCING clause do not get impacted by a drop of column which is not the last column of the table. This optimization of recognizing the right triggers may not be worth it since performance may not be that big a criteria for an ALTER TABLE DROP COLUMN which should be a rare operation in a production system.
> An interim solution to this problem is obviously to drop and recreate the triggers
--
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-4984) ALTER TABLE DROP COLUMN may leave
triggers invalid even if they are not using the column getting dropped.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4984?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor updated DERBY-4984:
-----------------------------------
Fix Version/s: 10.8.0.0
> ALTER TABLE DROP COLUMN may leave triggers invalid even if they are not using the column getting dropped.
> ---------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4984
> URL: https://issues.apache.org/jira/browse/DERBY-4984
> 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, 10.6.2.1, 10.7.1.1
> Reporter: Mamta A. Satoor
> Labels: derby_triage10_8
> Fix For: 10.8.0.0
>
>
> While doing testing for DERBY-4887, I found a case where ALTER TABLE DROP COLUMN will leave triggers in invalid state even if those triggers are not using the column getting dropped. eg
> CREATE TABLE tab (
> element_id INTEGER NOT NULL,
> altered_id VARCHAR(30) NOT NULL
> );
> insert into tab values(1,'aa');
> -- Create a trigger against the table
> CREATE TRIGGER mytrig
> AFTER UPDATE OF altered_id ON tab
> REFERENCING NEW AS newt OLD AS oldt
> FOR EACH ROW MODE DB2SQL
> SELECT newt.altered_id from tab;
> --Drop the first column in the table. This will cause the column positions to be recalculated within the table
> alter table tab drop column element_id;
> --mytrig is still looking for column altered_id at position 2 but drop column has changed it's position within the table to 1
> update tab set altered_id='bb';
> As shown in the example above, table "TAB" only has 2 columns. The trigger "MYTRIG" uses the 2nd column in it's trigger action through the REFERENCING clause. During trigger action sql parsing, every column referenced through REFERENCING clause gets transformed into a reference to the column through it's column position in the trigger table(this change to look for columns based on their column positions rather than the name went in as revision 397959 with following commit comments DERBY-1258 Change the generated code for a new/old column in a row trigger to access columns by position and not name to avoid the case-insensitive name lookup specified by JDBC.) When in the script above, we drop the column in position 1, the trigger "MYTRIG" ends up becoming invalid because column being used in the trigger action is no more in column position 2.
> One possible solution is to regenerate the SPSDescriptor associated with the trigger action for all the triggers defined on the table whose column is getting dropped. We could be little smarter and only regenerate the SPSDescriptor for the triggers who use the REFERENCING clause. But we need to do more testing to make sure that triggers without REFERENCING clause do not get impacted by a drop of column which is not the last column of the table. This optimization of recognizing the right triggers may not be worth it since performance may not be that big a criteria for an ALTER TABLE DROP COLUMN which should be a rare operation in a production system.
> An interim solution to this problem is obviously to drop and recreate the triggers
--
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Resolved] (DERBY-4984) ALTER TABLE DROP COLUMN may leave
triggers invalid even if they are not using the column getting dropped.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4984?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor resolved DERBY-4984.
------------------------------------
Resolution: Fixed
> ALTER TABLE DROP COLUMN may leave triggers invalid even if they are not using the column getting dropped.
> ---------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4984
> URL: https://issues.apache.org/jira/browse/DERBY-4984
> 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, 10.6.2.1, 10.7.1.1
> Reporter: Mamta A. Satoor
> Assignee: Mamta A. Satoor
> Labels: derby_triage10_8
> Fix For: 10.7.1.4, 10.8.1.3
>
>
> While doing testing for DERBY-4887, I found a case where ALTER TABLE DROP COLUMN will leave triggers in invalid state even if those triggers are not using the column getting dropped. eg
> CREATE TABLE tab (
> element_id INTEGER NOT NULL,
> altered_id VARCHAR(30) NOT NULL
> );
> insert into tab values(1,'aa');
> -- Create a trigger against the table
> CREATE TRIGGER mytrig
> AFTER UPDATE OF altered_id ON tab
> REFERENCING NEW AS newt OLD AS oldt
> FOR EACH ROW MODE DB2SQL
> SELECT newt.altered_id from tab;
> --Drop the first column in the table. This will cause the column positions to be recalculated within the table
> alter table tab drop column element_id;
> --mytrig is still looking for column altered_id at position 2 but drop column has changed it's position within the table to 1
> update tab set altered_id='bb';
> As shown in the example above, table "TAB" only has 2 columns. The trigger "MYTRIG" uses the 2nd column in it's trigger action through the REFERENCING clause. During trigger action sql parsing, every column referenced through REFERENCING clause gets transformed into a reference to the column through it's column position in the trigger table(this change to look for columns based on their column positions rather than the name went in as revision 397959 with following commit comments DERBY-1258 Change the generated code for a new/old column in a row trigger to access columns by position and not name to avoid the case-insensitive name lookup specified by JDBC.) When in the script above, we drop the column in position 1, the trigger "MYTRIG" ends up becoming invalid because column being used in the trigger action is no more in column position 2.
> One possible solution is to regenerate the SPSDescriptor associated with the trigger action for all the triggers defined on the table whose column is getting dropped. We could be little smarter and only regenerate the SPSDescriptor for the triggers who use the REFERENCING clause. But we need to do more testing to make sure that triggers without REFERENCING clause do not get impacted by a drop of column which is not the last column of the table. This optimization of recognizing the right triggers may not be worth it since performance may not be that big a criteria for an ALTER TABLE DROP COLUMN which should be a rare operation in a production system.
> An interim solution to this problem is obviously to drop and recreate the triggers
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-4984) ALTER TABLE DROP COLUMN may leave
triggers invalid even if they are not using the column getting dropped.
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4984?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-4984:
---------------------------------
Urgency: Normal
Labels: derby_triage10_8 (was: )
> ALTER TABLE DROP COLUMN may leave triggers invalid even if they are not using the column getting dropped.
> ---------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4984
> URL: https://issues.apache.org/jira/browse/DERBY-4984
> 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, 10.6.2.1, 10.7.1.1
> Reporter: Mamta A. Satoor
> Labels: derby_triage10_8
>
> While doing testing for DERBY-4887, I found a case where ALTER TABLE DROP COLUMN will leave triggers in invalid state even if those triggers are not using the column getting dropped. eg
> CREATE TABLE tab (
> element_id INTEGER NOT NULL,
> altered_id VARCHAR(30) NOT NULL
> );
> insert into tab values(1,'aa');
> -- Create a trigger against the table
> CREATE TRIGGER mytrig
> AFTER UPDATE OF altered_id ON tab
> REFERENCING NEW AS newt OLD AS oldt
> FOR EACH ROW MODE DB2SQL
> SELECT newt.altered_id from tab;
> --Drop the first column in the table. This will cause the column positions to be recalculated within the table
> alter table tab drop column element_id;
> --mytrig is still looking for column altered_id at position 2 but drop column has changed it's position within the table to 1
> update tab set altered_id='bb';
> As shown in the example above, table "TAB" only has 2 columns. The trigger "MYTRIG" uses the 2nd column in it's trigger action through the REFERENCING clause. During trigger action sql parsing, every column referenced through REFERENCING clause gets transformed into a reference to the column through it's column position in the trigger table(this change to look for columns based on their column positions rather than the name went in as revision 397959 with following commit comments DERBY-1258 Change the generated code for a new/old column in a row trigger to access columns by position and not name to avoid the case-insensitive name lookup specified by JDBC.) When in the script above, we drop the column in position 1, the trigger "MYTRIG" ends up becoming invalid because column being used in the trigger action is no more in column position 2.
> One possible solution is to regenerate the SPSDescriptor associated with the trigger action for all the triggers defined on the table whose column is getting dropped. We could be little smarter and only regenerate the SPSDescriptor for the triggers who use the REFERENCING clause. But we need to do more testing to make sure that triggers without REFERENCING clause do not get impacted by a drop of column which is not the last column of the table. This optimization of recognizing the right triggers may not be worth it since performance may not be that big a criteria for an ALTER TABLE DROP COLUMN which should be a rare operation in a production system.
> An interim solution to this problem is obviously to drop and recreate the triggers
--
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (DERBY-4984) ALTER TABLE DROP COLUMN may leave
triggers invalid even if they are not using the column getting dropped.
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4984?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-4984:
---------------------------------
Fix Version/s: (was: 10.8.1.2)
10.8.1.3
> ALTER TABLE DROP COLUMN may leave triggers invalid even if they are not using the column getting dropped.
> ---------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4984
> URL: https://issues.apache.org/jira/browse/DERBY-4984
> 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, 10.6.2.1, 10.7.1.1
> Reporter: Mamta A. Satoor
> Labels: derby_triage10_8
> Fix For: 10.7.1.4, 10.8.1.3
>
>
> While doing testing for DERBY-4887, I found a case where ALTER TABLE DROP COLUMN will leave triggers in invalid state even if those triggers are not using the column getting dropped. eg
> CREATE TABLE tab (
> element_id INTEGER NOT NULL,
> altered_id VARCHAR(30) NOT NULL
> );
> insert into tab values(1,'aa');
> -- Create a trigger against the table
> CREATE TRIGGER mytrig
> AFTER UPDATE OF altered_id ON tab
> REFERENCING NEW AS newt OLD AS oldt
> FOR EACH ROW MODE DB2SQL
> SELECT newt.altered_id from tab;
> --Drop the first column in the table. This will cause the column positions to be recalculated within the table
> alter table tab drop column element_id;
> --mytrig is still looking for column altered_id at position 2 but drop column has changed it's position within the table to 1
> update tab set altered_id='bb';
> As shown in the example above, table "TAB" only has 2 columns. The trigger "MYTRIG" uses the 2nd column in it's trigger action through the REFERENCING clause. During trigger action sql parsing, every column referenced through REFERENCING clause gets transformed into a reference to the column through it's column position in the trigger table(this change to look for columns based on their column positions rather than the name went in as revision 397959 with following commit comments DERBY-1258 Change the generated code for a new/old column in a row trigger to access columns by position and not name to avoid the case-insensitive name lookup specified by JDBC.) When in the script above, we drop the column in position 1, the trigger "MYTRIG" ends up becoming invalid because column being used in the trigger action is no more in column position 2.
> One possible solution is to regenerate the SPSDescriptor associated with the trigger action for all the triggers defined on the table whose column is getting dropped. We could be little smarter and only regenerate the SPSDescriptor for the triggers who use the REFERENCING clause. But we need to do more testing to make sure that triggers without REFERENCING clause do not get impacted by a drop of column which is not the last column of the table. This optimization of recognizing the right triggers may not be worth it since performance may not be that big a criteria for an ALTER TABLE DROP COLUMN which should be a rare operation in a production system.
> An interim solution to this problem is obviously to drop and recreate the triggers
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-4984) ALTER TABLE DROP COLUMN may leave
triggers invalid even if they are not using the column getting dropped.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4984?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor updated DERBY-4984:
-----------------------------------
Fix Version/s: 10.7.1.3
> ALTER TABLE DROP COLUMN may leave triggers invalid even if they are not using the column getting dropped.
> ---------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4984
> URL: https://issues.apache.org/jira/browse/DERBY-4984
> 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, 10.6.2.1, 10.7.1.1
> Reporter: Mamta A. Satoor
> Labels: derby_triage10_8
> Fix For: 10.7.1.3, 10.8.0.0
>
>
> While doing testing for DERBY-4887, I found a case where ALTER TABLE DROP COLUMN will leave triggers in invalid state even if those triggers are not using the column getting dropped. eg
> CREATE TABLE tab (
> element_id INTEGER NOT NULL,
> altered_id VARCHAR(30) NOT NULL
> );
> insert into tab values(1,'aa');
> -- Create a trigger against the table
> CREATE TRIGGER mytrig
> AFTER UPDATE OF altered_id ON tab
> REFERENCING NEW AS newt OLD AS oldt
> FOR EACH ROW MODE DB2SQL
> SELECT newt.altered_id from tab;
> --Drop the first column in the table. This will cause the column positions to be recalculated within the table
> alter table tab drop column element_id;
> --mytrig is still looking for column altered_id at position 2 but drop column has changed it's position within the table to 1
> update tab set altered_id='bb';
> As shown in the example above, table "TAB" only has 2 columns. The trigger "MYTRIG" uses the 2nd column in it's trigger action through the REFERENCING clause. During trigger action sql parsing, every column referenced through REFERENCING clause gets transformed into a reference to the column through it's column position in the trigger table(this change to look for columns based on their column positions rather than the name went in as revision 397959 with following commit comments DERBY-1258 Change the generated code for a new/old column in a row trigger to access columns by position and not name to avoid the case-insensitive name lookup specified by JDBC.) When in the script above, we drop the column in position 1, the trigger "MYTRIG" ends up becoming invalid because column being used in the trigger action is no more in column position 2.
> One possible solution is to regenerate the SPSDescriptor associated with the trigger action for all the triggers defined on the table whose column is getting dropped. We could be little smarter and only regenerate the SPSDescriptor for the triggers who use the REFERENCING clause. But we need to do more testing to make sure that triggers without REFERENCING clause do not get impacted by a drop of column which is not the last column of the table. This optimization of recognizing the right triggers may not be worth it since performance may not be that big a criteria for an ALTER TABLE DROP COLUMN which should be a rare operation in a production system.
> An interim solution to this problem is obviously to drop and recreate the triggers
--
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-4984) ALTER TABLE DROP COLUMN may leave
triggers invalid even if they are not using the column getting dropped.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4984?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12995670#comment-12995670 ]
Mamta A. Satoor commented on DERBY-4984:
----------------------------------------
The changes committed for this jira can be backported all the way to 10.3
> ALTER TABLE DROP COLUMN may leave triggers invalid even if they are not using the column getting dropped.
> ---------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4984
> URL: https://issues.apache.org/jira/browse/DERBY-4984
> 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, 10.6.2.1, 10.7.1.1
> Reporter: Mamta A. Satoor
> Labels: derby_triage10_8
> Fix For: 10.8.0.0
>
>
> While doing testing for DERBY-4887, I found a case where ALTER TABLE DROP COLUMN will leave triggers in invalid state even if those triggers are not using the column getting dropped. eg
> CREATE TABLE tab (
> element_id INTEGER NOT NULL,
> altered_id VARCHAR(30) NOT NULL
> );
> insert into tab values(1,'aa');
> -- Create a trigger against the table
> CREATE TRIGGER mytrig
> AFTER UPDATE OF altered_id ON tab
> REFERENCING NEW AS newt OLD AS oldt
> FOR EACH ROW MODE DB2SQL
> SELECT newt.altered_id from tab;
> --Drop the first column in the table. This will cause the column positions to be recalculated within the table
> alter table tab drop column element_id;
> --mytrig is still looking for column altered_id at position 2 but drop column has changed it's position within the table to 1
> update tab set altered_id='bb';
> As shown in the example above, table "TAB" only has 2 columns. The trigger "MYTRIG" uses the 2nd column in it's trigger action through the REFERENCING clause. During trigger action sql parsing, every column referenced through REFERENCING clause gets transformed into a reference to the column through it's column position in the trigger table(this change to look for columns based on their column positions rather than the name went in as revision 397959 with following commit comments DERBY-1258 Change the generated code for a new/old column in a row trigger to access columns by position and not name to avoid the case-insensitive name lookup specified by JDBC.) When in the script above, we drop the column in position 1, the trigger "MYTRIG" ends up becoming invalid because column being used in the trigger action is no more in column position 2.
> One possible solution is to regenerate the SPSDescriptor associated with the trigger action for all the triggers defined on the table whose column is getting dropped. We could be little smarter and only regenerate the SPSDescriptor for the triggers who use the REFERENCING clause. But we need to do more testing to make sure that triggers without REFERENCING clause do not get impacted by a drop of column which is not the last column of the table. This optimization of recognizing the right triggers may not be worth it since performance may not be that big a criteria for an ALTER TABLE DROP COLUMN which should be a rare operation in a production system.
> An interim solution to this problem is obviously to drop and recreate the triggers
--
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (DERBY-4984) ALTER TABLE DROP COLUMN may leave
triggers invalid even if they are not using the column getting dropped.
Posted by "Mamta A. Satoor (Updated) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4984?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor updated DERBY-4984:
-----------------------------------
Labels: derby_backport_reject_10_5 derby_backport_reject_10_6 derby_backport_reject_10_7 derby_triage10_8 (was: derby_triage10_8)
> ALTER TABLE DROP COLUMN may leave triggers invalid even if they are not using the column getting dropped.
> ---------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4984
> URL: https://issues.apache.org/jira/browse/DERBY-4984
> 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, 10.6.2.1, 10.7.1.1
> Reporter: Mamta A. Satoor
> Assignee: Mamta A. Satoor
> Labels: derby_backport_reject_10_5, derby_backport_reject_10_6, derby_backport_reject_10_7, derby_triage10_8
> Fix For: 10.7.1.4, 10.8.2.2
>
>
> While doing testing for DERBY-4887, I found a case where ALTER TABLE DROP COLUMN will leave triggers in invalid state even if those triggers are not using the column getting dropped. eg
> CREATE TABLE tab (
> element_id INTEGER NOT NULL,
> altered_id VARCHAR(30) NOT NULL
> );
> insert into tab values(1,'aa');
> -- Create a trigger against the table
> CREATE TRIGGER mytrig
> AFTER UPDATE OF altered_id ON tab
> REFERENCING NEW AS newt OLD AS oldt
> FOR EACH ROW MODE DB2SQL
> SELECT newt.altered_id from tab;
> --Drop the first column in the table. This will cause the column positions to be recalculated within the table
> alter table tab drop column element_id;
> --mytrig is still looking for column altered_id at position 2 but drop column has changed it's position within the table to 1
> update tab set altered_id='bb';
> As shown in the example above, table "TAB" only has 2 columns. The trigger "MYTRIG" uses the 2nd column in it's trigger action through the REFERENCING clause. During trigger action sql parsing, every column referenced through REFERENCING clause gets transformed into a reference to the column through it's column position in the trigger table(this change to look for columns based on their column positions rather than the name went in as revision 397959 with following commit comments DERBY-1258 Change the generated code for a new/old column in a row trigger to access columns by position and not name to avoid the case-insensitive name lookup specified by JDBC.) When in the script above, we drop the column in position 1, the trigger "MYTRIG" ends up becoming invalid because column being used in the trigger action is no more in column position 2.
> One possible solution is to regenerate the SPSDescriptor associated with the trigger action for all the triggers defined on the table whose column is getting dropped. We could be little smarter and only regenerate the SPSDescriptor for the triggers who use the REFERENCING clause. But we need to do more testing to make sure that triggers without REFERENCING clause do not get impacted by a drop of column which is not the last column of the table. This optimization of recognizing the right triggers may not be worth it since performance may not be that big a criteria for an ALTER TABLE DROP COLUMN which should be a rare operation in a production system.
> An interim solution to this problem is obviously to drop and recreate the triggers
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira