db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] Commented: (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
Date Wed, 03 Nov 2010 20:48:25 GMT

    [ 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.


Mime
View raw message