[DERBY-2016] ArrayIndexOutOfBoundsException for COALESCE with aggregate functions

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.2.1.6, 10.2.2.0, 10.3.1.4
• Fix Version/s: 10.3.2.1, 10.4.1.3
• Component/s: SQL
• Environment: 1.5.0_06-b05
• Assignee: Dag H. Wanvik
• Reporter: Christian d'Heureuse
• Created: 29/Oct/06 14:57
• Updated: 05/May/08 15:07
• Resolved: 22/Nov/07 00:53

Description

The following statements produce an ArrayIndexOutOfBoundsException:

CREATE TABLE t1 (
f1 INTEGER);

SELECT COALESCE(MAX(f1),0) FROM t1;

Workaround:
VALUES COALESCE( (SELECT MAX(f1) FROM t1), 0);

Stack trace:
----------------------------------------------------------------
2006-10-29 14:52:53.765 GMT:
Booting Derby version The Apache Software Foundation - Apache Derby - 10.2.1.6 - (452058): instance c013800d-010e-948f-0faa-00000012f418
on database directory C:\temp_sys\temp_Derby_TestErr_db

Database Class Loader started - derby.database.classpath=''
2006-10-29 14:53:02.906 GMT Thread[main,5,main] (XID = 122), (SESSIONID = 0), (DATABASE = c:\temp_sys\temp_Derby_TestErr_db), (DRDAID = null), Cleanup action starting
2006-10-29 14:53:02.906 GMT Thread[main,5,main] (XID = 122), (SESSIONID = 0), (DATABASE = c:\temp_sys\temp_Derby_TestErr_db), (DRDAID = null), Failed Statement is: SELECT COALESCE(MAX(f1),0) FROM t1
java.lang.ArrayIndexOutOfBoundsException: -1
at org.apache.derby.impl.services.bytecode.BCMethod.popStack(Unknown Source)
at org.apache.derby.impl.services.bytecode.BCMethod.callMethod(Unknown Source)
at org.apache.derby.impl.sql.compile.ResultColumnList.generateCore(Unknown Source)
at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(Unknown Source)
at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(Unknown Source)
at org.apache.derby.impl.sql.compile.ScrollInsensitiveResultSetNode.generate(Unknown Source)
at org.apache.derby.impl.sql.compile.CursorNode.generate(Unknown Source)
at org.apache.derby.impl.sql.compile.StatementNode.generate(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at org.apache.derby.impl.tools.ij.ij.executeImmediate(Unknown Source)
at org.apache.derby.impl.tools.ij.utilMain.doCatch(Unknown Source)
at org.apache.derby.impl.tools.ij.utilMain.go(Unknown Source)
at org.apache.derby.impl.tools.ij.Main.go(Unknown Source)
at org.apache.derby.impl.tools.ij.Main.mainCore(Unknown Source)
at org.apache.derby.impl.tools.ij.Main14.main(Unknown Source)
at org.apache.derby.tools.ij.main(Unknown Source)