Return-Path: X-Original-To: apmail-db-derby-dev-archive@www.apache.org Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 1060A187B3 for ; Fri, 19 Jun 2015 21:29:41 +0000 (UTC) Received: (qmail 29715 invoked by uid 500); 19 Jun 2015 21:29:40 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 29688 invoked by uid 500); 19 Jun 2015 21:29:40 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 29678 invoked by uid 99); 19 Jun 2015 21:29:40 -0000 Received: from Unknown (HELO spamd2-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 19 Jun 2015 21:29:40 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd2-us-west.apache.org (ASF Mail Server at spamd2-us-west.apache.org) with ESMTP id 13E631A5D92 for ; Fri, 19 Jun 2015 21:29:40 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd2-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 3.15 X-Spam-Level: *** X-Spam-Status: No, score=3.15 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, FREEMAIL_ENVFROM_END_DIGIT=0.25, HTML_MESSAGE=3, SPF_PASS=-0.001, URIBL_BLOCKED=0.001] autolearn=disabled Authentication-Results: spamd2-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=gmail.com Received: from mx1-us-west.apache.org ([10.40.0.8]) by localhost (spamd2-us-west.apache.org [10.40.0.9]) (amavisd-new, port 10024) with ESMTP id cRNSuw1VQH0G for ; Fri, 19 Jun 2015 21:29:32 +0000 (UTC) Received: from mail-ie0-f174.google.com (mail-ie0-f174.google.com [209.85.223.174]) by mx1-us-west.apache.org (ASF Mail Server at mx1-us-west.apache.org) with ESMTPS id 56F1327610 for ; Fri, 19 Jun 2015 21:29:32 +0000 (UTC) Received: by iefd2 with SMTP id d2so26373900ief.2 for ; Fri, 19 Jun 2015 14:29:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :content-type; bh=wCEGBPxsjHkBguFESeTcct0plv8DQ76RbKJYF8jtWYQ=; b=hhOX6gT4zM/ecTcH3PIkGdyweR7QmuCM5UbAsSfGADs1VD3CA18802oi9p2PPnr/1D LluEw99ZM1RNEby8nhC9jfb4YvRG43QkVv2rW847reT3I5dgMOsRo05aK+GYyGSQbPwE xgrVCKDbrOVzgx9cG4j3Uc3cWjS4ktfAkMB3MuP/H7HBYc6eQz/ulVUY/aeCIHKBbA14 Ol4sbBXgQYjSRQyLbcBfPytfzJb4/jhQdV7zlDWLJGsvTv/vohdjl/EagVDV2tMGkyTw VI71fatse/wCdM8QSZ2FK5EVkv5stszfo/EmD0Lhi6BmSnLV2+4djBJxBNvl3lOKyTkr rOtg== X-Received: by 10.42.93.17 with SMTP id v17mr14644579icm.42.1434749365252; Fri, 19 Jun 2015 14:29:25 -0700 (PDT) MIME-Version: 1.0 Received: by 10.36.144.193 with HTTP; Fri, 19 Jun 2015 14:29:05 -0700 (PDT) In-Reply-To: References: From: Abhinav Gupta Date: Sat, 20 Jun 2015 02:59:05 +0530 Message-ID: Subject: Re: [jira] [Updated] (DERBY-6783) WHEN clause in CREATE TRIGGER for UPDATE is not working for the sql script below To: derby-dev@db.apache.org Content-Type: multipart/alternative; boundary=90e6ba614ce641f7f00518e59e63 --90e6ba614ce641f7f00518e59e63 Content-Type: text/plain; charset=UTF-8 Hi Bryan, The reason I had added the sortTriggerColsAndTriggerAction was that, earlier when we had not separated the getTriggerActionString, during the first call of getTriggerActionString, the array triggerColsAndTriggerAction had values [1,2] where 1 referenced to the ID field and 2 referenced the STATUS. In the second call, the array had the value [1] and it referenced status. The the function that I had added, would put STATUS first in the array during the first call. And would have given STATUS the same column index of 1 when relative column positions are calculated. I think my logic was flawed since all test pass after removing it. There was one test that failed testDerby6783_2 but I just realized that it was due to a typo in the test query. On Fri, Jun 19, 2015 at 10:50 AM, Bryan Pendleton (JIRA) 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_moreTests_bryan.diff > > Slightly-tweaked copy of 6783_moreTests.diff; see the discussion > on derby-dev for some background about the change related to > sortTriggerColsAndTriggerActionCols() > > > > 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_moreTests.diff, > 6783_moreTests_bryan.diff, 6783_newTest.diff, 6783_newTest_bryan.diff, > 6783_newTests.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) > --90e6ba614ce641f7f00518e59e63 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi Bryan,

