[
https://issues.apache.org/jira/browse/DERBY-6443?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13860235#comment-13860235
]
Knut Anders Hatlen commented on DERBY-6443:
-------------------------------------------
When running in a debug build, it fails already in the CREATE TRIGGER statement:
{quote}
ERROR XJ001: Java exception: 'ASSERT FAILED sourceResultSetNumber expected to
be >= 0 for null.SQLCol3: org.apache.derby.shared.common.sanity.AssertFailure'.
{quote}
A similar, simpler trigger produces another assert failure, which seems to give
some more details about what has gone wrong:
{noformat}
ij> create table t1(x int);
0 rows inserted/updated/deleted
ij> create table t2(x int);
0 rows inserted/updated/deleted
ij> create trigger tr after insert on t1 insert into t2 select floor(avg(x))
from t1;
ERROR XJ001: Java exception: 'ASSERT FAILED generateExpression() should never
be called on an AggregateNode. replaceAggregatesWithColumnReferences should
have been called prior to generateExpression:
org.apache.derby.shared.common.sanity.AssertFailure'.
{noformat}
> ArrayIndexOutOfBoundsException when calling function from trigger
> -----------------------------------------------------------------
>
> Key: DERBY-6443
> URL: https://issues.apache.org/jira/browse/DERBY-6443
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.10.1.1
> Reporter: Tim Dudgeon
>
> 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)
