[
https://issues.apache.org/jira/browse/DERBY-4874?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12927995#action_12927995
]
Mamta A. Satoor edited comment on DERBY-4874 at 11/3/10 5:48 PM:
-----------------------------------------------------------------
Here is what I found out about the test case of alter table to drop the column
and another alter table to add the column back again. Although it appears that
it is working(no exception thrown during the UPDATE sql but UPDATE trigger is
not updating the table it is supposed to update). The test case is as follows
Following is the schema to create the table and the trigger
connect 'jdbc:derby:wombat;create=true';
-- Create the table
CREATE TABLE tab (
element_id INTEGER NOT NULL,
altered_id VARCHAR(30) NOT NULL,
counter SMALLINT NOT NULL DEFAULT 0,
timets TIMESTAMP NOT NULL
);
-- Create a trigger against the table
CREATE TRIGGER mytrig
AFTER UPDATE ON tab
REFERENCING NEW AS newt OLD AS oldt
FOR EACH ROW MODE DB2SQL
UPDATE tab set tab.counter = CASE WHEN (oldt.counter < 32767) THEN
(oldt.counter + 1) ELSE 1 END
WHERE ((newt.counter is null) or (oldt.counter = newt.counter))
AND newt.element_id = tab.element_id
AND newt.altered_id = tab.altered_id;
-- Next, we dop and recreate the column (with a different length) and a row
into the table
alter table tab drop column altered_id;
ALTER TABLE TAB ADD COLUMN altered_id VARCHAR(64);
insert into tab(element_id, altered_id, counter, timets) values (99,
'1234567890',1,CURRENT_TIMESTAMP);
select * from tab;
ELEMENT_ID |COUNT&|TIMETS |ALTERED_ID
----------------------------------------------------------------------------
99 |1 |2010-11-03 10:05:29.39 |1234567890
-- the following update will cause the trigger to fire which should increment
the counter column's value from 1 to 2 but it doesn't. The explanation is below
update tab set timets = CURRENT_TIMESTAMP where ELEMENT_ID = 99;
select * from tab;
ELEMENT_ID |COUNT&|TIMETS |ALTERED_ID
99 |1 |2010-11-03 10:05:38.343 |1234567890
The trigger action gets changed internally before a SPSDescriptor is created
for it
UPDATE tab set tab.counter = CASE WHEN (CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getObject(3)
AS SMALLINT) < 32767) THEN (CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getObject(3)
AS SMALLINT) + 1) ELSE 1 END
WHERE ((CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(3)
AS SMALLINT) is null) or (CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getObject(3)
AS SMALLINT) = CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(3)
AS SMALLINT) ))
AND CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(1)
AS INTEGER) = tab.element_id
AND CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(2)
AS VARCHAR(30)) = tab.altered_id
As we can see above, the columns are being accessed through column positions
rather than names(this was done as part of DERBY-1258. But the column positions
have changed in the underlying table because of the drop column and new column
additions. The above sql, when it gets the column
org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(2),
it is getting the column COUNTER rather than the column ALTERED_ID and because
of that, the WHERE clause above returns FALSE and hence no row is updated by
the trigger action.
This case is slightly different than the original test case where the ALTER
TABLE changed the length of the existing column. I will explain the difference
through the system tables. SYSTRIGGERS keeps track of the columns that are
referenced in the trigger action. Those columns are later used at trigger
execution time to decide which columns from the trigger table need to be
actually fetched into memory. The column tracking is done through the column
positions in the table. An ALTER TABLE which drops the column and adds it back
again, then the column is going to get added into a new position in the table
and hence the original column positions saved in SYSTRIGGERS are not going to
be correct anymore. In a case like this, we want to recalculate the column
positions of the trigger action columns and then regenerate the SQL for
SPSDescriptor and compile the regenerated SQL. For our original case, where
simply the length of the colunm was changed but not it's position, we just want
to regenerate the SQL for SPSDescriptor and then compile that new query. For
our original case, there is no need to recalculate the column positions of the
trigger action columns. It may turn out to be easier to just do both the steps
for all trigger invalidation (provided that the trigger has REFERENCES clause
because the problem is only when trigger action is referenecing to old and new
column values of the row. For all other trigger cases, we should be fine)
I will file a jira for this problem and go back to looking at SPSDescriptor sql
regerenation and updating SYSSTATEMENTS with that new sql.for a fix for the
original problem.
Please let me know if anyone has any comments.
was (Author: mamtas):
Here is what I found out about the test case of alter table to drop the
column and another alter table to add the column back again. Although it
appears that it is working(no exception thrown during the UPDATE sql but UPDATE
trigger is not updating the table it is supposed to update). The test case is
as follows
Following is the schema to create the table and the trigger
connect 'jdbc:derby:wombat;create=true';
-- Create the table
CREATE TABLE tab (
element_id INTEGER NOT NULL,
altered_id VARCHAR(30) NOT NULL,
counter SMALLINT NOT NULL DEFAULT 0,
timets TIMESTAMP NOT NULL
);
-- Create a trigger against the table
CREATE TRIGGER mytrig
AFTER UPDATE ON tab
REFERENCING NEW AS newt OLD AS oldt
FOR EACH ROW MODE DB2SQL
UPDATE tab set tab.counter = CASE WHEN (oldt.counter < 32767) THEN
(oldt.counter + 1) ELSE 1 END
WHERE ((newt.counter is null) or (oldt.counter = newt.counter))
AND newt.element_id = tab.element_id
AND newt.altered_id = tab.altered_id;
-- Next, we dop and recreate the column (with a different length) and a row
into the table
alter table tab drop column altered_id;
ALTER TABLE TAB ADD COLUMN altered_id VARCHAR(64);
insert into tab(element_id, altered_id, counter, timets) values (99,
'1234567890',1,CURRENT_TIMESTAMP);
select * from tab;
ELEMENT_ID |COUNT&|TIMETS |ALTERED_ID
----------------------------------------------------------------------------
99 |1 |2010-11-03 10:05:29.39 |1234567890
-- the following update will cause the trigger to fire which should increment
the counter column's value from 1 to 2 but it doesn't. The explanation is below
update tab set timets = CURRENT_TIMESTAMP where ELEMENT_ID = 99;
select * from tab;
ELEMENT_ID |COUNT&|TIMETS |ALTERED_ID
99 |1 |2010-11-03 10:05:38.343 |1234567890
The trigger action gets changed internally before a SPSDescriptor is created
for it
UPDATE tab set tab.counter = CASE WHEN (CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getObject(3)
AS SMALLINT) < 32767) THEN (CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getObject(3)
AS SMALLINT) + 1) ELSE 1 END
WHERE ((CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(3)
AS SMALLINT) is null) or (CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getObject(3)
AS SMALLINT) = CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(3)
AS SMALLINT) ))
AND CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(1)
AS INTEGER) = tab.element_id
AND CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(2)
AS VARCHAR(30)) = tab.altered_id
As we can see above, the columns are being accessed through column positions
rather than names(this was done as part of DERBY-1258. But the column positions
have changed in the underlying table because of the drop column and new column
additions. The above sql, when it gets the column
org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(2),
it is getting the column COUNTER rather than the column ALTERED_ID and because
of that, the WHERE clause above returns FALSE and hence no row is updated by
the trigger action.
I will file a jira for this problem and go back to ;looking for a fix for the
original problem.
> Trigger does not recognize new size of VARCHAR column expanded with ALTER
> TABLE. It fails with ERROR 22001: A truncation error was encountered trying
> to shrink VARCHAR
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4874
> URL: https://issues.apache.org/jira/browse/DERBY-4874
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.2.2.1, 10.3.3.1, 10.4.2.1, 10.5.3.0, 10.6.2.1,
> 10.7.1.0
> Reporter: Kathey Marsden
> Assignee: Mamta A. Satoor
> Attachments: trigAlterColumn.sql
>
>
> Trigger created before VARCHAR column is expanded with ALTER TABLE does not
> recognize new size and fails with:
> ERROR 22001: A truncation error was encountered trying to shrink VARCHAR
> '012345
> 678901234567890123456789001234567890' to length 30.
> CREATE TABLE tab (
> element_id INTEGER NOT NULL,
> altered_id VARCHAR(30) NOT NULL,
> counter SMALLINT NOT NULL DEFAULT 0,
> timets TIMESTAMP NOT NULL
> );
> 0 rows inserted/updated/deleted
> ij> -- Create a trigger against the table
> CREATE TRIGGER mytrig
> AFTER UPDATE ON tab
> REFERENCING NEW AS newt OLD AS oldt
> FOR EACH ROW MODE DB2SQL
> UPDATE tab set tab.counter = CASE WHEN (oldt.counter < 32767) THEN
> (oldt.count
> er + 1) ELSE 1 END
> WHERE ((newt.counter is null) or (oldt.counter = newt.counter))
> AND newt.element_id = tab.element_id
> AND newt.altered_id = tab.altered_id;
> 0 rows inserted/updated/deleted
> ij> -- Alter the table to increase column
> ALTER TABLE tab ALTER altered_id SET DATA TYPE VARCHAR(64);
> 0 rows inserted/updated/deleted
> ij> -- insert the data
> insert into tab values (99,
> '012345678901234567890123456789001234567890',1,CURRE
> NT_TIMESTAMP);
> 1 row inserted/updated/deleted
> ij> -- update and reproduce the issue
> update tab set timets = CURRENT_TIMESTAMP where ELEMENT_ID = 99;
> ERROR 22001: A truncation error was encountered trying to shrink VARCHAR
> '012345
> 678901234567890123456789001234567890' to length 30.
> java.sql.SQLDataException: A truncation error was encountered trying to
> shrink V
> ARCHAR '012345678901234567890123456789001234567890' to length 30.
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLE
> xceptionFactory40.java:79)
> at
> org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:256)
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException
> (TransactionResourceImpl.java:391)
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Tr
> ansactionResourceImpl.java:346)
> at
> org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConne
> ction.java:2269)
> at
> org.apache.derby.impl.jdbc.ConnectionChild.handleException(Connection
> Child.java:81)
> at
> org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedState
> ment.java:1321)
> at
> org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java
> :625)
> at
> org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java
> :555)
> at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:367)
> at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:521)
> at
> org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:3
> 63)
> at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:261)
> at org.apache.derby.impl.tools.ij.Main.go(Main.java:229)
> at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:184)
> at org.apache.derby.impl.tools.ij.Main.main(Main.java:75)
> at org.apache.derby.tools.ij.main(ij.java:59)
> Caused by: java.sql.SQLException: A truncation error was encountered trying
> to s
> hrink VARCHAR '012345678901234567890123456789001234567890' to length 30.
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExc
> eptionFactory.java:45)
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransport
> AcrossDRDA(SQLExceptionFactory40.java:119)
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLE
> xceptionFactory40.java:70)
> ... 16 more
> Caused by: ERROR 22001: A truncation error was encountered trying to shrink
> VARC
> HAR '012345678901234567890123456789001234567890' to length 30.
> at
> org.apache.derby.iapi.error.StandardException.newException(StandardEx
> ception.java:343)
> at
> org.apache.derby.iapi.types.SQLChar.hasNonBlankChars(SQLChar.java:176
> 6)
> at org.apache.derby.iapi.types.SQLChar.setWidth(SQLChar.java:1840)
> at
> org.apache.derby.exe.ac0b5b0099x012bxf542xab11x0000001bd2983.e2(Unkno
> wn Source)
> at
> org.apache.derby.impl.services.reflect.DirectCall.invoke(ReflectGener
> atedClass.java:143)
> at
> org.apache.derby.impl.sql.execute.GenericQualifier.getOrderable(Gener
> icQualifier.java:96)
> at
> org.apache.derby.impl.sql.execute.NoPutResultSetImpl.clearOrderableCa
> che(NoPutResultSetImpl.java:313)
> at
> org.apache.derby.impl.sql.execute.TableScanResultSet.openScanControll
> er(TableScanResultSet.java:350)
> at
> org.apache.derby.impl.sql.execute.TableScanResultSet.openCore(TableSc
> anResultSet.java:262)
> at
> org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(P
> rojectRestrictResultSet.java:174)
> at
> org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(P
> rojectRestrictResultSet.java:174)
> at
> org.apache.derby.impl.sql.execute.NormalizeResultSet.openCore(Normali
> zeResultSet.java:146)
> at
> org.apache.derby.impl.sql.execute.UpdateResultSet.setup(UpdateResultS
> et.java:344)
> at
> org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSe
> t.java:263)
> at
> org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Generi
> cPreparedStatement.java:436)
> at
> org.apache.derby.impl.sql.GenericPreparedStatement.executeSubStatemen
> t(GenericPreparedStatement.java:306)
> at
> org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(G
> enericTriggerExecutor.java:173)
> at
> org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(RowT
> riggerExecutor.java:111)
> at
> org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(T
> riggerEventActivator.java:278)
> at
> org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTriggers(U
> pdateResultSet.java:817)
> at
> org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSe
> t.java:280)
> at
> org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Generi
> cPreparedStatement.java:436)
> at
> org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPre
> paredStatement.java:317)
> at
> org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedState
> ment.java:1232)
> ... 10 more
> ij>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
