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