[jira] [Commented] (DERBY-6443) ArrayIndexOutOfBoundsException when calling function from trigger

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

    [ 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)