[teiid-issues] [JBoss JIRA] (TEIID-3285) Optional Left Join deleted while it has a condition in the where clause
Mark Tawk created TEIID-3285:
--------------------------------
Summary: Optional Left Join deleted while it has a condition in the where
clause
Key: TEIID-3285
URL: https://issues.jboss.org/browse/TEIID-3285
Project: Teiid
Issue Type: Bug
Reporter: Mark Tawk
Assignee: Steven Hawkins
Priority: Critical
I'm using Teiid 8.9.0.Alpha2
I'm executing via Teiid the following query in which I have an optional left join with
a where condition related to it.
The JDBC query executed by Teiid is removing the optional join and the corresponding where
condition.
SELECT AVG(TicketViewRed.CalculatedField13) AS TicketViewRed_CalculatedFiel,
TicketViewRed.jiraissue_PROJECT AS TicketViewRed_jiraissue_PROJ11
FROM implifyBusinessModel.TicketViewRed TicketViewRed
LEFT JOIN /* optional */ implifyBusinessModel.DimDate DimDate ON
TicketViewRed.jiraissue_RESOLUTIONDATE = DimDate.DimDate_iTn_241214_Date
WHERE (((TicketViewRed.jiraissue_PROJECT IN (10700, 11300))))
AND ((DimDate.DimDate_iTn_241214_Date BETWEEN PARSETIMESTAMP('2014-01-01
00:00:00', 'yyyy-MM-dd HH:mm:ss')
AND PARSETIMESTAMP('2014-09-02
00:00:00', 'yyyy-MM-dd HH:mm:ss')))
GROUP BY TicketViewRed.jiraissue_PROJECT
ORDER BY TicketViewRed.jiraissue_PROJECT DESC
JDBC query executed by Teiid
SELECT AVG(g_0.`CalculatedField13`) AS c_0, g_0.`jiraissue_PROJECT` AS c_1
FROM `implify_view`.`TicketViewRed` AS g_0
WHERE g_0.`jiraissue_PROJECT` IN (10700, 11300)
GROUP BY g_0.`jiraissue_PROJECT` ORDER BY c_1 DESC
And here is the corresponding execution plan:
<?xml version='1.0' encoding='UTF-8'?><node
name="AccessNode"><property name="Output
Columns"><value>TicketViewRed_CalculatedFiel
(double)</value><value>TicketViewRed_jiraissue_PROJ11
(long)</value></property><property
name="Statistics"><value>Node Output Rows:
2</value><value>Node Next Batch Process Time: 9</value><value>Node
Cumulative Next Batch Process Time: 0</value><value>Node Cumulative Process
Time: 9</value><value>Node Next Batch Calls: 2</value><value>Node
Blocks: 1</value></property><property name="Cost
Estimates"><value>Estimated Node Cardinality:
-1.0</value></property><property
name="Query"><value>SELECT AVG(g_0.CalculatedField13) AS c_0,
g_0.jiraissue_PROJECT AS c_1 FROM implify_viewModel.implify_view.TicketViewRed AS g_0
WHERE g_0.jiraissue_PROJECT IN (10700, 11300) GROUP BY g_0.jiraissue_PROJECT ORDER BY c_1
DESC</value></property><property name="Model
Name"><value>implify_viewModel</value></property></node>
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)