7:03 a.m.
[
https://issues.jboss.org/browse/TEIID-3276?page=com.atlassian.jira.plugin...
]
Steven Hawkins updated TEIID-3276:
----------------------------------
Affects Version/s: 7.7
Component/s: JDBC Connector
Looking at this some more I believe the issue is that the week function is being
calculated at both the source and in Teiid.
Teiid effectively has two options for the week calculation:
java default based upon locale (default)
iso8601 - https://docs.jboss.org/author/display/TEIID/System+Properties
MySQL has 8 modes:
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#funct...
And their default does not correspond to either of Teiid's week calculations.
With Teiid in iso 8601 mode and the mysql translator updated to call week with mode 3, the
weeks will match.
Also we should probably make the iso week handling the default for Teiid to make things
more consistent.
Another general fix is with how we are handling the week function subexpresion in this
situation. Ideally we should not be calculating it in both Teiid and at the source, but
we aren't yet going symbolic replacement of subexpressions generally (we did add
partial projection pushing, but even that doesn't apply here with the join).
Data result not correct when joinning subquery over a date field
----------------------------------------------------------------
Key: TEIID-3276
URL: https://issues.jboss.org/browse/TEIID-3276
Project: Teiid
Issue Type: Bug
Components: JDBC Connector
Affects Versions: 7.7
Reporter: Mark Tawk
Assignee: Steven Hawkins
Priority: Critical
Hello,
I'm using Teiid 8.9.0 To execute the below query and the execution is done without
the problem; the issue is that I'm getting different results when executing the same
query over mysql
The original query is much bigger but i tried to simplify it to isolate the problem.
SELECT DISTINCT
WEEK(contactcalldetail.startdatetime) AS contactcalldetail_startdatet,
sessionid_Sub.contactcalldetail_sessionid
AS contactcalldetail_sessionid
FROM db_cra_8F_VDB_DSModel.db_cra.contactcalldetail contactcalldetail
LEFT JOIN /* optional */
(SELECT COUNT(DISTINCT contactcalldetail_sub.sessionid)
AS contactcalldetail_sessionid,
WEEK(contactcalldetail_sub.startdatetime)
AS contactcalldetail_startdatet
FROM db_cra_8F_VDB_DSModel.db_cra.contactcalldetail contactcalldetail_sub
WHERE (((contactcalldetail_sub.contacttype IN (1))))
AND ((((contactcalldetail_sub.connecttime >= 300))))
GROUP BY WEEK(contactcalldetail_sub.startdatetime)) sessionid_Sub
ON (WEEK(contactcalldetail.startdatetime) =
sessionid_Sub.contactcalldetail_startdatet)
WHERE (((contactcalldetail.contacttype IN (1))))
LIMIT 0, 10
Here is Teiid execution plan :
<?xml version='1.0' encoding='UTF-8'?><node
name="LimitNode"><property name="Output
Columns"><value>contactcalldetail_startdatet
(integer)</value><value>contactcalldetail_sessionid
(integer)</value></property><property
name="Statistics"><value>Node Output Rows:
1</value><value>Node Next Batch Process Time: 0</value><value>Node
Cumulative Next Batch Process Time: 12</value><value>Node Cumulative Process
Time: 0</value><value>Node Next Batch Calls: 3</value><value>Node
Blocks: 2</value></property><property name="Cost
Estimates"><value>Estimated Node Cardinality:
10.0</value></property><property name="Child 0"><node
name="SortNode"><property name="Output
Columns"><value>contactcalldetail_startdatet
(integer)</value><value>contactcalldetail_sessionid
(integer)</value></property><property
name="Statistics"><value>Node Output Rows:
1</value><value>Node Next Batch Process Time: 0</value><value>Node
Cumulative Next Batch Process Time: 12</value><value>Node Cumulative Process
Time: 0</value><value>Node Next Batch Calls: 3</value><value>Node
Blocks: 2</value></property><property name="Cost
Estimates"><value>Estimated Node Cardinality:
-1.0</value></property><property name="Child 0"><node
name="ProjectNode"><property name="Output
Columns"><value>contactcalldetail_startdatet
(integer)</value><value>contactcalldetail_sessionid
(integer)</value></property><property
name="Statistics"><value>Node Output Rows:
4096</value><value>Node Next Batch Process Time:
0</value><value>Node Cumulative Next Batch Process Time:
13</value><value>Node Cumulative Process Time:
0</value><value>Node Next Batch Calls: 5</value><value>Node
Blocks: 3</value></property><property name="Cost
Estimates"><value>Estimated Node Cardinality:
-1.0</value></property><property name="Child 0"><node
name="JoinNode"><property name="Output
Columns"><value>startdatetime
(timestamp)</value><value>contactcalldetail_sessionid
(integer)</value></property><property
name="Statistics"><value>Node Output Rows:
4096</value><value>Node Next Batch Process Time:
0</value><value>Node Cumulative Next Batch Process Time:
11</value><value>Node Cumulative Process Time:
0</value><value>Node Next Batch Calls: 5</value><value>Node
Blocks: 3</value></property><property name="Cost
Estimates"><value>Estimated Node Cardinality:
-1.0</value></property><property name="Child 0"><node
name="AccessNode"><property name="Output
Columns"><value>startdatetime (timestamp)</value><value>expr
(integer)</value></property><property
name="Statistics"><value>Node Output Rows:
4096</value><value>Node Next Batch Process Time:
0</value><value>Node Cumulative Next Batch Process Time:
0</value><value>Node Cumulative Process Time: 0</value><value>Node
Next Batch Calls: 7</value><value>Node Blocks:
5</value></property><property name="Cost
Estimates"><value>Estimated Node Cardinality:
-1.0</value></property><property
name="Query"><value>SELECT DISTINCT g_0.startdatetime AS c_0,
WEEK(g_0.startdatetime) AS c_1 FROM db_cra_8F_VDB_DSModel.db_cra.contactcalldetail AS g_0
WHERE g_0.contacttype = 1 ORDER BY c_1</value></property><property
name="Model
Name"><value>db_cra_8F_VDB_DSModel</value></property></node></property><property
name="Child 1"><node name="ProjectNode"><property
name="Output Columns"><value>contactcalldetail_startdatet
(integer)</value><value>contactcalldetail_sessionid
(integer)</value></property><property
name="Statistics"><value>Node Output Rows:
3</value><value>Node Next Batch Process Time: 7</value><value>Node
Cumulative Next Batch Process Time: 8</value><value>Node Cumulative Process
Time: 15</value><value>Node Next Batch Calls: 2</value><value>Node
Blocks: 1</value></property><property name="Cost
Estimates"><value>Estimated Node Cardinality:
-1.0</value></property><property name="Child 0"><node
name="GroupingNode"><property name="Output
Columns"><value>gcol0 (integer)</value><value>agg0
(integer)</value></property><property
name="Statistics"><value>Node Output Rows:
3</value><value>Node Next Batch Process Time:
14</value><value>Node Cumulative Next Batch Process Time:
8</value><value>Node Cumulative Process Time:
15</value><value>Node Next Batch Calls: 2</value><value>Node
Blocks: 1</value></property><property name="Cost
Estimates"><value>Estimated Node Cardinality:
-1.0</value></property><property name="Child 0"><node
name="AccessNode"><property name="Output
Columns"><value>startdatetime
(timestamp)</value><value>sessionid
(long)</value></property><property
name="Statistics"><value>Node Output Rows:
772</value><value>Node Next Batch Process Time:
9</value><value>Node Cumulative Next Batch Process Time:
1</value><value>Node Cumulative Process Time: 9</value><value>Node
Next Batch Calls: 2</value><value>Node Blocks:
1</value></property><property name="Cost
Estimates"><value>Estimated Node Cardinality:
-1.0</value></property><property
name="Query"><value>SELECT g_0.startdatetime, g_0.sessionid FROM
db_cra_8F_VDB_DSModel.db_cra.contactcalldetail AS g_0 WHERE (g_0.contacttype = 1) AND
(g_0.connecttime >= 300)</value></property><property name="Model
Name"><value>db_cra_8F_VDB_DSModel</value></property></node></property><property
name="Grouping
Columns"><value>WEEK(contactcalldetail_sub.startdatetime)</value></property><property
name="Sort
Mode"><value>false</value></property></node></property><property
name="Select Columns"><value>anon_grp0.gcol0 AS
contactcalldetail_startdatet</value><value>anon_grp0.agg0 AS
contactcalldetail_sessionid</value></property></node></property><property
name="Join Strategy"><value>MERGE JOIN
(ALREADY_SORTED/SORT)</value></property><property name="Join
Type"><value>LEFT OUTER JOIN</value></property><property
name="Join
Criteria"><value>WEEK(contactcalldetail.startdatetime)=sessionid_Sub.contactcalldetail_startdatet</value></property></node></property><property
name="Select Columns"><value>WEEK(contactcalldetail.startdatetime) AS
contactcalldetail_startdatet</value><value>sessionid_Sub.contactcalldetail_sessionid
AS
contactcalldetail_sessionid</value></property></node></property><property
name="Sort
Mode"><value>DUP_REMOVE</value></property></node></property><property
name="Row
Offset"><value>null</value></property><property
name="Row Limit"><value>10</value></property></node>
Here are the 2 JDBC queries executed by Teiid:
SELECT DISTINCT g_0.`startdatetime` AS c_0, week(g_0.`startdatetime`) AS c_1 FROM
`db_cra`.`contactcalldetail` AS g_0 WHERE g_0.`contacttype` = 1 ORDER BY c_1
SELECT g_0.`startdatetime`, g_0.`sessionid` FROM `db_cra`.`contactcalldetail` AS g_0
WHERE g_0.`contacttype` = 1 AND g_0.`connecttime` >= 300
Teiid query execution result is :
week countsession
36 null
37 328
38 384
mysql execution result:
week countsession
35 328
36 384
37 60
You can notice that the week is incremented by one and the count result is scrambled
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)