]
Steven Hawkins updated TEIID-3276:
----------------------------------
Fix Version/s: 8.10
Data result not correct when joinning subquery over a date field
----------------------------------------------------------------
Key: TEIID-3276
URL:
https://issues.jboss.org/browse/TEIID-3276
Project: Teiid
Issue Type: Bug
Components: JDBC Connector
Affects Versions: 7.7
Reporter: Mark Tawk
Assignee: Steven Hawkins
Priority: Critical
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