[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 resolved TEIID-3285.
-----------------------------------
    Resolution: Rejected


That is expected.  From the docs:

"When a join clause is omitted via the optional join hint, the relevant criteria is not applied. Thus it is possible that the query results may not have the same cardinality or even the same row values as when the join is fully applied."

Only if you project a value from the optional join will we preserve it when there is a hint.

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