The reason I had added the so= rtTriggerColsAndTriggerAction was that, earlier when we had not separated t= he getTriggerActionString, during the first call of getTriggerActionString,= the array triggerColsAndTriggerAction had values [1,2]
where 1 r= eferenced to the ID field and 2 referenced the STATUS.

=
In the second call, the array had the value [1] and it referenced stat= us.

The the function that I had added, would put S= TATUS first in the array during the first call. And would have given STATUS= the same column index of 1 when relative column positions are calculated.= =C2=A0

I think my logic was flawed since all test = pass after removing it. There was one test that failed testDerby6783_2 but = I just realized that it was due to a typo in the test query.=C2=A0

On Fri, Jun 19= , 2015 at 10:50 AM, Bryan Pendleton (JIRA) <jira@apache.org> w= rote:

=C2=A0 =C2=A0 =C2=A0[ https://issues.apache.org/jira/browse= /DERBY-6783?page=3Dcom.atlassian.jira.plugin.system.issuetabpanels:all-tabp= anel ]

Bryan Pendleton updated DERBY-6783:
-----------------------------------
=C2=A0 =C2=A0 Attachment: 6783_moreTests_bryan.diff

Slightly-tweaked copy of 6783_moreTests.diff; see the discussion
on derby-dev for some background about the change related to
=C2=A0sortTriggerColsAndTriggerActionCols()


> WHEN clause in CREATE TRIGGER for UPDATE is not working for the sql sc= ript below
> ----------------------------------------------------------------------= ----------
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Key: DERB= Y-6783
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0URL: https://issues.apache.org/jira/browse/DERBY-6783
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Project: Derby
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Issue Type: Bug
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Components: SQL
>=C2=A0 =C2=A0 Affects Versions: 10.11.1.1
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Reporter: Mamta A. Satoor
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Assignee: Abhinav Gupta
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Attachments: 6783.diff, 6783_moreTest= s.diff, 6783_moreTests_bryan.diff, 6783_newTest.diff, 6783_newTest_bryan.di= ff, 6783_newTests.diff, diagnostics.diff, sortFunction.diff, testTriggerWhe= nClause.diff
>
>
> Following sql script was shared on derby-user(http://mail-archives.apach= e.org/mod_mbox/db-derby-user/201412.mbox/%3c548ABA6D.8000509@zoho.com%3e).
> The UPDATE TRIGGER=C2=A0 with the WHEN clause below does not fire as e= xpected. Same script works fine on DB2.
> ij version 10.11
>=C2=A0 ij> connect 'jdbc:derby:MyDbTest;create=3Dtrue';
>=C2=A0 ij> CREATE TABLE t1 (id INTEGER, done_date DATE, status CHAR(= 1));
>=C2=A0 0 rows inserted/updated/deleted
>=C2=A0 ij> CREATE TRIGGER tr1 AFTER UPDATE OF status ON t1 REFERENCI= NG NEW AS newrow FOR EACH ROW WHEN (newrow.status=3D'd') UPDATE t1 = SET done_date=3Dcurrent_date WHERE id=3Dnewrow.id;
>=C2=A0 0 rows inserted/updated/deleted
>=C2=A0 ij> insert into t1 values (1, null, 'a');
>=C2=A0 1 row inserted/updated/deleted
>=C2=A0 ij> SELECT * FROM t1;
>=C2=A0 ID=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|DONE_DATE |STA&
>=C2=A0 ---------------------------
>=C2=A0 1=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |NULL=C2=A0 =C2=A0 =C2=A0 |a=
>
>=C2=A0 1 row selected
>=C2=A0 ij> UPDATE t1 SET status=3D'd';
>=C2=A0 1 row inserted/updated/deleted
>=C2=A0 ij> SELECT * FROM t1;
>=C2=A0 ID=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|DONE_DATE |STA&
>=C2=A0 ---------------------------
>=C2=A0 1=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |NULL=C2=A0 =C2=A0 =C2=A0 |d=
>
>=C2=A0 1 row selected
>=C2=A0 ij> exit;



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

--90e6ba614ce641f7f00518e59e63--