Uploaded image for project: 'Derby'
  1. Derby
  2. 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

    XMLWordPrintableJSON

    Details

    • Issue & fix info:
      High Value Fix, Release Note Needed, Repro attached

      Description

      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>

        Attachments

        1. trigAlterColumn.sql
          1 kB
          Katherine Marsden
        2. releaseNote.html
          3 kB
          Mamta A. Satoor
        3. droptable.diff
          0.9 kB
          Knut Anders Hatlen
        4. DERBY4874_not_ready_for_commit_stat.txt
          0.3 kB
          Mamta A. Satoor
        5. DERBY4874_not_ready_for_commit_stat_ver2.txt
          0.3 kB
          Mamta A. Satoor
        6. DERBY4874_not_ready_for_commit_diff.txt
          15 kB
          Mamta A. Satoor
        7. DERBY4874_not_ready_for_commit_diff_ver2.txt
          17 kB
          Mamta A. Satoor
        8. DERBY4874_comments_and_test_missing_stat_ver3.txt
          0.5 kB
          Mamta A. Satoor
        9. DERBY4874_comments_and_test_missing_diff_ver3.txt
          56 kB
          Mamta A. Satoor
        10. DERBY4874_106_backport_stat_ver4.txt
          0.5 kB
          Mamta A. Satoor
        11. DERBY4874_106_backport_diff_ver4.txt
          38 kB
          Mamta A. Satoor
        12. derby.log
          9 kB
          Knut Anders Hatlen

          Issue Links

            Activity

              People

              • Assignee:
                mamtas Mamta A. Satoor
                Reporter:
                kmarsden Katherine Marsden
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: