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) >>> >> >> >
