[teiid-issues] [JBoss JIRA] (TEIID-4445) Optimizer NPE In RuleChooseDependent.findCandidate

Optimizer NPE In RuleChooseDependent.findCandidate -------------------------------------------------- Key: TEIID-4445 URL: https://issues.jboss.org/browse/TEIID-4445 Project: Teiid Issue Type: Bug Affects Versions: 8.13.2 Reporter: Ehud Reches Assignee: Steven Hawkins Attachments: app1-vdb.xml, query.sql, schema.sql The attached query against a VDB with a single federated Oracle database causes an NPE in org.teiid.query.optimizer.relational.rules.RuleChooseDependent.findCandidate. Was reproduced independently at our customer site and by us: org.teiid.core.TeiidException SELECT count(*) from SF_OPPORTUNITY OPP INNER JOIN DIM_DATE_EXTN ON MODIFYTIMEZONE(OPP.CREATEDDATE,'GMT','PST') = DIM_DATE_EXTN.CLNDR_DT INNER JOIN SF_USER OWNER_USR ON OPP.OWNERID = OWNER_USR.ID INNER JOIN SF_USER CREATED_USR ON OPP.CREATEDBYID = CREATED_USR.ID LEFT OUTER JOIN FACT_BOOKING FB ON OPP.ID = FB.SALE_ORDER_HDR_ID LEFT OUTER JOIN SF_PRICEBOOK2 PB2 ON OPP.PRICEBOOK2ID = PB2.ID LEFT OUTER JOIN RPTCOMPANYIDS_1 QBO ON CAST(OPP.COMPANY_ID__C as varchar (25)) = CAST(QBO.COMPANY_ID as varchar (25)) LEFT OUTER JOIN SF_OPPORTUNITYLINEITEM OPP_L ON OPP.ID = OPP_L.OPPORTUNITYID LEFT OUTER JOIN SF_OPPORTUNITYLINEITEM OPP_LI ON OPP_LI.ID = FB.SALE_ORDER_LINE_ID LEFT OUTER JOIN SF_PRODUCT2 PR ON OPP_L.PRODUCT2ID = PR.ID LEFT OUTER JOIN SF_PRICEBOOKENTRY PBE ON (PR.ID = PBE.PRODUCT2ID AND PB2.ID = PBE.PRICEBOOK2ID) LEFT OUTER JOIN DTL_PAYCYCLE PC ON CAST(OPP.COMPANY_ID__C as varchar (25)) = CAST(PC.QBOCOMPANYID as varchar (25)) LEFT OUTER JOIN (SELECT * FROM DIM_EMPLOYEE WHERE EMAIL_ADDR IS NOT NULL) C_AGENT ON C_AGENT.CORP_ID = CREATED_USR.FEDERATIONIDENTIFIER LEFT OUTER JOIN DIM_EMPLOYEE_DIVISION C_AGENT_DIVISION ON C_AGENT.DIM_EMPLOYEE_KEY = C_AGENT_DIVISION.DIM_EMPLOYEE_KEY AND MODIFYTIMEZONE(OPP.CREATEDDATE,'GMT','PST') >= NVL(C_AGENT_DIVISION.REC_START_DATE,'01-JAN-13') AND MODIFYTIMEZONE(OPP.CREATEDDATE,'GMT','PST') <= NVL(C_AGENT_DIVISION.REC_END_DATE,'01-JAN-50') LEFT OUTER JOIN DIM_DIVISION_PIVOT_VW C_DIVISION ON C_DIVISION.DIM_DIVISION_KEY = C_AGENT_DIVISION.DIM_DIVISION_KEY LEFT OUTER JOIN DIM_EMPLOYEE_LOGIN C_AGENT_LOGIN ON C_AGENT_LOGIN.DIM_EMPLOYEE_KEY = C_AGENT.DIM_EMPLOYEE_KEY AND MODIFYTIMEZONE(OPP.CREATEDDATE,'GMT','PST') >= NVL(C_AGENT_LOGIN.REC_START_DATE,'01-JAN-13') AND MODIFYTIMEZONE(OPP.CREATEDDATE,'GMT','PST') <= NVL(C_AGENT_LOGIN.REC_END_DATE,'01-JAN-50') LEFT OUTER JOIN DIM_EMPLOYEE C_AGENT_MGR ON C_AGENT_MGR.DIM_EMPLOYEE_KEY = C_AGENT_LOGIN.MANAGER_EMPLOYEE_KEY LEFT OUTER JOIN (SELECT * FROM DIM_EMPLOYEE WHERE EMAIL_ADDR IS NOT NULL) AGENT ON AGENT.CORP_ID = OWNER_USR.FEDERATIONIDENTIFIER LEFT OUTER JOIN DIM_EMPLOYEE_DIVISION AGENT_DIVISION ON AGENT.DIM_EMPLOYEE_KEY = AGENT_DIVISION.DIM_EMPLOYEE_KEY AND MODIFYTIMEZONE(OPP.CREATEDDATE,'GMT','PST') >= NVL(AGENT_DIVISION.REC_START_DATE,'01-JAN-13') AND MODIFYTIMEZONE(OPP.CREATEDDATE,'GMT','PST') <= NVL(AGENT_DIVISION.REC_END_DATE,'01-JAN-50') LEFT OUTER JOIN DIM_DIVISION_PIVOT_VW DIVISION ON DIVISION.DIM_DIVISION_KEY = AGENT_DIVISION.DIM_DIVISION_KEY LEFT OUTER JOIN DIM_EMPLOYEE_LOGIN AGENT_LOGIN ON AGENT_LOGIN.DIM_EMPLOYEE_KEY = AGENT.DIM_EMPLOYEE_KEY AND MODIFYTIMEZONE(OPP.CREATEDDATE,'GMT','PST') >= NVL(AGENT_LOGIN.REC_START_DATE,'01-JAN-13') AND MODIFYTIMEZONE(OPP.CREATEDDATE,'GMT','PST') <= NVL(AGENT_LOGIN.REC_END_DATE,'01-JAN-50') LEFT OUTER JOIN DIM_EMPLOYEE AGENT_MGR ON AGENT_MGR.DIM_EMPLOYEE_KEY = AGENT_LOGIN.MANAGER_EMPLOYEE_KEY LEFT OUTER JOIN SF_CAMPAIGNMEMBER CM ON CM.LEADID = OPP.ID LEFT OUTER JOIN SF_CAMPAIGN C ON C.ID = CM.CAMPAIGNID WHERE ( DIVISION.DIM_SCENARIO_KEY = 2 OR DIVISION.DIM_SCENARIO_KEY IS NULL) AND ( C_DIVISION.DIM_SCENARIO_KEY = 2 OR C_DIVISION.DIM_SCENARIO_KEY IS NULL) AND OPP.ISDELETED = 0 AND (OPP.CLOSEDATE IS NULL OR OPP.CLOSEDATE <= TIMESTAMPADD(SQL_TSI_DAY , 60, now())) AND (C_DIVISION.DIVISION_L6_NAME IN ('APD Sales','Telesales') OR DIVISION.DIVISION_L6_NAME IN ('APD Sales','Telesales')) AND DIM_DATE_EXTN.CLNDR_DT >= '01-JUN-15' AND C_DIVISION.DIVISION_L5_NAME = 'SBFS Sales' org.teiid.jdbc.TeiidSQLException: org.teiid.core.TeiidException at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135) at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71) at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:706) at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:64) at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:545) at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:135) at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:40) at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:79) at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:268) at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:306) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:98) at com.sun.proxy.$Proxy97.read(Unknown Source) at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:405) at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:554) at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:1076) at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:323) at plat36961.SQLExecuter.exceSQL(SQLExecuter.java:121) at plat36961.SQLExecuter.execStatements(SQLExecuter.java:65) at plat36961.ComplexQueryTest.runComplexQuery(ComplexQueryTest.java:83) at plat36961.ComplexQueryTest.runTest(ComplexQueryTest.java:76) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20) at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28) at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:30) at com.attivio.junit.rules.CleanupHooksRule$1.evaluate(CleanupHooksRule.java:36) at com.attivio.junit.rules.DisplayLeftoverThreadsRule$1.evaluate(DisplayLeftoverThreadsRule.java:32) at com.attivio.junit.rules.CheckBaseportRule$1.evaluate(CheckBaseportRule.java:26) at com.attivio.junit.rules.ResetHttpConnectionManagerRule$1.evaluate(ResetHttpConnectionManagerRule.java:26) at com.attivio.junit.rules.KillExternalProcessesRule$1.evaluate(KillExternalProcessesRule.java:50) at org.junit.rules.RunRules.evaluate(RunRules.java:18) at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:263) at com.attivio.junit.runners.JUnit4EnhancedTimeoutClassRunner.runChild(JUnit4EnhancedTimeoutClassRunner.java:112) at com.attivio.junit.runners.JUnit4EnhancedTimeoutClassRunner.runChild(JUnit4EnhancedTimeoutClassRunner.java:27) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222) at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28) at org.junit.runners.ParentRunner.run(ParentRunner.java:300) at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192) Caused by: org.teiid.core.TeiidException at org.teiid.client.ResultsMessage.setException(ResultsMessage.java:196) at org.teiid.dqp.internal.process.RequestWorkItem.sendError(RequestWorkItem.java:1084) at org.teiid.dqp.internal.process.RequestWorkItem.close(RequestWorkItem.java:576) at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:374) at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274) at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276) at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Caused by: java.lang.NullPointerException at org.teiid.query.optimizer.relational.rules.RuleChooseDependent.findCandidate(RuleChooseDependent.java:196) at org.teiid.query.optimizer.relational.rules.RuleChooseDependent.execute(RuleChooseDependent.java:100) at org.teiid.query.optimizer.relational.RelationalPlanner.executeRules(RelationalPlanner.java:834) at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:224) at org.teiid.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:159) at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:440) at org.teiid.dqp.internal.process.Request.processRequest(Request.java:468) at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:640) at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:337)