You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Tim Dudgeon <td...@informaticsmatters.com> on 2013/12/27 13:05:53 UTC
ArrayIndexOutOfBoundsException when calling function from trigger
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?
Tim
Re: ArrayIndexOutOfBoundsException when calling function from trigger
Posted by Tim Dudgeon <td...@informaticsmatters.com>.
On 30/12/2013 20:26, Knut Anders Hatlen wrote:
> Not really, but it definitely looks like bug. Could you file a bug
> report
Here is the JIRA issue:
https://issues.apache.org/jira/browse/DERBY-6443
Re: ArrayIndexOutOfBoundsException when calling function from trigger
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Tim Dudgeon <td...@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