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 (Resolved) (JIRA)" <ji...@apache.org> on 2012/02/15 01:02:00 UTC
[jira] [Resolved] (DERBY-4874) 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
[ https://issues.apache.org/jira/browse/DERBY-4874?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor resolved DERBY-4874.
------------------------------------
Resolution: Fixed
Will go ahead and close this issue since there is nothing outstanding at this point.
> 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
> Components: SQL
> 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.1
> Reporter: Kathey Marsden
> Assignee: Mamta A. Satoor
> Fix For: 10.3.3.1, 10.4.2.1, 10.5.3.2, 10.6.2.3, 10.7.1.4, 10.8.2.2
>
> Attachments: DERBY4874_106_backport_diff_ver4.txt, DERBY4874_106_backport_stat_ver4.txt, DERBY4874_comments_and_test_missing_diff_ver3.txt, DERBY4874_comments_and_test_missing_stat_ver3.txt, DERBY4874_not_ready_for_commit_diff.txt, DERBY4874_not_ready_for_commit_diff_ver2.txt, DERBY4874_not_ready_for_commit_stat.txt, DERBY4874_not_ready_for_commit_stat_ver2.txt, derby.log, droptable.diff, releaseNote.html, 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.
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