Tim Dudgeon <[email protected]> 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
