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

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-3285?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins closed TEIID-3285.
---------------------------------


> 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)