Return-Path: X-Original-To: apmail-db-derby-dev-archive@www.apache.org Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 938189A98 for ; Wed, 15 Feb 2012 00:02:25 +0000 (UTC) Received: (qmail 60579 invoked by uid 500); 15 Feb 2012 00:02:25 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 60555 invoked by uid 500); 15 Feb 2012 00:02:25 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 60548 invoked by uid 99); 15 Feb 2012 00:02:25 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 15 Feb 2012 00:02:25 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=5.0 tests=ALL_TRUSTED,T_RP_MATCHES_RCVD X-Spam-Check-By: apache.org Received: from [140.211.11.116] (HELO hel.zones.apache.org) (140.211.11.116) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 15 Feb 2012 00:02:21 +0000 Received: from hel.zones.apache.org (hel.zones.apache.org [140.211.11.116]) by hel.zones.apache.org (Postfix) with ESMTP id 653231B707C for ; Wed, 15 Feb 2012 00:02:00 +0000 (UTC) Date: Wed, 15 Feb 2012 00:02:00 +0000 (UTC) From: "Mamta A. Satoor (Resolved) (JIRA)" To: derby-dev@db.apache.org Message-ID: <603457450.38642.1329264120415.JavaMail.tomcat@hel.zones.apache.org> In-Reply-To: <23021464.129771288310122354.JavaMail.jira@thor> Subject: [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 MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 X-Virus-Checked: Checked by ClamAV on apache.org [ 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