Derby
  1. Derby
  2. DERBY-4242

NPE or assert for query with duplicate predicates and duplicate aliasing

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.6.1.0
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Normal

      Description

      This case was reported by a user on derby-user: http://www.nabble.com/FEHLER-XJ001%3A-Java-Ausnahme%3A-%27%3A-java.lang.NullPointerException%27.-td23636203.html

      The original query and table definitions can be found in the mail from the user. I managed to reduce it to the following query;
      – Reproduces problem
      SELECT
      POC_CNTNT.pln_ordr_id
      FROM
      POC_CNTNT,

      PLN_ORDR,

      POC as poc1,
      POC as poc12,
      POC as poc121,
      POC as poc2,

      POC_STRUCT
      where
      PLN_ORDR.pln_ordr_id = poc1.pln_ordr_id
      and PLN_ORDR.pln_ordr_id = poc121.pln_ordr_id

      and poc1.pln_ordr_id = POC_CNTNT.pln_ordr_id
      and poc1.pln_ordr_id = POC_STRUCT.poc_struct_subj_pln_ordr_id
      and poc121.pln_ordr_id = POC_STRUCT.poc_struct_obj_pln_ordr_id

      and poc2.poc_ix = POC_CNTNT.poc_ix
      and poc2.poc_ix = POC_CNTNT.poc_ix
      ;

      I think this cooks down to the following query, which runs without errors:
      SELECT
      POC_CNTNT.pln_ordr_id
      FROM
      POC_CNTNT,
      PLN_ORDR,
      POC,
      POC_STRUCT
      where
      PLN_ORDR.pln_ordr_id = POC.pln_ordr_id
      and POC.pln_ordr_id = POC_CNTNT.pln_ordr_id
      and POC.pln_ordr_id = POC_STRUCT.poc_struct_subj_pln_ordr_id
      and POC.poc_ix = POC_CNTNT.poc_ix
      ;

      It is clear that the original query, and the reduced query reproducing the error, could be improved significantly. What remains to investigate, is whether the error observed is indeed a weakness in Derby's SQL handling, and if it can cause trouble for more general queries.

      At first sight, it looks to me like Derby gets confused because of duplicate aliases and predicates. I don't know what the expected behavior for such queries is; fail (it is valid SQL though, right?), create a warning, compile and perform badly, or compiled to a form where duplicate/unnecessarily clauses are removed.

      The stack trace with trunk (sane build) looks like this:
      ERROR XJ001: Java exception: 'ASSERT FAILED ColumnReference for correct column (columnPosition = 1, exposed table name = APP.POC_STRUCT) not found on either side of BinaryRelationalOperator: org.apache.derby.shared.common.sanity.AssertFailure'.
      java.sql.SQLException: Java exception: 'ASSERT FAILED ColumnReference for correct column (columnPosition = 1, exposed table name = APP.POC_STRUCT) not found on either side of BinaryRelationalOperator: 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:87)
      at org.apache.derby.impl.jdbc.Util.javaException(Util.java:244)
      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:2201)
      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:329)
      at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:505)
      at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:347)
      at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:245)
      at org.apache.derby.impl.tools.ij.Main.go(Main.java:210)
      at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:177)
      at org.apache.derby.impl.tools.ij.Main.main(Main.java:73)
      at org.apache.derby.tools.ij.main(ij.java:59)
      Caused by: java.sql.SQLException: Java exception: 'ASSERT FAILED ColumnReference for correct column (columnPosition = 1, exposed table name = APP.POC_STRUCT) not found on either side of BinaryRelationalOperator: 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)
      ... 16 more
      Caused by: org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED ColumnReference for correct column (columnPosition = 1, exposed table name = APP.POC_STRUCT) not found on either side of BinaryRelationalOperator
      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.BinaryRelationalOperatorNode.generateExpressionOperand(BinaryRelationalOperatorNode.java:488)
      at org.apache.derby.impl.sql.compile.Predicate.generateExpressionOperand(Predicate.java:670)
      at org.apache.derby.impl.sql.compile.PredicateList.generateSetColumn(PredicateList.java:3544)
      at org.apache.derby.impl.sql.compile.PredicateList.generateStartKey(PredicateList.java:3368)
      at org.apache.derby.impl.sql.compile.BaseJoinStrategy.fillInScanArgs1(BaseJoinStrategy.java:109)
      at org.apache.derby.impl.sql.compile.NestedLoopJoinStrategy.getScanArgs(NestedLoopJoinStrategy.java:248)
      at org.apache.derby.impl.sql.compile.FromBaseTable.getScanArguments(FromBaseTable.java:3449)
      at org.apache.derby.impl.sql.compile.FromBaseTable.generateResultSet(FromBaseTable.java:3139)
      at org.apache.derby.impl.sql.compile.FromBaseTable.generate(FromBaseTable.java:3066)
      at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1477)
      at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1341)
      at org.apache.derby.impl.sql.compile.JoinNode.getJoinArguments(JoinNode.java:1579)
      at org.apache.derby.impl.sql.compile.JoinNode.generateCore(JoinNode.java:1555)
      at org.apache.derby.impl.sql.compile.JoinNode.generate(JoinNode.java:1479)
      at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1477)
      at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1341)
      at org.apache.derby.impl.sql.compile.ScrollInsensitiveResultSetNode.generate(ScrollInsensitiveResultSetNode.java:109)
      at org.apache.derby.impl.sql.compile.CursorNode.generate(CursorNode.java:606)
      at org.apache.derby.impl.sql.compile.StatementNode.generate(StatementNode.java:347)
      at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:449)
      at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:88)
      at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:822)
      at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:606)
      ... 9 more

        Activity

        Hide
        Dag H. Wanvik added a comment -

        Triaged for 10.5.2, "repro attached" and setting "normal" urgency.

        Show
        Dag H. Wanvik added a comment - Triaged for 10.5.2, "repro attached" and setting "normal" urgency.

          People

          • Assignee:
            Unassigned
            Reporter:
            Kristian Waagan
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:

              Development