[
https://issues.apache.org/jira/browse/DERBY-6443?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Knut Anders Hatlen reassigned DERBY-6443:
-----------------------------------------
Assignee: Knut Anders Hatlen
> ArrayIndexOutOfBoundsException when calling function from trigger
> -----------------------------------------------------------------
>
> Key: DERBY-6443
> URL: https://issues.apache.org/jira/browse/DERBY-6443
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.0.2.1, 10.10.1.1
> Reporter: Tim Dudgeon
> Assignee: Knut Anders Hatlen
>
> 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.
> {code}
> -- 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;
> {code}
--
This message was sent by Atlassian JIRA
(v6.1.5#6160)
