db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5121) Data corruption when executing an UPDATE trigger
Date Mon, 21 Mar 2011 20:22:06 GMT

    [ https://issues.apache.org/jira/browse/DERBY-5121?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13009350#comment-13009350

Mamta A. Satoor commented on DERBY-5121:

I just wanted to summarize the problem here to make it easier to understand the issue and
the solution I am working on.

Some background information : With DERBY-1482, we decided to read only the columns really
needed during trigger execution. Trigger only knows about trigger columns and columns in it's
trigger action used through the REFERENCING clause. And so DERBY-1482 made the assumption
that those will be the only columns read from the trigger table and it uses the relative column
positions in that resultset to access the columns in it's trigger action when we generate
the internal sql for the trigger action. The problem is that there can be cases when the SQL
causing the trigger to fire needs to read more columns than just what the trigger needs. eg
create table t1( id int, name varchar( 50 ) );
create table t2
	name	varchar( 50 )	not null,
	description	int	not null,
	id	integer
insert into t2( name, description ) values ( 'Foo Name', 0 );

create trigger t2UpdateTrigger
after UPDATE of name
on t2
new row as nr
for each ROW
insert into t1 values ( nr.id, nr.name );

The trigger above only needs columns "name" and "id" from the trigger table and hence it will
assume that the runtime resultset will have just those 2 columns, "name" as first column in
the resultset and "id" as the 2nd column in the resultset and using that assumption, it will
change trigger action sql "insert into t1 values ( nr.id, nr.name )" to following "insert
into t1 values ( CAST(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(2)
AS INTEGER) , CAST (org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(1)
AS VARCHAR(50))  )

But the following triggering sql needs more columns than just  "name" and "id", it also needs
"description".(The list of columns required by triggering sql are columns used by the triggering
sql and columns needed by the triggers which will get fired).
update t2 set name = 'Another name' , description = 1;
So the runtime resulset will end up having columns "name", "description"  and "id". So the
column "id" is the 3rd column in the resultset and not 2nd column in the resultset as expected
by the trigger.

The solution I am working on is to see if we can map out only the columns needed by the trigger
from the actual runtime resulset created by the triggering sql. 

> 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:,
>            Reporter: Rick Hillegas
>            Assignee: Mamta A. Satoor
>              Labels: derby_triage10_8
>         Attachments: DummyProc.java, Test_5121.java, derby5121_patch1_diff.txt, triggerBug.sql,
triggerBug.sql, triggeredBug2.sql, triggeredCorruption.sql
> When executing an UPDATE trigger, the following error is raised. I will attach a test
> 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

View raw message