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] 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
Date Wed, 03 Nov 2010 21:50:24 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 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 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.

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 difference through the system tables.
SYSTRIGGERS keeps track of the columns that are referenced in the trigger action. Those columns
are later used at trigger execution time to decide which columns from the trigger table need
to be actually fetched into memory. The column tracking is done through the column 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
recalculate the column positions of the trigger action columns and then regenerate the 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 position, we just want to regenerate the SQL
for SPSDescriptor and then compile that new query. For our original case, there is no need
to recalculate the column 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 action is referenecing to old and new column
values of the row. For all other trigger 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 for 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 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