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