db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kathey Marsden (JIRA)" <j...@apache.org>
Subject [jira] Created: (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 Thu, 28 Oct 2010 23:55:22 GMT
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.6.2.1, 10.5.3.0, 10.7.1.0
            Reporter: Kathey Marsden


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