[teiid-issues] [JBoss JIRA] (TEIID-5474) NPE on Complex Query with Procedure Call in SELECT

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-5474?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13633225#comment-13633225 ] 

Steven Hawkins commented on TEIID-5474:
---------------------------------------

Thanks for capturing this.  It is caused by a bad interaction between the common table planning logic and a correlated subquery in the select clause - the correlated references are not being set until after the common table planning, which causes the initial run of output element assignment to be incorrect.  The fix should be straight-forward.

> NPE on Complex Query with Procedure Call in SELECT
> --------------------------------------------------
>
>                 Key: TEIID-5474
>                 URL: https://issues.jboss.org/browse/TEIID-5474
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 11.1
>         Environment: teiid-11.1.0 (from 01.09.2018) on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)
>            Reporter: dalex dalex
>            Assignee: Steven Hawkins
>            Priority: Critical
>             Fix For: 11.2
>
>
> Running the following query:
> {code:sql}
> SELECT 
> (exec "SYSADMIN.logMsg"(
>     "level" => 'INFO',
>     "context" => 'DEBUG.FOO.BAR',
>     "msg" => tccd.bank_account_holder_name)) as something
> FROM 
> (
> 	SELECT c.city AS "bank_account_holder_name"
> 	FROM "adventureworks.address" c
> 	JOIN 
> 	(
> 		select ca.addressid
> 		from "adventureworks.customeraddress" ca
> 	 cross JOIN 
> 	(
> 		WITH latest_exchange_rates AS 
> 		(
> 			SELECT exchange_rate_date AS month_begin
> 			FROM (
> 				SELECT  CURDATE() as exchange_rate_date
> 				)t
> 		)
> 		SELECT DISTINCT dt.month_start AS month_begin
> 		FROM (
> 			CALL views.createDateDimensionsTable(
> 				"startdate" => TIMESTAMPADD(SQL_TSI_MONTH, 1, (SELECT month_begin FROM latest_exchange_rates)),
> 				"enddate" => TIMESTAMPADD(SQL_TSI_YEAR, 15, (SELECT month_begin FROM latest_exchange_rates))
> 			) ) AS dt
> 	) fx
> 	) ci ON ci.addressid = c.addressid
> ) tccd ;;
> {code}
> will throw out the following stacktrace with NPE:
> {code:noformat}
> 2018-09-13 13:35:21,777 ERROR [org.teiid.PROCESSOR] (Worker5_QueryProcessorQueue36) ZvEDctN1yjKY TEIID30019 Unexpected exception for request ZvEDctN1yjKY.15: java.lang.NullPointerExc
> eption
>         at org.teiid.query.optimizer.relational.rules.RuleMergeVirtual.doMerge(RuleMergeVirtual.java:218)
>         at org.teiid.query.optimizer.relational.rules.RuleMergeVirtual.execute(RuleMergeVirtual.java:80)
>         at org.teiid.query.optimizer.relational.RelationalPlanner.executeRules(RelationalPlanner.java:1025)
>         at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:228)
>         at org.teiid.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:179)
>         at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:458)
>         at org.teiid.dqp.internal.process.Request.processRequest(Request.java:486)
>         at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:672)
>         at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:351)
>         at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
>         at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:285)
>         at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
>         at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
>         at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
>         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)
> {code}



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)