From derby-dev-return-82430-apmail-db-derby-dev-archive=db.apache.org@db.apache.org Wed Nov 03 21:50:17 2010 Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 4086 invoked from network); 3 Nov 2010 21:50:17 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 3 Nov 2010 21:50:17 -0000 Received: (qmail 51658 invoked by uid 500); 3 Nov 2010 21:50:48 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 51460 invoked by uid 500); 3 Nov 2010 21:50:46 -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 51452 invoked by uid 99); 3 Nov 2010 21:50:46 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 03 Nov 2010 21:50:46 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.22] (HELO thor.apache.org) (140.211.11.22) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 03 Nov 2010 21:50:45 +0000 Received: from thor (localhost [127.0.0.1]) by thor.apache.org (8.13.8+Sun/8.13.8) with ESMTP id oA3LoOpR023765 for ; Wed, 3 Nov 2010 21:50:24 GMT Message-ID: <28809864.226681288821024409.JavaMail.jira@thor> Date: Wed, 3 Nov 2010 17:50:24 -0400 (EDT) From: "Mamta A. Satoor (JIRA)" To: derby-dev@db.apache.org Subject: [jira] Issue Comment Edited: (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 In-Reply-To: <23021464.129771288310122354.JavaMail.jira@thor> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/DERBY-4874?page=3Dcom.atlassian= .jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D1292= 7995#action_12927995 ]=20 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 col= umn and another alter table to add the column back again. Although it appea= rs 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=3Dtrue'; -- Create the table CREATE TABLE tab ( element_id INTEGER NOT NULL, altered_id =09 VARCHAR(30) NOT NULL, counter SMALLINT NOT NULL DEFAULT 0,=09 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 =3D CASE WHEN (oldt.counter < 32767) THEN (old= t.counter + 1) ELSE 1 END WHERE ((newt.counter is null) or (oldt.counter =3D newt.counter)) AND newt.element_id =3D tab.element_id AND newt.altered_id =3D 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);=20 insert into tab(element_id, altered_id, counter, timets) values (99, '12345= 67890',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 increme= nt the counter column's value from 1 to 2 but it doesn't. The explanation i= s below update tab set timets =3D CURRENT_TIMESTAMP where ELEMENT_ID =3D 99;=20 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 create= d for it UPDATE tab set tab.counter =3D CASE WHEN (CAST (org.apache.derby.iapi.db.Fa= ctory::getTriggerExecutionContext().getOldRow().getObject(3) AS SMALLINT) <= 32767) THEN (CAST (org.apache.derby.iapi.db.Factory::getTriggerExecutionCo= ntext().getOldRow().getObject(3) AS SMALLINT) + 1) ELSE 1 END=20 WHERE ((CAST (org.apache.derby.iapi.db.Factory::getTriggerExecutionContex= t().getNewRow().getObject(3) AS SMALLINT) is null) or (CAST (org.apache.der= by.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getObject(3) A= S SMALLINT) =3D CAST (org.apache.derby.iapi.db.Factory::getTriggerExecution= Context().getNewRow().getObject(3) AS SMALLINT) ))=20 AND CAST (org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().= getNewRow().getObject(1) AS INTEGER) =3D tab.element_id=20 AND CAST (org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().= getNewRow().getObject(2) AS VARCHAR(30)) =3D tab.altered_id=20 As we can see above, the columns are being accessed through column position= s rather than names(this was done as part of DERBY-1258. But the column pos= itions 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.der= by.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(2), = it is getting the column COUNTER rather than the column ALTERED_ID and beca= use of that, the WHERE clause above returns FALSE and hence no row is updat= ed 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 differ= ence through the system tables. SYSTRIGGERS keeps track of the columns that= are referenced in the trigger action. Those columns are later used at trig= ger execution time to decide which columns from the trigger table need to b= e actually fetched into memory. The column tracking is done through the col= umn 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 recalcula= te the column positions of the trigger action columns and then regenerate t= he 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 positi= on, we just want to regenerate the SQL for SPSDescriptor and then compile t= hat new query. For our original case, there is no need to recalculate the c= olumn 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 acti= on is referenecing to old and new column values of the row. For all other t= rigger 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 fo= r 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 a= ppears that it is working(no exception thrown during the UPDATE sql but UPD= ATE trigger is not updating the table it is supposed to update). The test c= ase is as follows Following is the schema to create the table and the trigger connect 'jdbc:derby:wombat;create=3Dtrue'; -- Create the table CREATE TABLE tab ( element_id INTEGER NOT NULL, altered_id =09 VARCHAR(30) NOT NULL, counter SMALLINT NOT NULL DEFAULT 0,=09 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 =3D CASE WHEN (oldt.counter < 32767) THEN (old= t.counter + 1) ELSE 1 END WHERE ((newt.counter is null) or (oldt.counter =3D newt.counter)) AND newt.element_id =3D tab.element_id AND newt.altered_id =3D 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);=20 insert into tab(element_id, altered_id, counter, timets) values (99, '12345= 67890',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 increme= nt the counter column's value from 1 to 2 but it doesn't. The explanation i= s below update tab set timets =3D CURRENT_TIMESTAMP where ELEMENT_ID =3D 99;=20 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 create= d for it UPDATE tab set tab.counter =3D CASE WHEN (CAST (org.apache.derby.iapi.db.Fa= ctory::getTriggerExecutionContext().getOldRow().getObject(3) AS SMALLINT) <= 32767) THEN (CAST (org.apache.derby.iapi.db.Factory::getTriggerExecutionCo= ntext().getOldRow().getObject(3) AS SMALLINT) + 1) ELSE 1 END=20 WHERE ((CAST (org.apache.derby.iapi.db.Factory::getTriggerExecutionContex= t().getNewRow().getObject(3) AS SMALLINT) is null) or (CAST (org.apache.der= by.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getObject(3) A= S SMALLINT) =3D CAST (org.apache.derby.iapi.db.Factory::getTriggerExecution= Context().getNewRow().getObject(3) AS SMALLINT) ))=20 AND CAST (org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().= getNewRow().getObject(1) AS INTEGER) =3D tab.element_id=20 AND CAST (org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().= getNewRow().getObject(2) AS VARCHAR(30)) =3D tab.altered_id=20 As we can see above, the columns are being accessed through column position= s rather than names(this was done as part of DERBY-1258. But the column pos= itions 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.der= by.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(2), = it is getting the column COUNTER rather than the column ALTERED_ID and beca= use of that, the WHERE clause above returns FALSE and hence no row is updat= ed by the trigger action. I will file a jira for this problem and go back to ;looking for a fix for t= he original problem. =20 > Trigger does not recognize new size of VARCHAR column expanded with ALTE= R TABLE. It fails with ERROR 22001: A truncation error was encountered tryi= ng to shrink VARCHAR=20 > -------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------- > > 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 n= ot 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 =3D CASE WHEN (oldt.counter < 32767) THEN (o= ldt.count > er + 1) ELSE 1 END > WHERE ((newt.counter is null) or (oldt.counter =3D newt.counter)) > AND newt.element_id =3D tab.element_id > AND newt.altered_id =3D 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 =3D CURRENT_TIMESTAMP where ELEMENT_ID =3D 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 s= hrink V > ARCHAR '012345678901234567890123456789001234567890' to length 30. > at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLExcepti= on(SQLE > xceptionFactory40.java:79) > at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.ja= va:256) > at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLEx= ception > (TransactionResourceImpl.java:391) > at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleExcep= tion(Tr > ansactionResourceImpl.java:346) > at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Emb= edConne > ction.java:2269) > at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Con= nection > Child.java:81) > at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Emb= edState > ment.java:1321) > at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStateme= nt.java > :625) > at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStateme= nt.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 tryi= ng 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.wrapArgsForTr= ansport > AcrossDRDA(SQLExceptionFactory40.java:119) > at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLExcepti= on(SQLE > xceptionFactory40.java:70) > ... 16 more > Caused by: ERROR 22001: A truncation error was encountered trying to shri= nk VARC > HAR '012345678901234567890123456789001234567890' to length 30. > at org.apache.derby.iapi.error.StandardException.newException(Sta= ndardEx > ception.java:343) > at org.apache.derby.iapi.types.SQLChar.hasNonBlankChars(SQLChar.j= ava:176 > 6) > at org.apache.derby.iapi.types.SQLChar.setWidth(SQLChar.java:1840= ) > at org.apache.derby.exe.ac0b5b0099x012bxf542xab11x0000001bd2983.e= 2(Unkno > wn Source) > at org.apache.derby.impl.services.reflect.DirectCall.invoke(Refle= ctGener > atedClass.java:143) > at org.apache.derby.impl.sql.execute.GenericQualifier.getOrderabl= e(Gener > icQualifier.java:96) > at org.apache.derby.impl.sql.execute.NoPutResultSetImpl.clearOrde= rableCa > che(NoPutResultSetImpl.java:313) > at org.apache.derby.impl.sql.execute.TableScanResultSet.openScanC= ontroll > 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.ope= nCore(P > rojectRestrictResultSet.java:174) > at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.ope= nCore(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(Update= ResultS > et.java:344) > at org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateR= esultSe > t.java:263) > at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt= (Generi > cPreparedStatement.java:436) > at org.apache.derby.impl.sql.GenericPreparedStatement.executeSubS= tatemen > t(GenericPreparedStatement.java:306) > at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.execu= teSPS(G > enericTriggerExecutor.java:173) > at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigg= er(RowT > riggerExecutor.java:111) > at org.apache.derby.impl.sql.execute.TriggerEventActivator.notify= Event(T > riggerEventActivator.java:278) > at org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTri= ggers(U > pdateResultSet.java:817) > at org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateR= esultSe > t.java:280) > at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt= (Generi > cPreparedStatement.java:436) > at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Gen= ericPre > paredStatement.java:317) > at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Emb= edState > ment.java:1232) > ... 10 more > ij> --=20 This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.