[JBoss JIRA] (TEIID-5335) "No value was available" in ROW_NUMBER while inserting in foreign temporary table
by Bram Gadeyne (JIRA)
[ https://issues.jboss.org/browse/TEIID-5335?page=com.atlassian.jira.plugin... ]
Bram Gadeyne updated TEIID-5335:
--------------------------------
Attachment: TEIID-5335.debugplan.txt
> "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
> Affects Versions: 10.2.1
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> 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)
6 years
[JBoss JIRA] (TEIID-5335) "No value was available" in ROW_NUMBER while inserting in foreign temporary table
by Bram Gadeyne (JIRA)
Bram Gadeyne created TEIID-5335:
-----------------------------------
Summary: "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
Affects Versions: 10.2.1
Reporter: Bram Gadeyne
Assignee: Steven Hawkins
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)
6 years
[JBoss JIRA] (TEIID-5334) Improve pg/ODBC mapping of char type
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5334?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5334.
-----------------------------------
Resolution: Done
Updated to use bpchar consistently with it's correct format name. Didn't yet map the pg_catalog schema usage of char to the pg "char" type, but that could be done as well. We can't use the pg "char" type in general because it only holds a single byte.
> Improve pg/ODBC mapping of char type
> ------------------------------------
>
> Key: TEIID-5334
> URL: https://issues.jboss.org/browse/TEIID-5334
> Project: Teiid
> Issue Type: Bug
> Components: ODBC
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 10.3, 10.2.2
>
>
> The char type pg_type has oid 1042 (bpchar) rather than the expected 18. There should be a separate mapping for both bpchar and char.
> Also the format type for bpchar should be character, not bpchar.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years
[JBoss JIRA] (TEIID-5334) Improve pg/ODBC mapping of char type
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-5334:
-------------------------------------
Summary: Improve pg/ODBC mapping of char type
Key: TEIID-5334
URL: https://issues.jboss.org/browse/TEIID-5334
Project: Teiid
Issue Type: Bug
Components: ODBC
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 10.3, 10.2.2
The char type pg_type has oid 1042 (bpchar) rather than the expected 18. There should be a separate mapping for both bpchar and char.
Also the format type for bpchar should be character, not bpchar.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years
[JBoss JIRA] (TEIID-5314) usage of environment properties
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5314?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5314.
-----------------------------------
Resolution: Done
Marking as resolved. All places where we directly used system properties now check environment properties first. If needed we can adjust the resolving order such that system properties take preference.
> usage of environment properties
> -------------------------------
>
> Key: TEIID-5314
> URL: https://issues.jboss.org/browse/TEIID-5314
> Project: Teiid
> Issue Type: Enhancement
> Components: Server
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 10.3
>
>
> Places in Teiid that look for system properties should allow for overrides to be specified via environment properties as well. This will make it easier to configure optional behavior on openshift.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years
[JBoss JIRA] (TEIID-5333) Complex foreign keys set the referenced key regardless of order
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5333?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5333.
-----------------------------------
Fix Version/s: 10.3
10.1.4
10.2.2
Resolution: Done
This is not a common situation, so for now the handling in the metadata validator is to just reorder the fk columns to match the pk columns. We can also just turn this into an exception case with little effort, or if we need to have our logic account for the actual order then we'll need to update a lot of assumptions.
> Complex foreign keys set the referenced key regardless of order
> ---------------------------------------------------------------
>
> Key: TEIID-5333
> URL: https://issues.jboss.org/browse/TEIID-5333
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 10.3, 10.1.4, 10.2.2
>
>
> The metadatavalidator will resolve foreign keys as long as the columns match - but does not reorder the fk columns nor track the referenced order which means that the referenced key can be wrong. For example:
> {code}
> CREATE FOREIGN TABLE G1(g1e1 integer, g1e2 integer, PRIMARY KEY(g1e1, g1e2));
> CREATE FOREIGN TABLE G2(g2e1 integer, g2e2 integer, FOREIGN KEY (g2e1, g2e2) REFERENCES G1(g1e2, g1e1));
> {code}
> Note that the elements are reversed in the references list, but this is not captured in the resolved foreign key meaning that it effectively represents a relationship on g2e1,g2e2 with g1e1,g1e2.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years
[JBoss JIRA] (TEIID-5333) Complex foreign keys set the referenced key regardless of order
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5333?page=com.atlassian.jira.plugin... ]
Work on TEIID-5333 started by Steven Hawkins.
---------------------------------------------
> Complex foreign keys set the referenced key regardless of order
> ---------------------------------------------------------------
>
> Key: TEIID-5333
> URL: https://issues.jboss.org/browse/TEIID-5333
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
>
> The metadatavalidator will resolve foreign keys as long as the columns match - but does not reorder the fk columns nor track the referenced order which means that the referenced key can be wrong. For example:
> {code}
> CREATE FOREIGN TABLE G1(g1e1 integer, g1e2 integer, PRIMARY KEY(g1e1, g1e2));
> CREATE FOREIGN TABLE G2(g2e1 integer, g2e2 integer, FOREIGN KEY (g2e1, g2e2) REFERENCES G1(g1e2, g1e1));
> {code}
> Note that the elements are reversed in the references list, but this is not captured in the resolved foreign key meaning that it effectively represents a relationship on g2e1,g2e2 with g1e1,g1e2.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years
[JBoss JIRA] (TEIID-5333) Complex foreign keys set the referenced key regardless of order
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-5333:
-------------------------------------
Summary: Complex foreign keys set the referenced key regardless of order
Key: TEIID-5333
URL: https://issues.jboss.org/browse/TEIID-5333
Project: Teiid
Issue Type: Bug
Components: Query Engine
Reporter: Steven Hawkins
Assignee: Steven Hawkins
The metadatavalidator will resolve foreign keys as long as the columns match - but does not reorder the fk columns nor track the referenced order which means that the referenced key can be wrong. For example:
{code}
CREATE FOREIGN TABLE G1(g1e1 integer, g1e2 integer, PRIMARY KEY(g1e1, g1e2));
CREATE FOREIGN TABLE G2(g2e1 integer, g2e2 integer, FOREIGN KEY (g2e1, g2e2) REFERENCES G1(g1e2, g1e1));
{code}
Note that the elements are reversed in the references list, but this is not captured in the resolved foreign key meaning that it effectively represents a relationship on g2e1,g2e2 with g1e1,g1e2.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years