[
https://issues.apache.org/jira/browse/DERBY-4874?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12933934#action_12933934
]
Mamta A. Satoor commented on DERBY-4874:
----------------------------------------
Thanks, Knut. I will gave that a try.
I was able to narrow done lang/predicatesIntoViews.sql problem to following sql
connect 'jdbc:derby:wombat;create=true';
create table xr.repositoryobjectversion (
id varchar(48) not null primary key,
resourceid varchar(48) not null,
name varchar(128) not null,
uname varchar(128),
versionName varchar(128) not null
);
create trigger xr.rov_uname_i after insert on xr.repositoryobjectversion
referencing new as n
for each row
update xr.repositoryobjectversion set uname = upper( n.name ) where name =
n.name;
create trigger xr.rov_unane_u after update of name, uname on
xr.repositoryobjectversion
referencing new as n
for each row
update xr.repositoryobjectversion set uname = upper( n.name )
where name = n.name and uname <> upper( n.name );
create unique index xr.versionname on xr.repositoryobjectversion (resourceid,
versionName);
The last sql which is creating the index invalidates the trigger and when we
regenerate the action plan, we are regenerating the sql with the actual column
number in the table rather than the relative column number of that column in
the resultset that will be generated for the trigger. As a next step, I will
debug this to see why the regenerated sql does not have the correct column
number. Once I am done with this, I will work on reworking the code to put the
common code in DataDictionary rather than the current duplication in
CreateTriggerNode and TriggerDescriptor as done in my patch.
> 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: 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, 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.
