[
https://issues.apache.org/jira/browse/DERBY-5121?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13006651#comment-13006651
]
Mamta A. Satoor commented on DERBY-5121:
----------------------------------------
Frist of all, thanks so much for having such an easy reproducible test case.
I looked at the generated code for the trigger action and see the problem Rick
described. The purpose of DERBY-1482 was to avoid reading the columns that are
not needed by the trigger action, especially the LOB columns which could result
in OOM problems. The code that I added for DERBY-1482 looked at the columns
referenced in trigger action and *assumed* that the resultset would have just
those columns during the trigger execution time. So, in following example, the
trigger action references only 2 out of the 3 columns, the 1st and the 3rd
column. The trigger action thus assumes that the resultset would have just
columns 1 and 3 in the resultset's column positions 1 and 2. But it did not
take into account where the triggering SQL might need additional columns of
it's own. In the given example, the triggering UPDATE statement is using the
column in position 2 from the trigger table and this causes the trigger action
to start using the columns in incorrect column positions.
I understand the urgency of this jira and based on that, I will see if I can
disable the optimization introduced by DERBY-1482. Once that is done, I will
look into what could be other ways to solve the unnecessary column reads
algorithm. One thing that comes to my mind is to may be read all the columns
upto the highest column position required by the trigger action. So in our eg,
triggeredCorruption.sql, the trigger action is looking at column positions 1
and 3 and hence we should read columns 1, 2 and 3 even though the trigger
action does not need the column 2. Any columns after the highest column
position required by the trigger action will be read if the triggering SQL
requires it but it will not affect the column positions getting used inside the
trigger action.
I will also think of any other possible way to tackle the column reading
optimization. If anyone has any other ideas, please let me know.
> Data corruption when executing an UPDATE trigger
> ------------------------------------------------
>
> Key: DERBY-5121
> URL: https://issues.apache.org/jira/browse/DERBY-5121
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.7.1.1, 10.8.0.0
> Reporter: Rick Hillegas
> Assignee: Mamta A. Satoor
> Labels: derby_triage10_8
> Attachments: DummyProc.java, triggerBug.sql, triggerBug.sql,
> triggeredCorruption.sql
>
>
> When executing an UPDATE trigger, the following error is raised. I will
> attach a test case:
> ERROR XCL12: An attempt was made to put a data value of type
> 'org.apache.derby.impl.jdbc.EmbedClob' into a data value of type 'INTEGER'.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
