[
https://issues.apache.org/jira/browse/DERBY-4874?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12930642#action_12930642
]
Mamta A. Satoor commented on DERBY-4874:
----------------------------------------
I am currently running Junit suite with my changes which are not ready for
commit. Once test that I see fails all the time is
DatabaseMetaDataTest.testGetURL with following failure. I do not know why my
changes would cause this failure. It seems like the terriotry and collation
properties are switched by the getURL method. Has anyone seen this failure
before?
junit.framework.ComparisonFailure: getURL match
expected:<...territory=en;collation=TERRITORY_BASED> but
was:<...collation=TERRITORY_BASED;territory=en>
As for my changes, I have copied the relevant code from CreateTriggerNode into
TriggerDescriptor to regenerate the trigger action statement. Currently, every
reference to a column from new table in trigger action gets switched to
CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(ColumnNumber)
AS COLUMNDatatype)
And every reference to
CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getObject(ColumnNumber)
AS COLUMNDatatype)
In our specific case, the Alter table has changed the data type from
varchar(30) to varchar(64) but the trigger action plan continued to use
varchar(30). To fix varchar(30) casting to varchar(64), we need to regenerate
the trigger action sql if
1)the trigger is found to be invalid,
2)the trigger is defined at row level (that is the only kind of trigger which
allows reference to individual columns from old/new row)
3)the trigger action plan has columns that reference old/new row columns
I check for above 3 conditions when a trigger is going to be fired and if true,
then regenerate the sql and update the SYSSTATEMENTS row for the trigger action
plan stored prepared statement.
At this point, I have lot of code duplication but once I get all the tests
running succesfully, I will post the patch as it is and then will work on
avoiding the code duplication between CreateTriggerNode and TriggerDescriptor.
> 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.
