Derby
  1. Derby
  2. DERBY-4594

ArrayIndexOutOfBoundsException thrown in PreparedStatement execution

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.2.2.0, 10.3.3.0, 10.4.2.0, 10.5.3.0, 10.6.1.0
    • Fix Version/s: 10.5.3.1, 10.6.1.0
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Derby 10.5.3.0_1
      Windows XP SP3
    • Issue & fix info:
      Repro attached

      Description

      I am encountering the following exception when trying to execute a PreparedStatement:

      Exception in thread "main" java.sql.SQLException: The exception 'java.lang.ArrayIndexOutOfBoundsException: -1' was thrown while evaluating an expression.
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
      ...
      Caused by: java.lang.ArrayIndexOutOfBoundsException: -1
      at org.apache.derby.impl.sql.execute.BaseActivation.getColumnFromRow(Unknown Source)
      at org.apache.derby.exe.ac80220011x0127x90baxe992x000075337d882.g0(Unknown Source)
      at org.apache.derby.exe.ac80220011x0127x90baxe992x000075337d882.e1(Unknown Source)
      at org.apache.derby.impl.services.reflect.DirectCall.invoke(Unknown Source)
      at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown Source)
      at org.apache.derby.impl.sql.execute.JoinResultSet.openCore(Unknown Source)
      at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(Unknown Source)
      at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.open(Unknown Source)
      at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
      ... 5 more

      The query I am executing is:
      SELECT R.report_id, ER.entity_id FROM reports AS R
      LEFT JOIN entity_reports AS ER ON R.report_id = ER.report_id
      WHERE R.details LIKE ?
      AND COALESCE(ER.entity_id, 0) != ?

      The table 'reports' can be prepared with the following SQL:
      ij> CREATE TABLE reports (report_id bigint PRIMARY KEY, document_num varchar(50), status varchar(100), type varchar(100) NOT NULL, specific varchar(100), owner varchar(100), summary clob, details clob, closing_remarks clob, occurred date, opened date, closed date, reference_guid varchar(50) NOT NULL);
      ij> INSERT INTO reports (report_id, type, summary, details, reference_guid) VALUES (1, 'Type 1', 'Summary 1', 'Details 1', 'guid 1');

      The table "entity_reports" can be prepared with:
      ij> CREATE TABLE entity_reports (report_id bigint, entity_id bigint, primary key (report_id, entity_id));
      The error happens regardless of whether this table has rows in it or not. Technically both report_id and entity_id are foreign keys but that doesn't seem to make a difference.

      Executing the above SQL on a new derby database and then running the query, whether it's via JDBC from Java or directly through ij, causes the problem to occur.
      Here is a copy of the query suitable for execution in ij (i.e., the values are filled out):
      SELECT R.report_id, ER.entity_id FROM reports AS R
      LEFT JOIN entity_reports AS ER ON R.report_id = ER.report_id
      WHERE R.details LIKE '%Details%'
      AND COALESCE(ER.entity_id, 0) != 1;

      I'm open to the idea that I'm doing something wrong, but the same code works against our test MSSQL db.
      Thank you for your time.

      1. categorize.diff
        1 kB
        Knut Anders Hatlen
      2. script.sql
        0.7 kB
        Arthur Doler
      3. with_tests.diff
        3 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Hide
          Arthur Doler added a comment -

          Attached an sql script to create the test DB, create the tables, add the data, and run the failing query. Script does not disconnect or exit ij.

          Show
          Arthur Doler added a comment - Attached an sql script to create the test DB, create the tables, add the data, and run the failing query. Script does not disconnect or exit ij.
          Hide
          Kristian Waagan added a comment -

          Thanks for reporting the bug and providing a repro, Arthur.

          Run with a trunk debug build the following assert is triggered:

          ERROR XJ001: Java exception: 'ASSERT FAILED sourceResultSetNumber expected to be >= 0 for ER.ENTITY_ID: org.apache.derby.shared.common.sanity.AssertFailure'.
          java.sql.SQLException: Java exception: 'ASSERT FAILED sourceResultSetNumber expected to be >= 0 for ER.ENTITY_ID: org.apache.derby.shared.common.sanity.AssertFailure'.
          at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95)
          at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:142)
          at org.apache.derby.impl.jdbc.Util.javaException(Util.java:299)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:403)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
          at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2269)
          at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
          at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:614)
          at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:555)
          at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:367)
          at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:521)
          at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:363)
          at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:261)
          at org.apache.derby.impl.tools.ij.Main.go(Main.java:229)
          at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:184)
          at org.apache.derby.impl.tools.ij.Main.main(Main.java:75)
          at org.apache.derby.tools.ij.main(ij.java:59)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at java.lang.reflect.Method.invoke(Method.java:597)
          at jline.ConsoleRunner.main(ConsoleRunner.java:69)
          Caused by: java.sql.SQLException: Java exception: 'ASSERT FAILED sourceResultSetNumber expected to be >= 0 for ER.ENTITY_ID: org.apache.derby.shared.common.sanity.AssertFailure'.
          at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
          at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:119)
          at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)
          ... 21 more
          Caused by: org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED sourceResultSetNumber expected to be >= 0 for ER.ENTITY_ID
          at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:162)
          at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:147)
          at org.apache.derby.impl.sql.compile.ColumnReference.generateExpression(ColumnReference.java:972)
          at org.apache.derby.impl.sql.compile.CoalesceFunctionNode.generateExpression(CoalesceFunctionNode.java:262)
          at org.apache.derby.impl.sql.compile.BinaryOperatorNode.generateExpression(BinaryOperatorNode.java:550)
          at org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.generateExpression(BinaryLogicalOperatorNode.java:157)
          at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1523)
          at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1334)
          at org.apache.derby.impl.sql.compile.JoinNode.getJoinArguments(JoinNode.java:1632)
          at org.apache.derby.impl.sql.compile.JoinNode.generateCore(JoinNode.java:1610)
          at org.apache.derby.impl.sql.compile.JoinNode.generateCore(JoinNode.java:1547)
          at org.apache.derby.impl.sql.compile.HalfOuterJoinNode.generate(HalfOuterJoinNode.java:694)
          at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1382)
          at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1334)
          at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1479)
          at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1334)
          at org.apache.derby.impl.sql.compile.ScrollInsensitiveResultSetNode.generate(ScrollInsensitiveResultSetNode.java:109)
          at org.apache.derby.impl.sql.compile.CursorNode.generate(CursorNode.java:608)
          at org.apache.derby.impl.sql.compile.StatementNode.generate(StatementNode.java:347)
          at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:469)
          at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:90)
          at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:828)
          at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:606)
          ... 14 more

          Show
          Kristian Waagan added a comment - Thanks for reporting the bug and providing a repro, Arthur. Run with a trunk debug build the following assert is triggered: ERROR XJ001: Java exception: 'ASSERT FAILED sourceResultSetNumber expected to be >= 0 for ER.ENTITY_ID: org.apache.derby.shared.common.sanity.AssertFailure'. java.sql.SQLException: Java exception: 'ASSERT FAILED sourceResultSetNumber expected to be >= 0 for ER.ENTITY_ID: org.apache.derby.shared.common.sanity.AssertFailure'. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95) at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:142) at org.apache.derby.impl.jdbc.Util.javaException(Util.java:299) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:403) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2269) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:614) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:555) at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:367) at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:521) at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:363) at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:261) at org.apache.derby.impl.tools.ij.Main.go(Main.java:229) at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:184) at org.apache.derby.impl.tools.ij.Main.main(Main.java:75) at org.apache.derby.tools.ij.main(ij.java:59) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at jline.ConsoleRunner.main(ConsoleRunner.java:69) Caused by: java.sql.SQLException: Java exception: 'ASSERT FAILED sourceResultSetNumber expected to be >= 0 for ER.ENTITY_ID: org.apache.derby.shared.common.sanity.AssertFailure'. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:119) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70) ... 21 more Caused by: org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED sourceResultSetNumber expected to be >= 0 for ER.ENTITY_ID at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:162) at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:147) at org.apache.derby.impl.sql.compile.ColumnReference.generateExpression(ColumnReference.java:972) at org.apache.derby.impl.sql.compile.CoalesceFunctionNode.generateExpression(CoalesceFunctionNode.java:262) at org.apache.derby.impl.sql.compile.BinaryOperatorNode.generateExpression(BinaryOperatorNode.java:550) at org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.generateExpression(BinaryLogicalOperatorNode.java:157) at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1523) at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1334) at org.apache.derby.impl.sql.compile.JoinNode.getJoinArguments(JoinNode.java:1632) at org.apache.derby.impl.sql.compile.JoinNode.generateCore(JoinNode.java:1610) at org.apache.derby.impl.sql.compile.JoinNode.generateCore(JoinNode.java:1547) at org.apache.derby.impl.sql.compile.HalfOuterJoinNode.generate(HalfOuterJoinNode.java:694) at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1382) at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1334) at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1479) at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1334) at org.apache.derby.impl.sql.compile.ScrollInsensitiveResultSetNode.generate(ScrollInsensitiveResultSetNode.java:109) at org.apache.derby.impl.sql.compile.CursorNode.generate(CursorNode.java:608) at org.apache.derby.impl.sql.compile.StatementNode.generate(StatementNode.java:347) at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:469) at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:90) at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:828) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:606) ... 14 more
          Hide
          Kristian Waagan added a comment -

          Bug exists back to 10.2, didn't try earlier versions (not readily available atm).

          Show
          Kristian Waagan added a comment - Bug exists back to 10.2, didn't try earlier versions (not readily available atm).
          Hide
          Knut Anders Hatlen added a comment -

          Here's a stripped down repro:

          CREATE TABLE t1(a1 INT);
          CREATE TABLE t2(a2 INT);
          INSERT INTO t1 VALUES 1;

          This statement will cause an ArrayIndexOutOfBoundsException:

          SELECT 1 FROM t1 LEFT JOIN t2 ON 1=1 WHERE COALESCE(a2, 0) != 1;

          Whereas this statement will cause a NullPointerException:

          SELECT 1 FROM t1 JOIN t2 ON 1=1 WHERE COALESCE(a2, 0) != 1;

          Show
          Knut Anders Hatlen added a comment - Here's a stripped down repro: CREATE TABLE t1(a1 INT); CREATE TABLE t2(a2 INT); INSERT INTO t1 VALUES 1; This statement will cause an ArrayIndexOutOfBoundsException: SELECT 1 FROM t1 LEFT JOIN t2 ON 1=1 WHERE COALESCE(a2, 0) != 1; Whereas this statement will cause a NullPointerException: SELECT 1 FROM t1 JOIN t2 ON 1=1 WHERE COALESCE(a2, 0) != 1;
          Hide
          Knut Anders Hatlen added a comment -

          The problem appears to be that CoalesceFunctionNode lacks an override of the categorize() method. The repro runs successfully with the attached patch.

          Show
          Knut Anders Hatlen added a comment - The problem appears to be that CoalesceFunctionNode lacks an override of the categorize() method. The repro runs successfully with the attached patch.
          Hide
          Knut Anders Hatlen added a comment -

          Added a comment and a regression test case and committed the changes to trunk with revision 927430.

          Show
          Knut Anders Hatlen added a comment - Added a comment and a regression test case and committed the changes to trunk with revision 927430.
          Hide
          Knut Anders Hatlen added a comment -

          Merged fix to the 10.5 branch and committed revision 927599.

          Show
          Knut Anders Hatlen added a comment - Merged fix to the 10.5 branch and committed revision 927599.
          Hide
          Knut Anders Hatlen added a comment -

          [bulk update] Close all resolved issues that haven't been updated for more than one year.

          Show
          Knut Anders Hatlen added a comment - [bulk update] Close all resolved issues that haven't been updated for more than one year.

            People

            • Assignee:
              Knut Anders Hatlen
              Reporter:
              Arthur Doler
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development