[JBoss JIRA] (TEIID-3276) Data result not correct when joinning subquery over a date field
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3276?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-3276:
----------------------------------
Fix Version/s: (was: 8.10)
> Data result not correct when joinning subquery over a date field
> ----------------------------------------------------------------
>
> Key: TEIID-3276
> URL: https://issues.jboss.org/browse/TEIID-3276
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 7.7
> Reporter: Mark Tawk
> Assignee: Steven Hawkins
> Priority: Critical
>
> Hello,
> I'm using Teiid 8.9.0 To execute the below query and the execution is done without the problem; the issue is that I'm getting different results when executing the same query over mysql
> The original query is much bigger but i tried to simplify it to isolate the problem.
> SELECT DISTINCT
> WEEK(contactcalldetail.startdatetime) AS contactcalldetail_startdatet,
> sessionid_Sub.contactcalldetail_sessionid
> AS contactcalldetail_sessionid
> FROM db_cra_8F_VDB_DSModel.db_cra.contactcalldetail contactcalldetail
> LEFT JOIN /* optional */
> (SELECT COUNT(DISTINCT contactcalldetail_sub.sessionid)
> AS contactcalldetail_sessionid,
> WEEK(contactcalldetail_sub.startdatetime)
> AS contactcalldetail_startdatet
> FROM db_cra_8F_VDB_DSModel.db_cra.contactcalldetail contactcalldetail_sub
> WHERE (((contactcalldetail_sub.contacttype IN (1))))
> AND ((((contactcalldetail_sub.connecttime >= 300))))
> GROUP BY WEEK(contactcalldetail_sub.startdatetime)) sessionid_Sub
> ON (WEEK(contactcalldetail.startdatetime) =
> sessionid_Sub.contactcalldetail_startdatet)
> WHERE (((contactcalldetail.contacttype IN (1))))
> LIMIT 0, 10
> Here is Teiid execution plan :
> <?xml version='1.0' encoding='UTF-8'?><node name="LimitNode"><property name="Output Columns"><value>contactcalldetail_startdatet (integer)</value><value>contactcalldetail_sessionid (integer)</value></property><property name="Statistics"><value>Node Output Rows: 1</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 12</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 3</value><value>Node Blocks: 2</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: 10.0</value></property><property name="Child 0"><node name="SortNode"><property name="Output Columns"><value>contactcalldetail_startdatet (integer)</value><value>contactcalldetail_sessionid (integer)</value></property><property name="Statistics"><value>Node Output Rows: 1</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 12</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 3</value><value>Node Blocks: 2</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="ProjectNode"><property name="Output Columns"><value>contactcalldetail_startdatet (integer)</value><value>contactcalldetail_sessionid (integer)</value></property><property name="Statistics"><value>Node Output Rows: 4096</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 13</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 3</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="JoinNode"><property name="Output Columns"><value>startdatetime (timestamp)</value><value>contactcalldetail_sessionid (integer)</value></property><property name="Statistics"><value>Node Output Rows: 4096</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 11</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 3</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="AccessNode"><property name="Output Columns"><value>startdatetime (timestamp)</value><value>expr (integer)</value></property><property name="Statistics"><value>Node Output Rows: 4096</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 0</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 7</value><value>Node Blocks: 5</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT DISTINCT g_0.startdatetime AS c_0, WEEK(g_0.startdatetime) AS c_1 FROM db_cra_8F_VDB_DSModel.db_cra.contactcalldetail AS g_0 WHERE g_0.contacttype = 1 ORDER BY c_1</value></property><property name="Model Name"><value>db_cra_8F_VDB_DSModel</value></property></node></property><property name="Child 1"><node name="ProjectNode"><property name="Output Columns"><value>contactcalldetail_startdatet (integer)</value><value>contactcalldetail_sessionid (integer)</value></property><property name="Statistics"><value>Node Output Rows: 3</value><value>Node Next Batch Process Time: 7</value><value>Node Cumulative Next Batch Process Time: 8</value><value>Node Cumulative Process Time: 15</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="GroupingNode"><property name="Output Columns"><value>gcol0 (integer)</value><value>agg0 (integer)</value></property><property name="Statistics"><value>Node Output Rows: 3</value><value>Node Next Batch Process Time: 14</value><value>Node Cumulative Next Batch Process Time: 8</value><value>Node Cumulative Process Time: 15</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="AccessNode"><property name="Output Columns"><value>startdatetime (timestamp)</value><value>sessionid (long)</value></property><property name="Statistics"><value>Node Output Rows: 772</value><value>Node Next Batch Process Time: 9</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 9</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT g_0.startdatetime, g_0.sessionid FROM db_cra_8F_VDB_DSModel.db_cra.contactcalldetail AS g_0 WHERE (g_0.contacttype = 1) AND (g_0.connecttime >= 300)</value></property><property name="Model Name"><value>db_cra_8F_VDB_DSModel</value></property></node></property><property name="Grouping Columns"><value>WEEK(contactcalldetail_sub.startdatetime)</value></property><property name="Sort Mode"><value>false</value></property></node></property><property name="Select Columns"><value>anon_grp0.gcol0 AS contactcalldetail_startdatet</value><value>anon_grp0.agg0 AS contactcalldetail_sessionid</value></property></node></property><property name="Join Strategy"><value>MERGE JOIN (ALREADY_SORTED/SORT)</value></property><property name="Join Type"><value>LEFT OUTER JOIN</value></property><property name="Join Criteria"><value>WEEK(contactcalldetail.startdatetime)=sessionid_Sub.contactcalldetail_startdatet</value></property></node></property><property name="Select Columns"><value>WEEK(contactcalldetail.startdatetime) AS contactcalldetail_startdatet</value><value>sessionid_Sub.contactcalldetail_sessionid AS contactcalldetail_sessionid</value></property></node></property><property name="Sort Mode"><value>DUP_REMOVE</value></property></node></property><property name="Row Offset"><value>null</value></property><property name="Row Limit"><value>10</value></property></node>
> Here are the 2 JDBC queries executed by Teiid:
> SELECT DISTINCT g_0.`startdatetime` AS c_0, week(g_0.`startdatetime`) AS c_1 FROM `db_cra`.`contactcalldetail` AS g_0 WHERE g_0.`contacttype` = 1 ORDER BY c_1
> SELECT g_0.`startdatetime`, g_0.`sessionid` FROM `db_cra`.`contactcalldetail` AS g_0 WHERE g_0.`contacttype` = 1 AND g_0.`connecttime` >= 300
> Teiid query execution result is :
> week countsession
> 36 null
> 37 328
> 38 384
> mysql execution result:
> week countsession
> 35 328
> 36 384
> 37 60
> You can notice that the week is incremented by one and the count result is scrambled
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 10 months
[JBoss JIRA] (TEIID-3358) Issues with entity set names
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3358?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3358:
---------------------------------------
The short answer is yes. The long answer is that we could play with the notion of the entity set name - such as adding both the usual namespaced entry and a uniquely/generated name entry and switch to that entity set after resolving.
It seems simpler to just patch InternalUtil.getEntityRelId to use entitySet.getType().getFullyQualifiedTypeName() rather than entitySet.getName()
We could also just validate at load time and require the use of the entity set extension metadata if there is a possible conflict.
> Issues with entity set names
> ----------------------------
>
> Key: TEIID-3358
> URL: https://issues.jboss.org/browse/TEIID-3358
> Project: Teiid
> Issue Type: Bug
> Components: OData
> Affects Versions: 8.7
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 8.11
>
>
> The entity set name used for sql generation should be the fully qualified name. If there is for example two table with the same names in schemas visible to odata, but one of the tables does not have a key, then an ambiguous name exception will be thrown if an odata url is used with only the base table name.
> It also appears that the URI link in the results metadata uses the non-qualified table name, which will have issues in the scenario above.
> We may also need to document or add an additional check for ambiguity as the OData4j findEdmEntitySet logic will return the first matching entity, which is generally against our approach to resolving.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 10 months
[JBoss JIRA] (TEIID-3371) Upgrade Teiid with EAP 6.4 Alpha
by Van Halbert (JIRA)
Van Halbert created TEIID-3371:
----------------------------------
Summary: Upgrade Teiid with EAP 6.4 Alpha
Key: TEIID-3371
URL: https://issues.jboss.org/browse/TEIID-3371
Project: Teiid
Issue Type: Enhancement
Components: Build/Kits
Affects Versions: 8.11
Reporter: Van Halbert
Assignee: Steven Hawkins
Upgrade all the Teiid projects to be built based on EAP 6.4 Alpha. Currently, this would align with jboss-integration-platform-bom 6.0.0.CR25 and EAP Alpha version 7.5.0.Final-redhat-17. Note, 7.5.0.Final-redhat-17 is the version in the EAP kit, but only CR25 bom is available.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 10 months
[JBoss JIRA] (TEIID-3358) Issues with entity set names
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-3358?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-3358:
-------------------------------------
Do you see that this requires odata4j changes?
> Issues with entity set names
> ----------------------------
>
> Key: TEIID-3358
> URL: https://issues.jboss.org/browse/TEIID-3358
> Project: Teiid
> Issue Type: Bug
> Components: OData
> Affects Versions: 8.7
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 8.11
>
>
> The entity set name used for sql generation should be the fully qualified name. If there is for example two table with the same names in schemas visible to odata, but one of the tables does not have a key, then an ambiguous name exception will be thrown if an odata url is used with only the base table name.
> It also appears that the URI link in the results metadata uses the non-qualified table name, which will have issues in the scenario above.
> We may also need to document or add an additional check for ambiguity as the OData4j findEdmEntitySet logic will return the first matching entity, which is generally against our approach to resolving.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 10 months
[JBoss JIRA] (TEIID-3369) Add a extension point to manipulate incoming sql
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-3369:
-------------------------------------
Summary: Add a extension point to manipulate incoming sql
Key: TEIID-3369
URL: https://issues.jboss.org/browse/TEIID-3369
Project: Teiid
Issue Type: Feature Request
Components: JDBC Driver, Query Engine
Reporter: Steven Hawkins
Assignee: Steven Hawkins
We should look at adding a client or server extension point for manipulating sql - just the sql string, pre resolving. This will allow more flexibility in dealing with clients that don't send queries directly understood by Teiid. For example a select for update.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 10 months
[JBoss JIRA] (TEIID-3357) Add the final processor plan to the command log
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3357?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3357.
-----------------------------------
Resolution: Done
At a trace level we'll now output the initial plan with the plan event, and include the final plan with the close/cancel - which should include relevant node stats, bytes sent, etc.
Also add a plan property for the time spent in planning and more information to the enhanced sort to see what style of processing is ultimately done.
> Add the final processor plan to the command log
> -----------------------------------------------
>
> Key: TEIID-3357
> URL: https://issues.jboss.org/browse/TEIID-3357
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Affects Versions: 8.11
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 8.11
>
>
> We should add the plan output not just once planning has completed, but when processing has completed as well so that the node statistics have been populated.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 10 months