[teiid-issues] [JBoss JIRA] (TEIID-5335) "No value was available" in ROW_NUMBER while inserting in foreign temporary table

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-5335?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins updated TEIID-5335:
----------------------------------
      Component/s: Query Engine
    Fix Version/s: 10.3
                   10.2.2


> "No value was available" in ROW_NUMBER while inserting in foreign temporary table
> ---------------------------------------------------------------------------------
>
>                 Key: TEIID-5335
>                 URL: https://issues.jboss.org/browse/TEIID-5335
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 10.2.1
>            Reporter: Bram Gadeyne
>            Assignee: Steven Hawkins
>             Fix For: 10.3, 10.2.2
>
>         Attachments: TEIID-5335.debugplan.txt
>
>
> while executing the following query the following error occurs:
> {code:sql}
> insert into rep149_variables (patientid,variableid,scoretime,varvalue)
> select patientid, code, entertime, 1 as varvalue
> from (
> 	select cr.code, ci.patientid, ci.entertime, row_number() over(partition by ci.patientid, ci.codeid order by ci.entertime) as rang
> 	from izisprod.S_CodeSystemRef csr
> 	join izisprod.S_CodeGroupRef cgr on cgr.CodeSysID = csr.CodeSysID
> 	join izisprod.S_CodeRef cr on cr.GroupID = cgr.GroupID and cr.code not in ('HK_GEEN','MZ_GEEN','zMG_fout')
> 	join iziswh.P_CodedItem ci on ci.CodeID = cr.CodeID and ci.ItemStatus = 0
> 	join rep149_cohort c on c.patientid = ci.patientid
> 	where csr.CodeSysID in (16,33)
> ) v
> where v.rang = 1;
> {code}
> 2018-05-02 14:03:56,206 ERROR [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue142) MPLUURNpwXk3 TEIID30019 Unexpected exception for request MPLUURNpwXk3.36: org.teiid.core.TeiidComponentException: TEIID30328 Unable to evaluate ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime): No value was available
>         at org.teiid.query.util.CommandContext.getFromContext(CommandContext.java:519)
>         at org.teiid.query.eval.Evaluator.internalEvaluate(Evaluator.java:732)
>         at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:707)
>         at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:331)
>         at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:185)
>         at org.teiid.query.eval.Evaluator.evaluateTVL(Evaluator.java:174)
>         at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:168)
>         at org.teiid.query.processor.relational.SelectNode.nextBatchDirect(SelectNode.java:106)
>         at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
>         at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
>         at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
>         at org.teiid.query.processor.relational.SourceState.getTupleBuffer(SourceState.java:244)
>         at org.teiid.query.processor.relational.EnhancedSortMergeJoinStrategy.loadLeft(EnhancedSortMergeJoinStrategy.java:227)
>         at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:228)
>         at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:200)
>         at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
>         at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
>         at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
>         at org.teiid.query.processor.relational.SourceState.getTupleBuffer(SourceState.java:244)
>         at org.teiid.query.processor.relational.EnhancedSortMergeJoinStrategy.loadLeft(EnhancedSortMergeJoinStrategy.java:227)
>         at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:228)
>         at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:200)
>         at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
>         at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
>         at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
>         at org.teiid.query.processor.relational.ProjectIntoNode.nextBatchDirect(ProjectIntoNode.java:140)
>         at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
>         at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
>         at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
>         at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
>         at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
>         at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
>         at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:480)
>         at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:350)
>         at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
>         at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:276)
>         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)
> When I only execute the query itself then the results are shown without any error.



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