NPE or assert for query with duplicate predicates and duplicate aliasing
------------------------------------------------------------------------
Key: DERBY-4242
URL: https://issues.apache.org/jira/browse/DERBY-4242
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.6.0.0
Reporter: Kristian Waagan
Priority: Minor
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
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
