[teiid-issues] [JBoss JIRA] (TEIID-3285) Optional Left Join deleted while it has a condition in the where clause

[Mark Tawk (JIRA)]

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)