[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 commented on TEIID-3276:
---------------------------------------
> By this, we won't change anything at the mysql configuration.
Yes you should be able configure that on the mysql datasource. It looks like it should support a sessionVariables property in the url.
> 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
> Fix For: 8.10
>
>
> 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, 11 months
[JBoss JIRA] (TEIID-3276) Data result not correct when joinning subquery over a date field
by Mark Tawk (JIRA)
[ https://issues.jboss.org/browse/TEIID-3276?page=com.atlassian.jira.plugin... ]
Mark Tawk commented on TEIID-3276:
----------------------------------
For the solution you suggested. In fact, our application connect to our clients existing databases and we can not change into their mysql DB server configuration.
So is it possible to execute : SET default_week_format=3; in each mysql connection that teiid will be using. Or adding it to mysql connection context used by teiid?
By this, we won't change anything at the mysql configuration.
> 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
> Fix For: 8.10
>
>
> 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, 11 months
[JBoss JIRA] (TEIID-3285) Optional Left Join deleted while it has a condition in the where clause
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3285?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3285.
-----------------------------------
Resolution: Rejected
That is expected. From the docs:
"When a join clause is omitted via the optional join hint, the relevant criteria is not applied. Thus it is possible that the query results may not have the same cardinality or even the same row values as when the join is fully applied."
Only if you project a value from the optional join will we preserve it when there is a hint.
> Optional Left Join deleted while it has a condition in the where clause
> -----------------------------------------------------------------------
>
> Key: TEIID-3285
> URL: https://issues.jboss.org/browse/TEIID-3285
> Project: Teiid
> Issue Type: Bug
> Reporter: Mark Tawk
> Assignee: Steven Hawkins
> Priority: Critical
>
> I'm using Teiid 8.9.0.Alpha2
> I'm executing via Teiid the following query in which I have an optional left join with a where condition related to it.
> The JDBC query executed by Teiid is removing the optional join and the corresponding where condition.
> SELECT AVG(TicketViewRed.CalculatedField13) AS TicketViewRed_CalculatedFiel,
> TicketViewRed.jiraissue_PROJECT AS TicketViewRed_jiraissue_PROJ11
> FROM implifyBusinessModel.TicketViewRed TicketViewRed
> LEFT JOIN /* optional */ implifyBusinessModel.DimDate DimDate ON TicketViewRed.jiraissue_RESOLUTIONDATE = DimDate.DimDate_iTn_241214_Date
> WHERE (((TicketViewRed.jiraissue_PROJECT IN (10700, 11300))))
> AND ((DimDate.DimDate_iTn_241214_Date BETWEEN PARSETIMESTAMP('2014-01-01 00:00:00', 'yyyy-MM-dd HH:mm:ss')
> AND PARSETIMESTAMP('2014-09-02 00:00:00', 'yyyy-MM-dd HH:mm:ss')))
> GROUP BY TicketViewRed.jiraissue_PROJECT
> ORDER BY TicketViewRed.jiraissue_PROJECT DESC
> JDBC query executed by Teiid
> SELECT AVG(g_0.`CalculatedField13`) AS c_0, g_0.`jiraissue_PROJECT` AS c_1
> FROM `implify_view`.`TicketViewRed` AS g_0
> WHERE g_0.`jiraissue_PROJECT` IN (10700, 11300)
> GROUP BY g_0.`jiraissue_PROJECT` ORDER BY c_1 DESC
> And here is the corresponding execution plan:
> <?xml version='1.0' encoding='UTF-8'?><node name="AccessNode"><property name="Output Columns"><value>TicketViewRed_CalculatedFiel (double)</value><value>TicketViewRed_jiraissue_PROJ11 (long)</value></property><property name="Statistics"><value>Node Output Rows: 2</value><value>Node Next Batch Process Time: 9</value><value>Node Cumulative Next Batch Process Time: 0</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 AVG(g_0.CalculatedField13) AS c_0, g_0.jiraissue_PROJECT AS c_1 FROM implify_viewModel.implify_view.TicketViewRed AS g_0 WHERE g_0.jiraissue_PROJECT IN (10700, 11300) GROUP BY g_0.jiraissue_PROJECT ORDER BY c_1 DESC</value></property><property name="Model Name"><value>implify_viewModel</value></property></node>
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 11 months
[JBoss JIRA] (TEIID-3285) Optional Left Join deleted while it has a condition in the where clause
by Mark Tawk (JIRA)
Mark Tawk created TEIID-3285:
--------------------------------
Summary: Optional Left Join deleted while it has a condition in the where clause
Key: TEIID-3285
URL: https://issues.jboss.org/browse/TEIID-3285
Project: Teiid
Issue Type: Bug
Reporter: Mark Tawk
Assignee: Steven Hawkins
Priority: Critical
I'm using Teiid 8.9.0.Alpha2
I'm executing via Teiid the following query in which I have an optional left join with a where condition related to it.
The JDBC query executed by Teiid is removing the optional join and the corresponding where condition.
SELECT AVG(TicketViewRed.CalculatedField13) AS TicketViewRed_CalculatedFiel,
TicketViewRed.jiraissue_PROJECT AS TicketViewRed_jiraissue_PROJ11
FROM implifyBusinessModel.TicketViewRed TicketViewRed
LEFT JOIN /* optional */ implifyBusinessModel.DimDate DimDate ON TicketViewRed.jiraissue_RESOLUTIONDATE = DimDate.DimDate_iTn_241214_Date
WHERE (((TicketViewRed.jiraissue_PROJECT IN (10700, 11300))))
AND ((DimDate.DimDate_iTn_241214_Date BETWEEN PARSETIMESTAMP('2014-01-01 00:00:00', 'yyyy-MM-dd HH:mm:ss')
AND PARSETIMESTAMP('2014-09-02 00:00:00', 'yyyy-MM-dd HH:mm:ss')))
GROUP BY TicketViewRed.jiraissue_PROJECT
ORDER BY TicketViewRed.jiraissue_PROJECT DESC
JDBC query executed by Teiid
SELECT AVG(g_0.`CalculatedField13`) AS c_0, g_0.`jiraissue_PROJECT` AS c_1
FROM `implify_view`.`TicketViewRed` AS g_0
WHERE g_0.`jiraissue_PROJECT` IN (10700, 11300)
GROUP BY g_0.`jiraissue_PROJECT` ORDER BY c_1 DESC
And here is the corresponding execution plan:
<?xml version='1.0' encoding='UTF-8'?><node name="AccessNode"><property name="Output Columns"><value>TicketViewRed_CalculatedFiel (double)</value><value>TicketViewRed_jiraissue_PROJ11 (long)</value></property><property name="Statistics"><value>Node Output Rows: 2</value><value>Node Next Batch Process Time: 9</value><value>Node Cumulative Next Batch Process Time: 0</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 AVG(g_0.CalculatedField13) AS c_0, g_0.jiraissue_PROJECT AS c_1 FROM implify_viewModel.implify_view.TicketViewRed AS g_0 WHERE g_0.jiraissue_PROJECT IN (10700, 11300) GROUP BY g_0.jiraissue_PROJECT ORDER BY c_1 DESC</value></property><property name="Model Name"><value>implify_viewModel</value></property></node>
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 11 months
[JBoss JIRA] (TEIID-2816) HBase Connector/Translator
by Kylin Soong (JIRA)
[ https://issues.jboss.org/browse/TEIID-2816?page=com.atlassian.jira.plugin... ]
Kylin Soong commented on TEIID-2816:
------------------------------------
> Following the behavior of the other JDBC translators we expect the name in source to use any expected quoting.
That's is reasonable and a better way to solve Phoenix upper casing issue.
> or if it's an expected precondition that the appropriate Phoenix tables will have been created.
In this way, current logic can do further simplify, we do not need define properties for column families, it just like a standard jdbc translator
> HBase Connector/Translator
> --------------------------
>
> Key: TEIID-2816
> URL: https://issues.jboss.org/browse/TEIID-2816
> Project: Teiid
> Issue Type: Feature Request
> Components: Connector API, JDBC Connector, JDBC Driver
> Affects Versions: Open To Community
> Reporter: devin pinkston
> Assignee: Kylin Soong
> Labels: HBase, Translator
> Fix For: 8.10
>
>
> Develop a HBase translator/connector. Potentially using the Phoenix JDBC driver, supports most SQL and handles connections:
> https://github.com/forcedotcom/phoenix
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 11 months
[JBoss JIRA] (TEIID-2816) HBase Connector/Translator
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2816?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-2816:
---------------------------------------
> We need add double quotes for all table name, qualifiers from nameinsource and columnFamilies from properties in Hbase Table Mapping DDL, cause as [1], Phoenix will upper casing all of them.
Following the behavior of the other JDBC translators we expect the name in source to use any expected quoting.
Probably the most important thing is to first determine whether we expect Teiid to issue any DDL at all, or if it's an expected precondition that the appropriate Phoenix tables will have been created.
> HBase Connector/Translator
> --------------------------
>
> Key: TEIID-2816
> URL: https://issues.jboss.org/browse/TEIID-2816
> Project: Teiid
> Issue Type: Feature Request
> Components: Connector API, JDBC Connector, JDBC Driver
> Affects Versions: Open To Community
> Reporter: devin pinkston
> Assignee: Kylin Soong
> Labels: HBase, Translator
> Fix For: 8.10
>
>
> Develop a HBase translator/connector. Potentially using the Phoenix JDBC driver, supports most SQL and handles connections:
> https://github.com/forcedotcom/phoenix
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 11 months
[JBoss JIRA] (TEIID-2816) HBase Connector/Translator
by Kylin Soong (JIRA)
[ https://issues.jboss.org/browse/TEIID-2816?page=com.atlassian.jira.plugin... ]
Kylin Soong edited comment on TEIID-2816 at 1/14/15 4:41 AM:
-------------------------------------------------------------
We need add double quotes for all table name, qualifiers from nameinsource and columnFamilies from properties in Hbase Table Mapping DDL, cause as [1], Phoenix will upper casing all of them. For example, use 'CREATE TABLE IF NOT EXISTS Customer(...' to map 'Customer' table in HBase, it will be cast to 'CREATE TABLE IF NOT EXISTS CUSTOMER(...', since CUSTOMER not exist in HBase, Phoenix will create a new one, rather than map to 'Customer', the same way in qualifiers and columnFamilies. This also affect the SQLConversionVisitor in runtime, we may need rewrite visit() method for adding the double quotes.
[1] http://phoenix.apache.org/faq.html#How_I_map_Phoenix_table_to_an_existing...
was (Author: kylin):
We need add double quotes for all table name, qualifiers from nameinsource and columnFamilies from properties in Hbase Table Mapping DDL, cause as [1], Phoenix will upper casing all of them. For example, use 'CREATE TABLE IF NOT EXISTS Customer(...' to map 'Customer' table in HBase, it will be cast to 'CREATE TABLE IF NOT EXISTS CUSTOMER(...', since CUSTOMER not exist in HBase, Phoenix will create a new one, rather than map to 'Customer', the same way in qualifiers and columnFamilies. This also affect the SQLConversionVisitor in runtime, me may need rewrite visit() method for adding the double quotes.
[1] http://phoenix.apache.org/faq.html#How_I_map_Phoenix_table_to_an_existing...
> HBase Connector/Translator
> --------------------------
>
> Key: TEIID-2816
> URL: https://issues.jboss.org/browse/TEIID-2816
> Project: Teiid
> Issue Type: Feature Request
> Components: Connector API, JDBC Connector, JDBC Driver
> Affects Versions: Open To Community
> Reporter: devin pinkston
> Assignee: Kylin Soong
> Labels: HBase, Translator
> Fix For: 8.10
>
>
> Develop a HBase translator/connector. Potentially using the Phoenix JDBC driver, supports most SQL and handles connections:
> https://github.com/forcedotcom/phoenix
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 11 months
[JBoss JIRA] (TEIID-2816) HBase Connector/Translator
by Kylin Soong (JIRA)
[ https://issues.jboss.org/browse/TEIID-2816?page=com.atlassian.jira.plugin... ]
Kylin Soong commented on TEIID-2816:
------------------------------------
We need add double quotes for all table name, qualifiers from nameinsource and columnFamilies from properties in Hbase Table Mapping DDL, cause as [1], Phoenix will upper casing all of them. For example, use 'CREATE TABLE IF NOT EXISTS Customer(...' to map 'Customer' table in HBase, it will be cast to 'CREATE TABLE IF NOT EXISTS CUSTOMER(...', since CUSTOMER not exist in HBase, Phoenix will create a new one, rather than map to 'Customer', the same way in qualifiers and columnFamilies. This also affect the SQLConversionVisitor in runtime, me may need rewrite visit() method for adding the double quotes.
[1] http://phoenix.apache.org/faq.html#How_I_map_Phoenix_table_to_an_existing...
> HBase Connector/Translator
> --------------------------
>
> Key: TEIID-2816
> URL: https://issues.jboss.org/browse/TEIID-2816
> Project: Teiid
> Issue Type: Feature Request
> Components: Connector API, JDBC Connector, JDBC Driver
> Affects Versions: Open To Community
> Reporter: devin pinkston
> Assignee: Kylin Soong
> Labels: HBase, Translator
> Fix For: 8.10
>
>
> Develop a HBase translator/connector. Potentially using the Phoenix JDBC driver, supports most SQL and handles connections:
> https://github.com/forcedotcom/phoenix
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 11 months