[JBoss JIRA] (TEIID-2384) Managing Spatial Data Types
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2384?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-2384:
---------------------------------------
Pulled in another set of commits from Tom with some additional refinements.
TODO:
- determine how the SRID will be associated when reading.
- jts does not care if the srid matches when performing the various system functions. We may need to restrict to only matching srids to stay consistent with expected source behavior.
- oracle …
[View More]and others seem to use null rather than 0 for an unknown srid. So we may want to use an Integer field on the GeometryType and update the FROM Text/Binary methods appropriately.
- the mysql distance function may not be appropriate for pushdown since at least in 5.6 it was somewhat of an approximation.
- add postgis support to the pg translator
- add get/set srid functions
> Managing Spatial Data Types
> ---------------------------
>
> Key: TEIID-2384
> URL: https://issues.jboss.org/browse/TEIID-2384
> Project: Teiid
> Issue Type: Feature Request
> Components: Query Engine
> Affects Versions: 8.2
> Reporter: luca gioppo
> Assignee: Steven Hawkins
> Labels: spatial, types
> Fix For: 8.10
>
>
> It would be useful to be able to consume data from spatial database exposing the VDB as a spatial database to other application (imagine geoserver).
> TEIID could be strategic for merging georeferenced data and make it available to those systems.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
[View Less]
9 years, 11 months
[JBoss JIRA] (TEIID-3292) Add Geometry Type
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-3292:
-------------------------------------
Summary: Add Geometry Type
Key: TEIID-3292
URL: https://issues.jboss.org/browse/TEIID-3292
Project: Teiid
Issue Type: Feature Request
Components: Query Engine
Affects Versions: 8.2
Reporter: luca gioppo
Assignee: Steven Hawkins
Fix For: 8.10
It would be useful to be able to consume data from spatial …
[View More]database exposing the VDB as a spatial database to other application (imagine geoserver).
TEIID could be strategic for merging georeferenced data and make it available to those systems.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
[View Less]
9 years, 11 months
[JBoss JIRA] (TEIID-3289) Join subquery containing ifnull in join condition
by Mark Tawk (JIRA)
[ https://issues.jboss.org/browse/TEIID-3289?page=com.atlassian.jira.plugin... ]
Mark Tawk commented on TEIID-3289:
----------------------------------
Ok I'll prepare you a mysql script of a table with the corresponding fields and several records of Data.
And the Sql query over that sample table that reproduces the case.
> Join subquery containing ifnull in join condition
> -------------------------------------------------
>
> Key: TEIID-3289
> …
[View More]URL: https://issues.jboss.org/browse/TEIID-3289
> Project: Teiid
> Issue Type: Feature Request
> Reporter: Mark Tawk
> Assignee: Steven Hawkins
> Priority: Critical
>
> I have join subquery linked to main query via 2 fields in the query below.
> To be able to join null values between main query and subquery, I have added ifnull to join fields with a random value.
> If I run my query directly over mysql, the field CalculatedField1 return values whenever the field RESOLUTIONDATE is null in the main query.
> But when executed in Teiid, the query returns always null for the field CalculatedField1.
> When looking at the jdbc queries executed by Teiid, I found that ifnull is used for the main query fields but not for the subquery fields, which may be causing a link problem between main query and subquery.
> I have tried to put the ifnull into the subquery but still getting jdbc subquery fields without the ifnull function.
> SELECT DISTINCT
> jiraissue.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE,
> jiraissue.CREATED AS jiraissue_CREATED,
> CalculatedField1_Sub.CalculatedField1 AS CalculatedField1
> FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue jiraissue
> LEFT JOIN
> (SELECT DISTINCT
> TIMESTAMPDIFF(
> SQL_TSI_MINUTE,
> jiraissue_sub.CREATED,
> PARSETIMESTAMP('2014-10-03 00:00:00',
> 'yyyy-MM-dd HH:mm:ss'))
> AS CalculatedField1,
> jiraissue_sub.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE,
> jiraissue_sub.CREATED AS jiraissue_CREATED
> FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue jiraissue_sub
> WHERE ((((jiraissue_sub.RESOLUTIONDATE IS NULL)))))
> CalculatedField1_Sub
> ON (IFNULL(jiraissue.RESOLUTIONDATE, -2147483648) =
> IFNULL(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE,
> -2147483648))
> AND (IFNULL(jiraissue.CREATED, -2147483648) =
> IFNULL(CalculatedField1_Sub.jiraissue_CREATED,
> -2147483648))
> Teiid JDBC queries:
> SELECT DISTINCT g_0.`RESOLUTIONDATE` AS c_0, g_0.`CREATED` AS c_1, ifnull(date_format(g_0.`RESOLUTIONDATE`, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648') AS c_2, ifnull(date_format(g_0.`CREATED`, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648') AS c_3 FROM `tjiradb`.`jiraissue` AS g_0 ORDER BY c_2, c_3
> SELECT g_0.`CREATED`, g_0.`RESOLUTIONDATE` FROM `tjiradb`.`jiraissue` AS g_0 WHERE g_0.`RESOLUTIONDATE` IS NULL
> Teiid Execution plan
>
> <?xml version='1.0' encoding='UTF-8'?><node name="LimitNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 10</value><value>Node Next Batch Process Time: 7</value><value>Node Cumulative Next Batch Process Time: 43</value><value>Node Cumulative Process Time: 49</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 3</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>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 10</value><value>Node Next Batch Process Time: 13</value><value>Node Cumulative Next Batch Process Time: 42</value><value>Node Cumulative Process Time: 48</value><value>Node Next Batch Calls: 4</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="ProjectNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 35</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 4</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>RESOLUTIONDATE (timestamp)</value><value>CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 35</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 4</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>RESOLUTIONDATE (timestamp)</value><value>CREATED (timestamp)</value><value>expr (string)</value><value>expr (string)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 10</value><value>Node Blocks: 6</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT DISTINCT g_0.RESOLUTIONDATE AS c_0, g_0.CREATED AS c_1, IFNULL(convert(g_0.RESOLUTIONDATE, string), '-2147483648') AS c_2, IFNULL(convert(g_0.CREATED, string), '-2147483648') AS c_3 FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_0 ORDER BY c_2, c_3</value></property><property name="Model Name"><value>tjiradb_D1_VDB_DSModel</value></property></node></property><property name="Child 1"><node name="ProjectNode"><property name="Output Columns"><value>CalculatedField1 (long)</value><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField11 (long)</value><value>expr (string)</value><value>expr (string)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 22</value><value>Node Cumulative Next Batch Process Time: 18</value><value>Node Cumulative Process Time: 25</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 1</value></property><property name="Child 0"><node name="ProjectNode"><property name="Output Columns"><value>CalculatedField1 (long)</value><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField11 (long)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 9</value><value>Node Cumulative Next Batch Process Time: 3</value><value>Node Cumulative Process Time: 10</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>CREATED (timestamp)</value><value>RESOLUTIONDATE (timestamp)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 8</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 8</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.CREATED, g_0.RESOLUTIONDATE FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_0 WHERE g_0.RESOLUTIONDATE IS NULL</value></property><property name="Model Name"><value>tjiradb_D1_VDB_DSModel</value></property></node></property><property name="Select Columns"><value>TIMESTAMPDIFF(SQL_TSI_MINUTE, jiraissue_sub.CREATED, {ts'2014-10-03 00:00:00.0'}) AS CalculatedField1</value><value>jiraissue_sub.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE</value><value>jiraissue_sub.CREATED AS jiraissue_CREATED</value><value>TIMESTAMPDIFF(SQL_TSI_MINUTE, jiraissue_sub.CREATED, {ts'2014-10-03 00:00:00.0'}) AS CalculatedField11</value></property></node></property><property name="Select Columns"><value>CalculatedField1_Sub.CalculatedField1</value><value>CalculatedField1_Sub.jiraissue_RESOLUTIONDATE</value><value>CalculatedField1_Sub.jiraissue_CREATED</value><value>CalculatedField1_Sub.CalculatedField11</value><value>IFNULL(convert(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE, string), '-2147483648')</value><value>IFNULL(convert(CalculatedField1_Sub.jiraissue_CREATED, string), '-2147483648')</value></property></node></property><property name="Join Strategy"><value>MERGE JOIN (ALREADY_SORTED/SORT_DISTINCT)</value></property><property name="Join Type"><value>LEFT OUTER JOIN</value></property><property name="Join Criteria"><value>IFNULL(convert(jiraissue.RESOLUTIONDATE, string), '-2147483648')=IFNULL(convert(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE, string), '-2147483648')</value><value>IFNULL(convert(jiraissue.CREATED, string), '-2147483648')=IFNULL(convert(CalculatedField1_Sub.jiraissue_CREATED, string), '-2147483648')</value></property></node></property><property name="Select Columns"><value>jiraissue.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE</value><value>jiraissue.CREATED AS jiraissue_CREATED</value><value>CalculatedField1_Sub.CalculatedField1 AS CalculatedField1</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>
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
[View Less]
9 years, 11 months
[JBoss JIRA] (TEIID-3289) Join subquery containing ifnull in join condition
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3289?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3289:
---------------------------------------
> Because from the jdbc queries, the ifnull is sent only for the main query.
You can see in the plan that it is still applied in the join criteria even if it is not pushed down.
Can you provide some sample data from each of the source queries and I can try to reproduce what you are seeing?
> Join subquery containing ifnull …
[View More]in join condition
> -------------------------------------------------
>
> Key: TEIID-3289
> URL: https://issues.jboss.org/browse/TEIID-3289
> Project: Teiid
> Issue Type: Feature Request
> Reporter: Mark Tawk
> Assignee: Steven Hawkins
> Priority: Critical
>
> I have join subquery linked to main query via 2 fields in the query below.
> To be able to join null values between main query and subquery, I have added ifnull to join fields with a random value.
> If I run my query directly over mysql, the field CalculatedField1 return values whenever the field RESOLUTIONDATE is null in the main query.
> But when executed in Teiid, the query returns always null for the field CalculatedField1.
> When looking at the jdbc queries executed by Teiid, I found that ifnull is used for the main query fields but not for the subquery fields, which may be causing a link problem between main query and subquery.
> I have tried to put the ifnull into the subquery but still getting jdbc subquery fields without the ifnull function.
> SELECT DISTINCT
> jiraissue.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE,
> jiraissue.CREATED AS jiraissue_CREATED,
> CalculatedField1_Sub.CalculatedField1 AS CalculatedField1
> FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue jiraissue
> LEFT JOIN
> (SELECT DISTINCT
> TIMESTAMPDIFF(
> SQL_TSI_MINUTE,
> jiraissue_sub.CREATED,
> PARSETIMESTAMP('2014-10-03 00:00:00',
> 'yyyy-MM-dd HH:mm:ss'))
> AS CalculatedField1,
> jiraissue_sub.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE,
> jiraissue_sub.CREATED AS jiraissue_CREATED
> FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue jiraissue_sub
> WHERE ((((jiraissue_sub.RESOLUTIONDATE IS NULL)))))
> CalculatedField1_Sub
> ON (IFNULL(jiraissue.RESOLUTIONDATE, -2147483648) =
> IFNULL(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE,
> -2147483648))
> AND (IFNULL(jiraissue.CREATED, -2147483648) =
> IFNULL(CalculatedField1_Sub.jiraissue_CREATED,
> -2147483648))
> Teiid JDBC queries:
> SELECT DISTINCT g_0.`RESOLUTIONDATE` AS c_0, g_0.`CREATED` AS c_1, ifnull(date_format(g_0.`RESOLUTIONDATE`, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648') AS c_2, ifnull(date_format(g_0.`CREATED`, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648') AS c_3 FROM `tjiradb`.`jiraissue` AS g_0 ORDER BY c_2, c_3
> SELECT g_0.`CREATED`, g_0.`RESOLUTIONDATE` FROM `tjiradb`.`jiraissue` AS g_0 WHERE g_0.`RESOLUTIONDATE` IS NULL
> Teiid Execution plan
>
> <?xml version='1.0' encoding='UTF-8'?><node name="LimitNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 10</value><value>Node Next Batch Process Time: 7</value><value>Node Cumulative Next Batch Process Time: 43</value><value>Node Cumulative Process Time: 49</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 3</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>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 10</value><value>Node Next Batch Process Time: 13</value><value>Node Cumulative Next Batch Process Time: 42</value><value>Node Cumulative Process Time: 48</value><value>Node Next Batch Calls: 4</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="ProjectNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 35</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 4</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>RESOLUTIONDATE (timestamp)</value><value>CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 35</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 4</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>RESOLUTIONDATE (timestamp)</value><value>CREATED (timestamp)</value><value>expr (string)</value><value>expr (string)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 10</value><value>Node Blocks: 6</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT DISTINCT g_0.RESOLUTIONDATE AS c_0, g_0.CREATED AS c_1, IFNULL(convert(g_0.RESOLUTIONDATE, string), '-2147483648') AS c_2, IFNULL(convert(g_0.CREATED, string), '-2147483648') AS c_3 FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_0 ORDER BY c_2, c_3</value></property><property name="Model Name"><value>tjiradb_D1_VDB_DSModel</value></property></node></property><property name="Child 1"><node name="ProjectNode"><property name="Output Columns"><value>CalculatedField1 (long)</value><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField11 (long)</value><value>expr (string)</value><value>expr (string)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 22</value><value>Node Cumulative Next Batch Process Time: 18</value><value>Node Cumulative Process Time: 25</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 1</value></property><property name="Child 0"><node name="ProjectNode"><property name="Output Columns"><value>CalculatedField1 (long)</value><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField11 (long)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 9</value><value>Node Cumulative Next Batch Process Time: 3</value><value>Node Cumulative Process Time: 10</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>CREATED (timestamp)</value><value>RESOLUTIONDATE (timestamp)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 8</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 8</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.CREATED, g_0.RESOLUTIONDATE FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_0 WHERE g_0.RESOLUTIONDATE IS NULL</value></property><property name="Model Name"><value>tjiradb_D1_VDB_DSModel</value></property></node></property><property name="Select Columns"><value>TIMESTAMPDIFF(SQL_TSI_MINUTE, jiraissue_sub.CREATED, {ts'2014-10-03 00:00:00.0'}) AS CalculatedField1</value><value>jiraissue_sub.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE</value><value>jiraissue_sub.CREATED AS jiraissue_CREATED</value><value>TIMESTAMPDIFF(SQL_TSI_MINUTE, jiraissue_sub.CREATED, {ts'2014-10-03 00:00:00.0'}) AS CalculatedField11</value></property></node></property><property name="Select Columns"><value>CalculatedField1_Sub.CalculatedField1</value><value>CalculatedField1_Sub.jiraissue_RESOLUTIONDATE</value><value>CalculatedField1_Sub.jiraissue_CREATED</value><value>CalculatedField1_Sub.CalculatedField11</value><value>IFNULL(convert(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE, string), '-2147483648')</value><value>IFNULL(convert(CalculatedField1_Sub.jiraissue_CREATED, string), '-2147483648')</value></property></node></property><property name="Join Strategy"><value>MERGE JOIN (ALREADY_SORTED/SORT_DISTINCT)</value></property><property name="Join Type"><value>LEFT OUTER JOIN</value></property><property name="Join Criteria"><value>IFNULL(convert(jiraissue.RESOLUTIONDATE, string), '-2147483648')=IFNULL(convert(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE, string), '-2147483648')</value><value>IFNULL(convert(jiraissue.CREATED, string), '-2147483648')=IFNULL(convert(CalculatedField1_Sub.jiraissue_CREATED, string), '-2147483648')</value></property></node></property><property name="Select Columns"><value>jiraissue.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE</value><value>jiraissue.CREATED AS jiraissue_CREATED</value><value>CalculatedField1_Sub.CalculatedField1 AS CalculatedField1</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>
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
[View Less]
9 years, 11 months
[JBoss JIRA] (TEIID-3289) Join subquery containing ifnull in join condition
by Mark Tawk (JIRA)
[ https://issues.jboss.org/browse/TEIID-3289?page=com.atlassian.jira.plugin... ]
Mark Tawk edited comment on TEIID-3289 at 1/20/15 2:16 AM:
-----------------------------------------------------------
Teiid version is 8.9.0Alpha2
Mysql 5.7
Translator : mysql5
I have removed TimeStampDiff and put only the concerned field into the subquery.
Now the subquery is returning values.
But Teiid is now executing one jdbc query, you can see that the join condition with ifnull is written into the jdbc …
[View More]query generated by Teiid.
So for the first case containing TimeStampDiff, I morelikely suspect the ifnull function that links the main query to the subquery, I don't know if it is well applied on Teiid level for the subquery. Because from the jdbc queries, the ifnull is sent only for the main query.
If i remove the ifnull function from the join condition, mysql is giving null to all subquery value, same as teiid. But this is not the comportment that i need.
Here is the execution plan when TimeStampDiff is removed:
<?xml version='1.0' encoding='UTF-8'?><node name="AccessNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (timestamp)</value></property><property name="Statistics"><value>Node Output Rows: 10</value><value>Node Next Batch Process Time: 4</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 4</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: 10.0</value></property><property name="Query"><value>SELECT DISTINCT g_0.RESOLUTIONDATE AS c_0, g_0.CREATED AS c_1, v_0.c_0 AS c_2 FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_0 LEFT OUTER JOIN (SELECT DISTINCT g_1.CREATED AS c_0, g_1.RESOLUTIONDATE AS c_1, g_1.CREATED AS c_2, g_1.CREATED AS c_3 FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_1 WHERE (g_1.RESOLUTIONDATE IS NOT NULL) AND (g_1.RESOLUTIONDATE IS NULL)) AS v_0 ON IFNULL(convert(g_0.RESOLUTIONDATE, string), '-2147483648') = IFNULL(convert(v_0.c_1, string), '-2147483648') AND IFNULL(convert(g_0.CREATED, string), '-2147483648') = IFNULL(convert(v_0.c_2, string), '-2147483648') WHERE g_0.RESOLUTIONDATE IS NOT NULL LIMIT 10</value></property><property name="Model Name"><value>tjiradb_D1_VDB_DSModel</value></property></node>
was (Author: mtawk):
Teiid version is 8.9.0Alpha2
Mysql 5.7
Translator : mysql5
I have removed TimeStampDiff and put only the concerned field into the subquery.
Now the subquery is returning values.
But the jdbc queries executed by Teiid are totally different, we can see that the join condition with ifnull is written into the jdbc query generated by Teiid.
So for the first case containing TimeStampDiff, I morelikely suspect the ifnull function that links the main query to the subquery, I don't know if it is well applied on Teiid level for the subquery. Because from the jdbc queries, the ifnull is sent only for the main query.
If i remove the ifnull function from the join condition, mysql is giving null to all subquery value, same as teiid. But this is not the comportment that i need.
Here is the JDBC queries and execution plan when TimeStampDiff is removed:
SELECT DISTINCT g_0.`RESOLUTIONDATE` AS c_0 FROM `tjiradb`.`jiraissue` AS g_0 ORDER BY c_0 LIMIT 10
SELECT DISTINCT g_0.`RESOLUTIONDATE` AS c_0, g_0.`CREATED` AS c_1, v_0.c_0 AS c_2
FROM `tjiradb`.`jiraissue` AS g_0
LEFT OUTER JOIN (SELECT DISTINCT g_1.`CREATED` AS c_0, g_1.`RESOLUTIONDATE` AS c_1, g_1.`CREATED` AS c_2, g_1.`CREATED` AS c_3
FROM `tjiradb`.`jiraissue` AS g_1
WHERE g_1.`RESOLUTIONDATE` IS NOT NULL AND g_1.`RESOLUTIONDATE` IS NULL) AS v_0
ON ifnull(date_format(g_0.`RESOLUTIONDATE`, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648') = ifnull(date_format(v_0.c_1, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648') AND ifnull(date_format(g_0.`CREATED`, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648') = ifnull(date_format(v_0.c_2, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648')
WHERE g_0.`RESOLUTIONDATE` IS NOT NULL LIMIT 10
<?xml version='1.0' encoding='UTF-8'?><node name="AccessNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (timestamp)</value></property><property name="Statistics"><value>Node Output Rows: 10</value><value>Node Next Batch Process Time: 4</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 4</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: 10.0</value></property><property name="Query"><value>SELECT DISTINCT g_0.RESOLUTIONDATE AS c_0, g_0.CREATED AS c_1, v_0.c_0 AS c_2 FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_0 LEFT OUTER JOIN (SELECT DISTINCT g_1.CREATED AS c_0, g_1.RESOLUTIONDATE AS c_1, g_1.CREATED AS c_2, g_1.CREATED AS c_3 FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_1 WHERE (g_1.RESOLUTIONDATE IS NOT NULL) AND (g_1.RESOLUTIONDATE IS NULL)) AS v_0 ON IFNULL(convert(g_0.RESOLUTIONDATE, string), '-2147483648') = IFNULL(convert(v_0.c_1, string), '-2147483648') AND IFNULL(convert(g_0.CREATED, string), '-2147483648') = IFNULL(convert(v_0.c_2, string), '-2147483648') WHERE g_0.RESOLUTIONDATE IS NOT NULL LIMIT 10</value></property><property name="Model Name"><value>tjiradb_D1_VDB_DSModel</value></property></node>
> Join subquery containing ifnull in join condition
> -------------------------------------------------
>
> Key: TEIID-3289
> URL: https://issues.jboss.org/browse/TEIID-3289
> Project: Teiid
> Issue Type: Feature Request
> Reporter: Mark Tawk
> Assignee: Steven Hawkins
> Priority: Critical
>
> I have join subquery linked to main query via 2 fields in the query below.
> To be able to join null values between main query and subquery, I have added ifnull to join fields with a random value.
> If I run my query directly over mysql, the field CalculatedField1 return values whenever the field RESOLUTIONDATE is null in the main query.
> But when executed in Teiid, the query returns always null for the field CalculatedField1.
> When looking at the jdbc queries executed by Teiid, I found that ifnull is used for the main query fields but not for the subquery fields, which may be causing a link problem between main query and subquery.
> I have tried to put the ifnull into the subquery but still getting jdbc subquery fields without the ifnull function.
> SELECT DISTINCT
> jiraissue.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE,
> jiraissue.CREATED AS jiraissue_CREATED,
> CalculatedField1_Sub.CalculatedField1 AS CalculatedField1
> FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue jiraissue
> LEFT JOIN
> (SELECT DISTINCT
> TIMESTAMPDIFF(
> SQL_TSI_MINUTE,
> jiraissue_sub.CREATED,
> PARSETIMESTAMP('2014-10-03 00:00:00',
> 'yyyy-MM-dd HH:mm:ss'))
> AS CalculatedField1,
> jiraissue_sub.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE,
> jiraissue_sub.CREATED AS jiraissue_CREATED
> FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue jiraissue_sub
> WHERE ((((jiraissue_sub.RESOLUTIONDATE IS NULL)))))
> CalculatedField1_Sub
> ON (IFNULL(jiraissue.RESOLUTIONDATE, -2147483648) =
> IFNULL(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE,
> -2147483648))
> AND (IFNULL(jiraissue.CREATED, -2147483648) =
> IFNULL(CalculatedField1_Sub.jiraissue_CREATED,
> -2147483648))
> Teiid JDBC queries:
> SELECT DISTINCT g_0.`RESOLUTIONDATE` AS c_0, g_0.`CREATED` AS c_1, ifnull(date_format(g_0.`RESOLUTIONDATE`, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648') AS c_2, ifnull(date_format(g_0.`CREATED`, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648') AS c_3 FROM `tjiradb`.`jiraissue` AS g_0 ORDER BY c_2, c_3
> SELECT g_0.`CREATED`, g_0.`RESOLUTIONDATE` FROM `tjiradb`.`jiraissue` AS g_0 WHERE g_0.`RESOLUTIONDATE` IS NULL
> Teiid Execution plan
>
> <?xml version='1.0' encoding='UTF-8'?><node name="LimitNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 10</value><value>Node Next Batch Process Time: 7</value><value>Node Cumulative Next Batch Process Time: 43</value><value>Node Cumulative Process Time: 49</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 3</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>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 10</value><value>Node Next Batch Process Time: 13</value><value>Node Cumulative Next Batch Process Time: 42</value><value>Node Cumulative Process Time: 48</value><value>Node Next Batch Calls: 4</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="ProjectNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 35</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 4</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>RESOLUTIONDATE (timestamp)</value><value>CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 35</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 4</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>RESOLUTIONDATE (timestamp)</value><value>CREATED (timestamp)</value><value>expr (string)</value><value>expr (string)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 10</value><value>Node Blocks: 6</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT DISTINCT g_0.RESOLUTIONDATE AS c_0, g_0.CREATED AS c_1, IFNULL(convert(g_0.RESOLUTIONDATE, string), '-2147483648') AS c_2, IFNULL(convert(g_0.CREATED, string), '-2147483648') AS c_3 FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_0 ORDER BY c_2, c_3</value></property><property name="Model Name"><value>tjiradb_D1_VDB_DSModel</value></property></node></property><property name="Child 1"><node name="ProjectNode"><property name="Output Columns"><value>CalculatedField1 (long)</value><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField11 (long)</value><value>expr (string)</value><value>expr (string)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 22</value><value>Node Cumulative Next Batch Process Time: 18</value><value>Node Cumulative Process Time: 25</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 1</value></property><property name="Child 0"><node name="ProjectNode"><property name="Output Columns"><value>CalculatedField1 (long)</value><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField11 (long)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 9</value><value>Node Cumulative Next Batch Process Time: 3</value><value>Node Cumulative Process Time: 10</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>CREATED (timestamp)</value><value>RESOLUTIONDATE (timestamp)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 8</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 8</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.CREATED, g_0.RESOLUTIONDATE FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_0 WHERE g_0.RESOLUTIONDATE IS NULL</value></property><property name="Model Name"><value>tjiradb_D1_VDB_DSModel</value></property></node></property><property name="Select Columns"><value>TIMESTAMPDIFF(SQL_TSI_MINUTE, jiraissue_sub.CREATED, {ts'2014-10-03 00:00:00.0'}) AS CalculatedField1</value><value>jiraissue_sub.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE</value><value>jiraissue_sub.CREATED AS jiraissue_CREATED</value><value>TIMESTAMPDIFF(SQL_TSI_MINUTE, jiraissue_sub.CREATED, {ts'2014-10-03 00:00:00.0'}) AS CalculatedField11</value></property></node></property><property name="Select Columns"><value>CalculatedField1_Sub.CalculatedField1</value><value>CalculatedField1_Sub.jiraissue_RESOLUTIONDATE</value><value>CalculatedField1_Sub.jiraissue_CREATED</value><value>CalculatedField1_Sub.CalculatedField11</value><value>IFNULL(convert(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE, string), '-2147483648')</value><value>IFNULL(convert(CalculatedField1_Sub.jiraissue_CREATED, string), '-2147483648')</value></property></node></property><property name="Join Strategy"><value>MERGE JOIN (ALREADY_SORTED/SORT_DISTINCT)</value></property><property name="Join Type"><value>LEFT OUTER JOIN</value></property><property name="Join Criteria"><value>IFNULL(convert(jiraissue.RESOLUTIONDATE, string), '-2147483648')=IFNULL(convert(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE, string), '-2147483648')</value><value>IFNULL(convert(jiraissue.CREATED, string), '-2147483648')=IFNULL(convert(CalculatedField1_Sub.jiraissue_CREATED, string), '-2147483648')</value></property></node></property><property name="Select Columns"><value>jiraissue.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE</value><value>jiraissue.CREATED AS jiraissue_CREATED</value><value>CalculatedField1_Sub.CalculatedField1 AS CalculatedField1</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>
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
[View Less]
9 years, 11 months
[JBoss JIRA] (TEIID-3289) Join subquery containing ifnull in join condition
by Mark Tawk (JIRA)
[ https://issues.jboss.org/browse/TEIID-3289?page=com.atlassian.jira.plugin... ]
Mark Tawk commented on TEIID-3289:
----------------------------------
Teiid version is 8.9.0Alpha2
Mysql 5.7
Translator : mysql5
I have removed TimeStampDiff and put only the concerned field into the subquery.
Now the subquery is returning values.
But the jdbc queries executed by Teiid are totally different, we can see that the join condition with ifnull is written into the jdbc query generated by Teiid.
So …
[View More]for the first case containing TimeStampDiff, I morelikely suspect the ifnull function that links the main query to the subquery, I don't know if it is well applied on Teiid level for the subquery. Because from the jdbc queries, the ifnull is sent only for the main query.
If i remove the ifnull function from the join condition, mysql is giving null to all subquery value, same as teiid. But this is not the comportment that i need.
Here is the JDBC queries and execution plan when TimeStampDiff is removed:
SELECT DISTINCT g_0.`RESOLUTIONDATE` AS c_0 FROM `tjiradb`.`jiraissue` AS g_0 ORDER BY c_0 LIMIT 10
SELECT DISTINCT g_0.`RESOLUTIONDATE` AS c_0, g_0.`CREATED` AS c_1, v_0.c_0 AS c_2
FROM `tjiradb`.`jiraissue` AS g_0
LEFT OUTER JOIN (SELECT DISTINCT g_1.`CREATED` AS c_0, g_1.`RESOLUTIONDATE` AS c_1, g_1.`CREATED` AS c_2, g_1.`CREATED` AS c_3
FROM `tjiradb`.`jiraissue` AS g_1
WHERE g_1.`RESOLUTIONDATE` IS NOT NULL AND g_1.`RESOLUTIONDATE` IS NULL) AS v_0
ON ifnull(date_format(g_0.`RESOLUTIONDATE`, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648') = ifnull(date_format(v_0.c_1, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648') AND ifnull(date_format(g_0.`CREATED`, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648') = ifnull(date_format(v_0.c_2, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648')
WHERE g_0.`RESOLUTIONDATE` IS NOT NULL LIMIT 10
<?xml version='1.0' encoding='UTF-8'?><node name="AccessNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (timestamp)</value></property><property name="Statistics"><value>Node Output Rows: 10</value><value>Node Next Batch Process Time: 4</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 4</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: 10.0</value></property><property name="Query"><value>SELECT DISTINCT g_0.RESOLUTIONDATE AS c_0, g_0.CREATED AS c_1, v_0.c_0 AS c_2 FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_0 LEFT OUTER JOIN (SELECT DISTINCT g_1.CREATED AS c_0, g_1.RESOLUTIONDATE AS c_1, g_1.CREATED AS c_2, g_1.CREATED AS c_3 FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_1 WHERE (g_1.RESOLUTIONDATE IS NOT NULL) AND (g_1.RESOLUTIONDATE IS NULL)) AS v_0 ON IFNULL(convert(g_0.RESOLUTIONDATE, string), '-2147483648') = IFNULL(convert(v_0.c_1, string), '-2147483648') AND IFNULL(convert(g_0.CREATED, string), '-2147483648') = IFNULL(convert(v_0.c_2, string), '-2147483648') WHERE g_0.RESOLUTIONDATE IS NOT NULL LIMIT 10</value></property><property name="Model Name"><value>tjiradb_D1_VDB_DSModel</value></property></node>
> Join subquery containing ifnull in join condition
> -------------------------------------------------
>
> Key: TEIID-3289
> URL: https://issues.jboss.org/browse/TEIID-3289
> Project: Teiid
> Issue Type: Feature Request
> Reporter: Mark Tawk
> Assignee: Steven Hawkins
> Priority: Critical
>
> I have join subquery linked to main query via 2 fields in the query below.
> To be able to join null values between main query and subquery, I have added ifnull to join fields with a random value.
> If I run my query directly over mysql, the field CalculatedField1 return values whenever the field RESOLUTIONDATE is null in the main query.
> But when executed in Teiid, the query returns always null for the field CalculatedField1.
> When looking at the jdbc queries executed by Teiid, I found that ifnull is used for the main query fields but not for the subquery fields, which may be causing a link problem between main query and subquery.
> I have tried to put the ifnull into the subquery but still getting jdbc subquery fields without the ifnull function.
> SELECT DISTINCT
> jiraissue.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE,
> jiraissue.CREATED AS jiraissue_CREATED,
> CalculatedField1_Sub.CalculatedField1 AS CalculatedField1
> FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue jiraissue
> LEFT JOIN
> (SELECT DISTINCT
> TIMESTAMPDIFF(
> SQL_TSI_MINUTE,
> jiraissue_sub.CREATED,
> PARSETIMESTAMP('2014-10-03 00:00:00',
> 'yyyy-MM-dd HH:mm:ss'))
> AS CalculatedField1,
> jiraissue_sub.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE,
> jiraissue_sub.CREATED AS jiraissue_CREATED
> FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue jiraissue_sub
> WHERE ((((jiraissue_sub.RESOLUTIONDATE IS NULL)))))
> CalculatedField1_Sub
> ON (IFNULL(jiraissue.RESOLUTIONDATE, -2147483648) =
> IFNULL(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE,
> -2147483648))
> AND (IFNULL(jiraissue.CREATED, -2147483648) =
> IFNULL(CalculatedField1_Sub.jiraissue_CREATED,
> -2147483648))
> Teiid JDBC queries:
> SELECT DISTINCT g_0.`RESOLUTIONDATE` AS c_0, g_0.`CREATED` AS c_1, ifnull(date_format(g_0.`RESOLUTIONDATE`, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648') AS c_2, ifnull(date_format(g_0.`CREATED`, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648') AS c_3 FROM `tjiradb`.`jiraissue` AS g_0 ORDER BY c_2, c_3
> SELECT g_0.`CREATED`, g_0.`RESOLUTIONDATE` FROM `tjiradb`.`jiraissue` AS g_0 WHERE g_0.`RESOLUTIONDATE` IS NULL
> Teiid Execution plan
>
> <?xml version='1.0' encoding='UTF-8'?><node name="LimitNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 10</value><value>Node Next Batch Process Time: 7</value><value>Node Cumulative Next Batch Process Time: 43</value><value>Node Cumulative Process Time: 49</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 3</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>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 10</value><value>Node Next Batch Process Time: 13</value><value>Node Cumulative Next Batch Process Time: 42</value><value>Node Cumulative Process Time: 48</value><value>Node Next Batch Calls: 4</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="ProjectNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 35</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 4</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>RESOLUTIONDATE (timestamp)</value><value>CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 35</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 4</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>RESOLUTIONDATE (timestamp)</value><value>CREATED (timestamp)</value><value>expr (string)</value><value>expr (string)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 10</value><value>Node Blocks: 6</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT DISTINCT g_0.RESOLUTIONDATE AS c_0, g_0.CREATED AS c_1, IFNULL(convert(g_0.RESOLUTIONDATE, string), '-2147483648') AS c_2, IFNULL(convert(g_0.CREATED, string), '-2147483648') AS c_3 FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_0 ORDER BY c_2, c_3</value></property><property name="Model Name"><value>tjiradb_D1_VDB_DSModel</value></property></node></property><property name="Child 1"><node name="ProjectNode"><property name="Output Columns"><value>CalculatedField1 (long)</value><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField11 (long)</value><value>expr (string)</value><value>expr (string)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 22</value><value>Node Cumulative Next Batch Process Time: 18</value><value>Node Cumulative Process Time: 25</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 1</value></property><property name="Child 0"><node name="ProjectNode"><property name="Output Columns"><value>CalculatedField1 (long)</value><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField11 (long)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 9</value><value>Node Cumulative Next Batch Process Time: 3</value><value>Node Cumulative Process Time: 10</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>CREATED (timestamp)</value><value>RESOLUTIONDATE (timestamp)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 8</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 8</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.CREATED, g_0.RESOLUTIONDATE FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_0 WHERE g_0.RESOLUTIONDATE IS NULL</value></property><property name="Model Name"><value>tjiradb_D1_VDB_DSModel</value></property></node></property><property name="Select Columns"><value>TIMESTAMPDIFF(SQL_TSI_MINUTE, jiraissue_sub.CREATED, {ts'2014-10-03 00:00:00.0'}) AS CalculatedField1</value><value>jiraissue_sub.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE</value><value>jiraissue_sub.CREATED AS jiraissue_CREATED</value><value>TIMESTAMPDIFF(SQL_TSI_MINUTE, jiraissue_sub.CREATED, {ts'2014-10-03 00:00:00.0'}) AS CalculatedField11</value></property></node></property><property name="Select Columns"><value>CalculatedField1_Sub.CalculatedField1</value><value>CalculatedField1_Sub.jiraissue_RESOLUTIONDATE</value><value>CalculatedField1_Sub.jiraissue_CREATED</value><value>CalculatedField1_Sub.CalculatedField11</value><value>IFNULL(convert(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE, string), '-2147483648')</value><value>IFNULL(convert(CalculatedField1_Sub.jiraissue_CREATED, string), '-2147483648')</value></property></node></property><property name="Join Strategy"><value>MERGE JOIN (ALREADY_SORTED/SORT_DISTINCT)</value></property><property name="Join Type"><value>LEFT OUTER JOIN</value></property><property name="Join Criteria"><value>IFNULL(convert(jiraissue.RESOLUTIONDATE, string), '-2147483648')=IFNULL(convert(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE, string), '-2147483648')</value><value>IFNULL(convert(jiraissue.CREATED, string), '-2147483648')=IFNULL(convert(CalculatedField1_Sub.jiraissue_CREATED, string), '-2147483648')</value></property></node></property><property name="Select Columns"><value>jiraissue.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE</value><value>jiraissue.CREATED AS jiraissue_CREATED</value><value>CalculatedField1_Sub.CalculatedField1 AS CalculatedField1</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>
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
[View Less]
9 years, 11 months
[JBoss JIRA] (TEIID-3289) Join subquery containing ifnull in join condition
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3289?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3289:
---------------------------------------
> When looking at the jdbc queries executed by Teiid, I found that ifnull is used for the main query fields but not for the subquery fields, which may be causing a link problem between main query and subquery.
The plan appears valid. The TIMESTAMPDIFF is not being pushed, which limits the pushdown of the other expressions. Is …
[View More]it possible that you are expecting different behavior from the TIMESTAMPDIFF function? What version of Teiid, MySQL, and the mysql translator are you using?
> Join subquery containing ifnull in join condition
> -------------------------------------------------
>
> Key: TEIID-3289
> URL: https://issues.jboss.org/browse/TEIID-3289
> Project: Teiid
> Issue Type: Feature Request
> Reporter: Mark Tawk
> Assignee: Steven Hawkins
> Priority: Critical
>
> I have join subquery linked to main query via 2 fields in the query below.
> To be able to join null values between main query and subquery, I have added ifnull to join fields with a random value.
> If I run my query directly over mysql, the field CalculatedField1 return values whenever the field RESOLUTIONDATE is null in the main query.
> But when executed in Teiid, the query returns always null for the field CalculatedField1.
> When looking at the jdbc queries executed by Teiid, I found that ifnull is used for the main query fields but not for the subquery fields, which may be causing a link problem between main query and subquery.
> I have tried to put the ifnull into the subquery but still getting jdbc subquery fields without the ifnull function.
> SELECT DISTINCT
> jiraissue.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE,
> jiraissue.CREATED AS jiraissue_CREATED,
> CalculatedField1_Sub.CalculatedField1 AS CalculatedField1
> FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue jiraissue
> LEFT JOIN
> (SELECT DISTINCT
> TIMESTAMPDIFF(
> SQL_TSI_MINUTE,
> jiraissue_sub.CREATED,
> PARSETIMESTAMP('2014-10-03 00:00:00',
> 'yyyy-MM-dd HH:mm:ss'))
> AS CalculatedField1,
> jiraissue_sub.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE,
> jiraissue_sub.CREATED AS jiraissue_CREATED
> FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue jiraissue_sub
> WHERE ((((jiraissue_sub.RESOLUTIONDATE IS NULL)))))
> CalculatedField1_Sub
> ON (IFNULL(jiraissue.RESOLUTIONDATE, -2147483648) =
> IFNULL(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE,
> -2147483648))
> AND (IFNULL(jiraissue.CREATED, -2147483648) =
> IFNULL(CalculatedField1_Sub.jiraissue_CREATED,
> -2147483648))
> Teiid JDBC queries:
> SELECT DISTINCT g_0.`RESOLUTIONDATE` AS c_0, g_0.`CREATED` AS c_1, ifnull(date_format(g_0.`RESOLUTIONDATE`, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648') AS c_2, ifnull(date_format(g_0.`CREATED`, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648') AS c_3 FROM `tjiradb`.`jiraissue` AS g_0 ORDER BY c_2, c_3
> SELECT g_0.`CREATED`, g_0.`RESOLUTIONDATE` FROM `tjiradb`.`jiraissue` AS g_0 WHERE g_0.`RESOLUTIONDATE` IS NULL
> Teiid Execution plan
>
> <?xml version='1.0' encoding='UTF-8'?><node name="LimitNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 10</value><value>Node Next Batch Process Time: 7</value><value>Node Cumulative Next Batch Process Time: 43</value><value>Node Cumulative Process Time: 49</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 3</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>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 10</value><value>Node Next Batch Process Time: 13</value><value>Node Cumulative Next Batch Process Time: 42</value><value>Node Cumulative Process Time: 48</value><value>Node Next Batch Calls: 4</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="ProjectNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 35</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 4</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>RESOLUTIONDATE (timestamp)</value><value>CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 35</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 4</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>RESOLUTIONDATE (timestamp)</value><value>CREATED (timestamp)</value><value>expr (string)</value><value>expr (string)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 10</value><value>Node Blocks: 6</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT DISTINCT g_0.RESOLUTIONDATE AS c_0, g_0.CREATED AS c_1, IFNULL(convert(g_0.RESOLUTIONDATE, string), '-2147483648') AS c_2, IFNULL(convert(g_0.CREATED, string), '-2147483648') AS c_3 FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_0 ORDER BY c_2, c_3</value></property><property name="Model Name"><value>tjiradb_D1_VDB_DSModel</value></property></node></property><property name="Child 1"><node name="ProjectNode"><property name="Output Columns"><value>CalculatedField1 (long)</value><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField11 (long)</value><value>expr (string)</value><value>expr (string)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 22</value><value>Node Cumulative Next Batch Process Time: 18</value><value>Node Cumulative Process Time: 25</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 1</value></property><property name="Child 0"><node name="ProjectNode"><property name="Output Columns"><value>CalculatedField1 (long)</value><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField11 (long)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 9</value><value>Node Cumulative Next Batch Process Time: 3</value><value>Node Cumulative Process Time: 10</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>CREATED (timestamp)</value><value>RESOLUTIONDATE (timestamp)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 8</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 8</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.CREATED, g_0.RESOLUTIONDATE FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_0 WHERE g_0.RESOLUTIONDATE IS NULL</value></property><property name="Model Name"><value>tjiradb_D1_VDB_DSModel</value></property></node></property><property name="Select Columns"><value>TIMESTAMPDIFF(SQL_TSI_MINUTE, jiraissue_sub.CREATED, {ts'2014-10-03 00:00:00.0'}) AS CalculatedField1</value><value>jiraissue_sub.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE</value><value>jiraissue_sub.CREATED AS jiraissue_CREATED</value><value>TIMESTAMPDIFF(SQL_TSI_MINUTE, jiraissue_sub.CREATED, {ts'2014-10-03 00:00:00.0'}) AS CalculatedField11</value></property></node></property><property name="Select Columns"><value>CalculatedField1_Sub.CalculatedField1</value><value>CalculatedField1_Sub.jiraissue_RESOLUTIONDATE</value><value>CalculatedField1_Sub.jiraissue_CREATED</value><value>CalculatedField1_Sub.CalculatedField11</value><value>IFNULL(convert(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE, string), '-2147483648')</value><value>IFNULL(convert(CalculatedField1_Sub.jiraissue_CREATED, string), '-2147483648')</value></property></node></property><property name="Join Strategy"><value>MERGE JOIN (ALREADY_SORTED/SORT_DISTINCT)</value></property><property name="Join Type"><value>LEFT OUTER JOIN</value></property><property name="Join Criteria"><value>IFNULL(convert(jiraissue.RESOLUTIONDATE, string), '-2147483648')=IFNULL(convert(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE, string), '-2147483648')</value><value>IFNULL(convert(jiraissue.CREATED, string), '-2147483648')=IFNULL(convert(CalculatedField1_Sub.jiraissue_CREATED, string), '-2147483648')</value></property></node></property><property name="Select Columns"><value>jiraissue.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE</value><value>jiraissue.CREATED AS jiraissue_CREATED</value><value>CalculatedField1_Sub.CalculatedField1 AS CalculatedField1</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>
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
[View Less]
9 years, 11 months