[
https://issues.apache.org/jira/browse/DERBY-6443?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13863061#comment-13863061
]
Knut Anders Hatlen commented on DERBY-6443:
-------------------------------------------
InsertNode.bindStatement() binds the underlying SelectNode twice. First here:
{code}
/* If any underlying ResultSetNode is a SelectNode, then we
* need to do a full bind(), including the expressions
* (since the fromList may include a FromSubquery).
*/
DataDictionary dataDictionary = getDataDictionary();
super.bindResultSetsWithTables(dataDictionary);
{code}
Then here:
{code}
/* Bind the expressions now that the result columns are bound
* NOTE: This will be the 2nd time for those underlying
ResultSets
* that have tables (no harm done), but it is necessary for
those
* that do not have tables. It's too hard/not work the effort
to
* avoid the redundancy.
*/
super.bindExpressions();
{code}
The first time the node is bound, the AggregateNode (for the AVG function) is
found and added to SelectNode.selectAggregates. The second time it is bound,
the original selectAggregates list is replaced with a fresh, empty list first.
For some reason, the AggregateNode is not found in this round, and the
selectAggregates list stays empty. Because of the empty selectAggregates list,
some transformations required for queries with aggregates are not performed,
which eventually leads to assert failures or IndexOutOfBoundsExceptions.
As a quick hack, I changed SelectNode.bindExpressions() so that it didn't drop
the original selectAggregates list the second time it was called. This made the
queries succeed. To fix it properly, I think we need to find out why the
AggregateNode is not found the second time the SelectNode is bound, and
possibly make adjustments so that it is found.
> 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
>
> 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)
