[
https://issues.apache.org/jira/browse/DERBY-4874?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12927995#action_12927995
]
Mamta A. Satoor commented on DERBY-4874:
----------------------------------------
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.
