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

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>