db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: ArrayIndexOutOfBoundsException when calling function from trigger
Date Mon, 30 Dec 2013 20:26:25 GMT
Tim Dudgeon <tdudgeon@informaticsmatters.com> writes:

> I'm having problems will calling functions from within a trigger.
> The problem seems to be with the outer function call (FLOOR() in this
> case, but it also happens with other functions). It works fine in the
> SELECT statement, but when used in the trigger it throws a
> ArrayIndexOutOfBoundsException.
> Remove the FLOOR() part from the trigger and it works fine.
>
>
>
> -- create source table and some data
> CREATE TABLE foo (name VARCHAR(20), val DOUBLE);
> INSERT INTO foo (name, val) VALUES ('A', 10), ('A', 20), ('B', 30),
> ('C', 40);
>
> -- calling the function works fine here
> SELECT name, FLOOR(AVG(LOG10(val))), COUNT(*)
> FROM foo
> GROUP BY name;
>
> -- create target table for trigger
> CREATE TABLE summary (name VARCHAR(20), aver DOUBLE, size INT);
>
> -- create the trigger
> CREATE TRIGGER trg_foo AFTER INSERT ON foo
> REFERENCING NEW TABLE AS changed FOR EACH STATEMENT MODE DB2SQL
> INSERT INTO summary (name, aver, size)
> SELECT name, FLOOR(AVG(LOG10(val))), COUNT(*)
> FROM changed
> GROUP BY name;
>
> -- insert rows to cause trigger to fire
> INSERT INTO foo (name, val) VALUES ('A', 10), ('A', 20), ('B', 30),
> ('C', 40);
>
> SELECT * FROM foo;
> SELECT * FROM summary;
>
>
> Any ideas?

Not really, but it definitely looks like bug. Could you file a bug
report at https://issues.apache.org/jira/browse/DERBY so that we can
track it?

Thanks,

-- 
Knut Anders

Mime
View raw message