[ https://issues.apache.org/jira/browse/DERBY-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17459799#comment-17459799 ]
Fanweifeng commented on DERBY-4041: ----------------------------------- The reason of NPE is caused by CostEstimate is null when invoke optimizableList.getOptimizable(bestJoinOrder[i]).getTrulyTheBestAccessPath().getCostEstimate(). This problem can be fixed referred to add ( ce ==null ) continue like code in line 912. for (int i = 0; i < numOptimizables; i++) { firstLookOrder[i] = i; CostEstimate ce = optimizableList.getOptimizable(i). getBestAccessPath().getCostEstimate(); if (ce == null) { permuteState = READY_TO_JUMP; //come again? break; } rc[i] = ce.singleScanRowCount(); } > NullPointerException on query with misplaced DERBY-PROPERTIES clause > -------------------------------------------------------------------- > > Key: DERBY-4041 > URL: https://issues.apache.org/jira/browse/DERBY-4041 > Project: Derby > Issue Type: Bug > Components: SQL > Affects Versions: 10.3.3.0, 10.4.2.0, 10.5.1.1 > Reporter: Katherine Marsden > Assignee: Dimuthu Wickramanayake > Priority: Minor > Labels: derby_triage10_8 > Attachments: AttributeHolder.patch, commentedQueryRepro.out, > commentedQueryRepro.sql, modified_querynperepro.sql, querynperepro.sql > > > The following sql throws a NullPointerException > ij> CREATE TABLE "APP"."T1" ("I" INTEGER, "J" INTEGER); > 0 rows inserted/updated/deleted > ij> insert into t1 values (1, 2), (2, 4), (3, 6), (4, 8), (5, 10); > 5 rows inserted/updated/deleted > ij> CREATE TABLE "APP"."T2" ("I" INTEGER, "J" INTEGER); > 0 rows inserted/updated/deleted > ij> insert into t2 values (1, 2), (2, -4), (3, 6), (4, -8), (5, 10); > 5 rows inserted/updated/deleted > ij> CREATE TABLE "APP"."T3" ("A" INTEGER, "B" INTEGER); > 0 rows inserted/updated/deleted > ij> insert into T3 values (1,1), (2,2), (3,3), (4,4), (6, 24), > (7, 28), (8, 32), (9, 36), (10, 40); > 9 rows inserted/updated/deleted > ij> insert into t3 (a) values 11, 12, 13, 14, 15, 16, 17, 18, 19, 20; > 10 rows inserted/updated/deleted > ij> update t3 set b = 2 * a where a > 10; > 10 rows inserted/updated/deleted > ij> CREATE TABLE "APP"."T4" ("A" INTEGER, "B" INTEGER); > 0 rows inserted/updated/deleted > ij> insert into t4 values (3, 12), (4, 16); > 2 rows inserted/updated/deleted > ij> insert into t4 (a) values 11, 12, 13, 14, 15, 16, 17, 18, 19, 20; > 10 rows inserted/updated/deleted > ij> update t4 set b = 2 * a where a > 10; > 10 rows inserted/updated/deleted > ij> select count(*) from (select * from t1 union select * from t3 > --DERBY-PROPERTIES joinStrategy=HASH > ) x1 (c, d), (select * from t2 union select * from t4) x2 (e, f) where x1.c = > x2.e ; > ERROR XJ001: Java exception: ': java.lang.NullPointerException'. > java.sql.SQLException: Java exception: ': java.lang.NullPointerException'. > 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: ': > java.lang.NullPointerException'. > at > org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45) > at > org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:11 > 9) > at > org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70) > ... 16 more > Caused by: java.lang.NullPointerException > at > org.apache.derby.impl.sql.compile.OptimizerImpl.getFinalCost(OptimizerImpl.java:2498) > at > org.apache.derby.impl.sql.compile.SelectNode.getFinalCostEstimate(SelectNode.java:1987) > at > org.apache.derby.impl.sql.compile.UnionNode.getFinalCostEstimate(UnionNode.java:653) > at > org.apache.derby.impl.sql.compile.SetOperatorNode.modifyAccessPath(SetOperatorNode.java:169) > at > org.apache.derby.impl.sql.compile.ProjectRestrictNode.modifyAccessPath(ProjectRestrictNode.java:718) > at > org.apache.derby.impl.sql.compile.OptimizerImpl.modifyAccessPaths(OptimizerImpl.java:2456) > at > org.apache.derby.impl.sql.compile.SelectNode.modifyAccessPaths(SelectNode.java:1865) > at > org.apache.derby.impl.sql.compile.DMLStatementNode.optimizeStatement(DMLStatementNode.java:307) > at > org.apache.derby.impl.sql.compile.CursorNode.optimizeStatement(CursorNode.java:515) > at > org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:367) > at > org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:88) > at > org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConne > ctionContext.java:802) > at > org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:606) > ... 9 more > I think the directive is misplaced and should come after the x1 (c, d) but it > shouldn't throw an NPE -- This message was sent by Atlassian Jira (v8.20.1#820001)