Hi Bryan, I spent quite some time on this bug today and there was a slight problem with my sort function which I corrected, but from what I infer from the print statements, I have restructured the "triggerColsAndTriggerActionCols" such that ID has a value 2 for the case when both ID and STATUS are retrieved. Though I couldn't put print statements that could print the value of STATUS of confirm it is 1 (My print statement would just print the value of ID and I couldn't actually understand why is wasn't printing STATUS) but when I dry run it, it turns out to be 1.
And for the case where only STATUS is retrieved, the value in " triggerColsAndTriggerActionCols" is indeed 1. So it should have solved the problem in theory, I haven't yet understood where this logic fails. On Tue, Jun 2, 2015 at 2:15 AM, Abhinav Gupta <[email protected]> wrote: > Hi Bryan, > > I tried implementing the sort logic and I've attached a patch for it. I > think the bug still hasn't been solved, I'll try to understand where I'm > going wrong with this logic or if there is a mistake with the > implementation. > > On Tue, Jun 2, 2015 at 1:21 AM, Abhinav Gupta <[email protected]> > wrote: > >> I'm sorry, I clicked on send by mistake without completing the mail, >> >> So a possible solution that I have thought of is that if the arrays >> - triggerActionColsOnly >> - triggerColsAndTriggerActionCols >> are different then I will sort the triggerColsAndTriggerActionCols array >> such that the elements that are present in triggerColsAndTriggerActionCols >> are but absent in triggerActionColsOnly are placed before the other >> elements. >> This way, the relative column position will be same for columns appearing >> in both calls of getTriggerActionString(). >> >> On Tue, Jun 2, 2015 at 1:16 AM, Abhinav Gupta <[email protected] >> > wrote: >> >>> Hi Bryan, >>> >>> //Now that we know what columns we need for trigger >>> columns and >>> //trigger action columns, we can get rid of remaining -1 entries >>> //for the remaining columns from trigger table. >>> //eg >>> //CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1 >>> // REFERENCING OLD AS oldt NEW AS newt >>> // FOR EACH ROW UPDATE table2 SET c24=oldt.c14; >>> //For the above trigger, before the justTheRequiredColumns() call, >>> //the content of triggerColsAndTriggerActionCols array were as >>> //follows [-1, 2, -1, 4, -1] >>> //After the justTheRequiredColumns() call below, >>> //triggerColsAndTriggerActionCols will have [2,4]. What this means >>> //that, at run time, during trigger execution, these are the only >>> //2 column positions that will be read into memory from the >>> //trigger table. The columns in other column positions are not >>> //needed for trigger execution. >>> triggerColsAndTriggerActionCols = justTheRequiredColumns( >>> triggerColsAndTriggerActionCols, triggerTableDescriptor); >>> >>> Till this point things seem to be fine enough, when I dry run the code >>> value of triggerColsAndTriggerActionCols is >>> >>> [2,3] for UPDATE t1 SET done_date=current_date WHERE id=newrow.id; & AFTER >>> UPDATE OF status ON t1 >>> >>> And >>> >>> [3] for WHEN (newrow.status='d') & AFTER UPDATE OF status ON t1 >>> >>> The problem arises when the relative position is calculated >>> >>> if (in10_9_orHigherVersion && >>> triggerColsAndTriggerActionCols != null){ >>> for (int j=0; j<triggerColsAndTriggerActionCols.length; j++){ >>> if (triggerColsAndTriggerActionCols[j] == colPositionInTriggerTable) >>> colPositionInRuntimeResultSet=j+1; >>> } >>> } >>> Now the values become >>> >>> [1,2] for UPDATE t1 SET done_date=current_date WHERE id=newrow.id; & AFTER >>> UPDATE OF status ON t1 >>> >>> And >>> >>> [1] for WHEN (newrow.status='d') & AFTER UPDATE OF status ON t1 >>> >>> So a possible solution that I have thought of is that if the arrays >>> >>> >>> On Sat, May 30, 2015 at 1:11 AM, Bryan Pendleton (JIRA) <[email protected] >>> > wrote: >>> >>>> >>>> [ >>>> https://issues.apache.org/jira/browse/DERBY-6783?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14565277#comment-14565277 >>>> ] >>>> >>>> Bryan Pendleton commented on DERBY-6783: >>>> ---------------------------------------- >>>> >>>> My theory is that the root cause of this bug is the following code from >>>> CreateTriggerNode.bindReferencesClause(): >>>> >>>> //Now that we have verified that are no invalid column >>>> references >>>> //for trigger columns, let's go ahead and transform the >>>> OLD/NEW >>>> //transient table references in the trigger action sql. >>>> transformedActionText = >>>> getDataDictionary().getTriggerActionString(a >>>> ctionNode, >>>> oldTableName, >>>> newTableName, >>>> originalActionText, >>>> referencedColInts, >>>> referencedColsInTriggerAction, >>>> actionNode.getBeginOffset(), >>>> triggerTableDescriptor, >>>> triggerEventMask, >>>> true, >>>> actionTransformations); >>>> >>>> // If there is a WHEN clause, we need to transform its text >>>> too. >>>> if (whenClause != null) { >>>> transformedWhenText = >>>> getDataDictionary().getTriggerActionString( >>>> whenClause, oldTableName, newTableName, >>>> originalWhenText, referencedColInts, >>>> referencedColsInTriggerAction, >>>> whenClause.getBeginOffset(), >>>> triggerTableDescriptor, triggerEventMask, >>>> true, >>>> whenClauseTransformations); >>>> } >>>> >>>> Note that this code calls getTriggerActionString() twice. >>>> >>>> The first time, we are working with the Trigger "action node", which >>>> references the column "id": >>>> >>>> UPDATE t1 SET done_date=current_date WHERE id=newrow.id; >>>> >>>> and also references the column "status": >>>> >>>> AFTER UPDATE OF status ON t1 >>>> >>>> The second time, we are working with the Trigger "when node", which >>>> references the column "status": >>>> >>>> WHEN (newrow.status='d') >>>> >>>> and also references the column "status": >>>> >>>> AFTER UPDATE OF status ON t1 >>>> >>>> The net result is that, the first time through, we determine that we >>>> will have two columns (id, status), >>>> but the second time through, we incorrectly determine that the 'newrow' >>>> will have only one column (status), >>>> and therefore we generate the reference to column 1, thinking we will >>>> be referencing the column "status", >>>> but instead we end up referencing the column "id". >>>> >>>> To fix this bug, we have to somehow change the compilation of the >>>> trigger so that it >>>> computes the set of columns that we will need in 'newrow' exactly once, >>>> and use that >>>> set of columns for compiling both the action node and the when node, >>>> instead of >>>> computing their column sets independently. >>>> >>>> >>>> > 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: diagnostics.diff, testTriggerWhenClause.diff >>>> > >>>> > >>>> > Following sql script was shared on derby-user( >>>> http://mail-archives.apache.org/mod_mbox/db-derby-user/201412.mbox/%[email protected]%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) >>>> >>> >>> >> >
