1:17 a.m.
[
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 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)
3644
days inactive
3644
days old
0 comments
1 participants
participants (1)
-
Mark Tawk (JIRA)