[JBoss JIRA] (TEIID-5688) groupby functionality not working with Teiid
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5688?page=com.atlassian.jira.plugin... ]
Christoph John updated TEIID-5688:
----------------------------------
Description:
Hello together,
I tried to get the distinct values of a certain column in a mysql table and used the following query
https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary?$select=fkIdPr...
However, it seems the apply=group is ignored from Teiid. I retrieve all records with fkIdProductCode and not just a single one. In the example below the first and last record both have the same fkIdProductCode. I would expect to retrieve just a single result. The result in my example looks as follows:
{
@odata.context: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/$metadata#Diary(idDi...",
value: [
{
@odata.id: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(17)",
idDiaryEntry: 17,
fkIdProductCode: 17
},
{
@odata.id: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(18)",
idDiaryEntry: 18,
fkIdProductCode: 38
},
{
@odata.id: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(19)",
idDiaryEntry: 19,
fkIdProductCode: 482
},
{
@odata.id: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(20)",
idDiaryEntry: 20,
fkIdProductCode: 1564
},
{
@odata.id: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(21)",
idDiaryEntry: 21,
fkIdProductCode: 17
}
]
}
So as a matter of principle, I would like to get each fkIdProductCode in the Diary table just once and expand its name via a navigationProperty. Is there some other workaround, how I could achieve this?
Thans for your help
was:
Hello together,
I tried to get the distinct values of a certain column in a mysql table and used the following query
https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary?$select=fkIdPr...
However, it seems the apply=group is ignored from Teiid. I retrieve all records with fkIdProductCode and not just a single one. In the example below the first and last record both have the same fkIdProductCode. I would expect to retrieve just a single result. The result in my example looks as follows:
{
@odata.context: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/$metadata#Diary(idDi...",
value: [
{
@odata.id: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(17)",
idDiaryEntry: 17,
fkIdProductCode: 17
},
{
@odata.id: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(18)",
idDiaryEntry: 18,
fkIdProductCode: 38
},
{
@odata.id: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(19)",
idDiaryEntry: 19,
fkIdProductCode: 482
},
{
@odata.id: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(20)",
idDiaryEntry: 20,
fkIdProductCode: 1564
},
{
@odata.id: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(21)",
idDiaryEntry: 21,
fkIdProductCode: 17
}
]
}
> groupby functionality not working with Teiid
> --------------------------------------------
>
> Key: TEIID-5688
> URL: https://issues.jboss.org/browse/TEIID-5688
> Project: Teiid
> Issue Type: Bug
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Major
>
> Hello together,
> I tried to get the distinct values of a certain column in a mysql table and used the following query
> https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary?$select=fkIdPr...
> However, it seems the apply=group is ignored from Teiid. I retrieve all records with fkIdProductCode and not just a single one. In the example below the first and last record both have the same fkIdProductCode. I would expect to retrieve just a single result. The result in my example looks as follows:
> {
> @odata.context: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/$metadata#Diary(idDi...",
> value: [
> {
> @odata.id: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(17)",
> idDiaryEntry: 17,
> fkIdProductCode: 17
> },
> {
> @odata.id: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(18)",
> idDiaryEntry: 18,
> fkIdProductCode: 38
> },
> {
> @odata.id: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(19)",
> idDiaryEntry: 19,
> fkIdProductCode: 482
> },
> {
> @odata.id: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(20)",
> idDiaryEntry: 20,
> fkIdProductCode: 1564
> },
> {
> @odata.id: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(21)",
> idDiaryEntry: 21,
> fkIdProductCode: 17
> }
> ]
> }
> So as a matter of principle, I would like to get each fkIdProductCode in the Diary table just once and expand its name via a navigationProperty. Is there some other workaround, how I could achieve this?
> Thans for your help
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
7 years
[JBoss JIRA] (TEIID-5688) groupby functionality not working with Teiid
by Christoph John (Jira)
Christoph John created TEIID-5688:
-------------------------------------
Summary: groupby functionality not working with Teiid
Key: TEIID-5688
URL: https://issues.jboss.org/browse/TEIID-5688
Project: Teiid
Issue Type: Bug
Reporter: Christoph John
Assignee: Steven Hawkins
Hello together,
I tried to get the distinct values of a certain column in a mysql table and used the following query
https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary?$select=fkIdPr...
However, it seems the apply=group is ignored from Teiid. I retrieve all records with fkIdProductCode and not just a single one. In the example below the first and last record both have the same fkIdProductCode. I would expect to retrieve just a single result. The result in my example looks as follows:
{
@odata.context: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/$metadata#Diary(idDi...",
value: [
{
@odata.id: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(17)",
idDiaryEntry: 17,
fkIdProductCode: 17
},
{
@odata.id: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(18)",
idDiaryEntry: 18,
fkIdProductCode: 38
},
{
@odata.id: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(19)",
idDiaryEntry: 19,
fkIdProductCode: 482
},
{
@odata.id: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(20)",
idDiaryEntry: 20,
fkIdProductCode: 1564
},
{
@odata.id: "https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(21)",
idDiaryEntry: 21,
fkIdProductCode: 17
}
]
}
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
7 years
[JBoss JIRA] (TEIID-5683) Push or use limit information before dependent join processing
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5683?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5683:
---------------------------------------
There are nuances to the pushing logic that make it hard to simply change into a simple detection for earlier costing. Running the rule early violates some existing assumptions, such at the final output element assignment has already been run. So there isn't a very quick fix here. I'll see if some new code will help.
> Push or use limit information before dependent join processing
> --------------------------------------------------------------
>
> Key: TEIID-5683
> URL: https://issues.jboss.org/browse/TEIID-5683
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 12.2
>
>
> There appear to be some situations (such as a left outer join from TEIID-5680) where the limit could help direct a dependent join, but isn't considered as rule push limit isn't run until later.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
7 years
[JBoss JIRA] (TEIID-5686) NPE in MergeJoinStrategy when using joins with a particular limit value
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5686?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5686.
-----------------------------------
Resolution: Done
Corrected the handling in the procedure plan to buffer the results of an iteration that may end up being the implicit return. This was easier than restructuring the logic to track the cursors such that we could clone the plan, but it does require additional buffering.
> NPE in MergeJoinStrategy when using joins with a particular limit value
> -----------------------------------------------------------------------
>
> Key: TEIID-5686
> URL: https://issues.jboss.org/browse/TEIID-5686
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 12.0
> Environment: teiid-12.0.0 on WildFly Full 14.0.1.Final (WildFly Core 6.0.2.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 12.2
>
>
> When running the following query (IMPORTANT: you shouldn't use row limit):
> {code:sql}
> begin
> declare integer i=0;
> while (i < 2)
> begin
> select *
> from "test_tables_pg.test_a" t0
> JOIN "test_tables_pg.test_e" t1
> ON true
> JOIN "test_tables_pg.test_e" t2
> ON true
> JOIN views.v t3
> on true
> JOIN views.v t4
> on true
> limit 257 ;
> i=i+1;
> end
> end ;;
> {code}
> teiid throws out the following NPE:
> {code:noformat}
> 2019-03-13 13:50:28,582 ERROR [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue233) InwGU8fhfbja TEIID30019 Unexpected exception for request InwGU8fhfbja.22: java.lang.NullPointerEx
> ception
> at org.teiid.query.processor.relational.MergeJoinStrategy.compareToPrevious(MergeJoinStrategy.java:284)
> at org.teiid.query.processor.relational.MergeJoinStrategy.process(MergeJoinStrategy.java:238)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:260)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> 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.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.BatchIterator.finalRow(BatchIterator.java:65)
> at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:66)
> at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:80)
> at org.teiid.common.buffer.AbstractTupleSource.nextTuple(AbstractTupleSource.java:44)
> at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(ProcedurePlan.java:303)
> at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:269)
> 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:492)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:362)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:43)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:285)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:281)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:113)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:199)
> 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.12.1#712002)
7 years
[JBoss JIRA] (TEIID-5686) NPE in MergeJoinStrategy when using joins with a particular limit value
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5686?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-5686:
----------------------------------
Priority: Major (was: Blocker)
This appears to be due to state handling in the nested select plan in the loop. The expected behavior of implicit cursor return should be to only have the last loop iteration return the result.
> NPE in MergeJoinStrategy when using joins with a particular limit value
> -----------------------------------------------------------------------
>
> Key: TEIID-5686
> URL: https://issues.jboss.org/browse/TEIID-5686
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 12.0
> Environment: teiid-12.0.0 on WildFly Full 14.0.1.Final (WildFly Core 6.0.2.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 12.2
>
>
> When running the following query (IMPORTANT: you shouldn't use row limit):
> {code:sql}
> begin
> declare integer i=0;
> while (i < 2)
> begin
> select *
> from "test_tables_pg.test_a" t0
> JOIN "test_tables_pg.test_e" t1
> ON true
> JOIN "test_tables_pg.test_e" t2
> ON true
> JOIN views.v t3
> on true
> JOIN views.v t4
> on true
> limit 257 ;
> i=i+1;
> end
> end ;;
> {code}
> teiid throws out the following NPE:
> {code:noformat}
> 2019-03-13 13:50:28,582 ERROR [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue233) InwGU8fhfbja TEIID30019 Unexpected exception for request InwGU8fhfbja.22: java.lang.NullPointerEx
> ception
> at org.teiid.query.processor.relational.MergeJoinStrategy.compareToPrevious(MergeJoinStrategy.java:284)
> at org.teiid.query.processor.relational.MergeJoinStrategy.process(MergeJoinStrategy.java:238)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:260)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> 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.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.BatchIterator.finalRow(BatchIterator.java:65)
> at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:66)
> at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:80)
> at org.teiid.common.buffer.AbstractTupleSource.nextTuple(AbstractTupleSource.java:44)
> at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(ProcedurePlan.java:303)
> at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:269)
> 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:492)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:362)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:43)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:285)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:281)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:113)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:199)
> 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.12.1#712002)
7 years
[JBoss JIRA] (TEIID-4508) DDL procedure update count not handled correctly
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-4508?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4508:
---------------------------------------
[~jolee] should be TEIID-4856
> DDL procedure update count not handled correctly
> ------------------------------------------------
>
> Key: TEIID-4508
> URL: https://issues.jboss.org/browse/TEIID-4508
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 7.0
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 9.1, 9.0.5
>
>
> The handling for procedure.updateCount was based upon the index file logic that effectively needed to have 1 subtracted from the value. So when ddl specified 2, it was actually be interpreted as 1. The index metadata workaround needs to be moved and the rest of the handling needs to be made consistent.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
7 years
[JBoss JIRA] (TEIID-5687) Oracle comparison with binding of nchar with non-ascii chars is not correct
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5687?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5687.
-----------------------------------
Resolution: Done
Resolving as a known issue. Oracle does not provide a good facility for this. If we bind as a fixed character string, then there is a character conversion. If we bind as nchar, the padding can be off. We can try to get the padding from the length, but that gets complicated for longer values. The database has a limit of 2000 chars and 2000 bytes, so it depends upon the encoding to determine what the actual padding could be.
> Oracle comparison with binding of nchar with non-ascii chars is not correct
> ---------------------------------------------------------------------------
>
> Key: TEIID-5687
> URL: https://issues.jboss.org/browse/TEIID-5687
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 12.2
>
>
> An nchar column binding in the where clause will use the FIXED_CHAR (999) oracle type - however that converts the value to ascii, which may result in invalid results:
> nchar_col = ?
> where ? is bound to Ā would search instead for A.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
7 years