db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Abhinav Gupta <abhinavgupta2...@gmail.com>
Subject Re: [jira] [Updated] (DERBY-6783) WHEN clause in CREATE TRIGGER for UPDATE is not working for the sql script below
Date Wed, 17 Jun 2015 13:57:41 GMT
Hi Bryan,

I tried to find the reason for truncation error in the code but I couldn't
find any reason for it. Later I shifted my focus on the queries and I added
a new test that is similar in structure with the previous test I wrote.

And I noticed that for testDerby6783_1_1 if I change my query to

UPDATE tabDerby6783_1_1 SET newrow.TOTAL_MARKS

I don't get the truncation error. I get a different error saying

ERROR 42X55: Table name 'NEWROW' should be the same as 'TABDERBY6783_1_1'.

The unusual behavior that I noticed was when I run the testDerby6783_1_2,
which is a bit similar in structure. Here if I run the query

UPDATE tabDerby6783_1_2 SET FINAL_GRADE

The test passes and the query works perfectly without any kind of error.

I'm writing more queries to understand better, as of now it seems the patch
works well for not so complex queries and solves Derby-6783.



On Wed, Jun 17, 2015 at 3:21 AM, Abhinav Gupta <abhinavgupta2004@gmail.com>
wrote:

> Hi Bryan,
>
> I was trying to understand the truncation error, I changed the values of
> the field marks1 and marks2 in the derbyTest-6783_1 and that '20' is
> basically the value of the integer field marks1 that I think is being cast
> as a char value. But I haven't been able to track where this error is
> occurring.
>
>
> On Fri, Jun 12, 2015 at 6:42 AM, Bryan Pendleton (JIRA) <jira@apache.org>
> wrote:
>
>>
>>      [
>> https://issues.apache.org/jira/browse/DERBY-6783?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
>> ]
>>
>> Bryan Pendleton updated DERBY-6783:
>> -----------------------------------
>>     Attachment: 6783_newTest_bryan.diff
>>
>> Hi Abhinav, I spent some time today studying your latest patch.
>>
>> I think we are making progress!
>>
>> I made a few small changes to your patch, and have attached mine.
>>
>> 1) In CreateTriggerNode, I removed the "whenCols" variable, and just
>>    used the "cols" variable in both places, because I think we want to
>>    combine the two sets of variables into one list.
>>
>> 2) In DataDictionaryImpl.examineTriggerNodeAndCols(), I added a
>>    couple lines of code to initialize triggerColsAndTriggerActionCols
>>    from referencedColsInTriggerAction, if it is passed, so that the
>>    columns we computed in the first invocation get carried through
>>    into the second invocation.
>>
>> 3) In DataDictionaryImpl.getTriggerActionString(), I changed the
>>    handling of "cols = null". We still want to execute most of the code
>>    in this method even if cols = null, but in that case we need to
>>    set triggerColsAndTriggerActionCols to contain the entire set of
>>    columns in the table.
>>
>> The overall set of changes from your patch to mine was very small;
>> the easiest way to see this is to "diff the diffs" -- that is, download
>> your patch and my patch and put them together in a directory
>> and diff them.
>>
>> With the patch that I've attached, we now pass all of the test
>> cases in TriggerWhenClauseTest except for the new "testDerby6783_1"
>> test, which I think is a brand new test that you have just added. That
>> test gets the error:
>>
>> ERROR 22001: A truncation error was encountered trying to shrink CHAR
>> '20' to length 1.
>>
>> So clearly there are still some issues with our patch, but I felt like
>> this was good progress and wanted to share it with you.
>>
>> Do try to download my latest patch and let me know what you think!
>>
>> > WHEN clause in CREATE TRIGGER for UPDATE is not working for the sql
>> script below
>> >
>> --------------------------------------------------------------------------------
>> >
>> >                 Key: DERBY-6783
>> >                 URL: https://issues.apache.org/jira/browse/DERBY-6783
>> >             Project: Derby
>> >          Issue Type: Bug
>> >          Components: SQL
>> >    Affects Versions: 10.11.1.1
>> >            Reporter: Mamta A. Satoor
>> >            Assignee: Abhinav Gupta
>> >         Attachments: 6783.diff, 6783_newTest.diff,
>> 6783_newTest_bryan.diff, diagnostics.diff, sortFunction.diff,
>> testTriggerWhenClause.diff
>> >
>> >
>> > Following sql script was shared on derby-user(
>> http://mail-archives.apache.org/mod_mbox/db-derby-user/201412.mbox/%3c548ABA6D.8000509@zoho.com%3e
>> ).
>> > The UPDATE TRIGGER  with the WHEN clause below does not fire as
>> expected. Same script works fine on DB2.
>> > ij version 10.11
>> >  ij> connect 'jdbc:derby:MyDbTest;create=true';
>> >  ij> CREATE TABLE t1 (id INTEGER, done_date DATE, status CHAR(1));
>> >  0 rows inserted/updated/deleted
>> >  ij> CREATE TRIGGER tr1 AFTER UPDATE OF status ON t1 REFERENCING NEW AS
>> newrow FOR EACH ROW WHEN (newrow.status='d') UPDATE t1 SET
>> done_date=current_date WHERE id=newrow.id;
>> >  0 rows inserted/updated/deleted
>> >  ij> insert into t1 values (1, null, 'a');
>> >  1 row inserted/updated/deleted
>> >  ij> SELECT * FROM t1;
>> >  ID         |DONE_DATE |STA&
>> >  ---------------------------
>> >  1          |NULL      |a
>> >
>> >  1 row selected
>> >  ij> UPDATE t1 SET status='d';
>> >  1 row inserted/updated/deleted
>> >  ij> SELECT * FROM t1;
>> >  ID         |DONE_DATE |STA&
>> >  ---------------------------
>> >  1          |NULL      |d
>> >
>> >  1 row selected
>> >  ij> exit;
>>
>>
>>
>> --
>> This message was sent by Atlassian JIRA
>> (v6.3.4#6332)
>>
>
>

Mime
View raw message