Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <22345863.34571289508854266.JavaMail.jira@thor>
Date: Thu, 11 Nov 2010 15:54:14 -0500 (EST)
From: "Mamta A. Satoor (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (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
In-Reply-To: <23021464.129771288310122354.JavaMail.jira@thor>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ https://issues.apache.org/jira/browse/DERBY-4874?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mamta A. Satoor updated DERBY-4874:
-----------------------------------

    Attachment: DERBY4874_not_ready_for_commit_stat.txt
                DERBY4874_not_ready_for_commit_diff.txt

I have a patch which is not ready for commit yet because of a failure I still need to investigate. The failure is in lang/predicatesIntoViews.sql and it seems like that the trigger action plan is getting regenerated with wrong column numbers. I need to narrow down the test to a form where it is easier to debug. The part of the failure looks as follows
$ java org.apache.derbyTesting.functionTests.harness.RunTest lang/predicatesIntoViews.sql
derby.optimizer.noTimeout=true
*** Start: predicatesIntoViews jdk1.6.0 2010-11-11 11:52:34 ***
4739a4740,4756
> ERROR 38000: The exception 'java.sql.SQLException: Column '3' not found.' was thrown while evaluating an expression.
> ERROR S0022: Column '3' not found.

I wanted to put out the patch in case anybody had comments on the general approach of the fix. Once I have the test failure fixed, I will try to put as much of the code as possible may be in DataDictionary and have both CreateTriigerNode and TriggerDescriptor call that code rather than have the duplication of the code in two places as it being 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_stat.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.