[teiid-issues] [JBoss JIRA] (TEIID-3568) Order By and Limit are not getting pushed to the database, when Union and join are used together.

[Guru Prasad (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-3568?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13090055#comment-13090055 ] 

Guru Prasad commented on TEIID-3568:
------------------------------------

Noticed that the limit and order by are not being pushed if the translator does not have support to either InlineViews or Unions.

We are using a custom DB2 translator build on db2 translator, but here this does not support "union" and "in criteria".

We have another translator for datacom, which does not have support for InlineViews, so even here the limit and order by are not being pushed down. 

Would it be possible to push order by and limit to the queries, even when the translator does not have support to either InlineViews or Unions.
Also the queries execute well on these translators if union or join is used individually. 

> Order By and Limit are not getting pushed to the database, when Union and join are used together. 
> --------------------------------------------------------------------------------------------------
>
>                 Key: TEIID-3568
>                 URL: https://issues.jboss.org/browse/TEIID-3568
>             Project: Teiid
>          Issue Type: Bug
>    Affects Versions: 8.1
>            Reporter: Guru Prasad
>            Assignee: Steven Hawkins
>         Attachments: ShowPlan.txt
>
>
> Order By and Limit are not getting pushed to the database, when Union and join are used together. 
> In this scenario there if the underlying table has millions of records the query never returns with data.
> *Query 1*: Using only Join without union, this works fine.
> SELECT u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode FROM ( 	
> 	select evttypecode, evtsysid, evtutctod, evtsystod, evtcatcode from XYZ.Tab1 
> ) as u 
> LEFT OUTER JOIN XYZ.CATEGORY AS ct ON u.evtcatcode = ct.evtcatcode 
> WHERE (u.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (u.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'}) ORDER BY u.evtsysid LIMIT 8
> PROCESSOR PLAN:
> AccessNode(0) output=[evttypecode AS evttypecode, evtsysid AS evtsysid, evtutctod AS evtutctod, evtsystod AS evtsystod, evtcatcode AS evtcatcode] 
> SELECT g_0.EVTTYPECODE AS c_0, g_0.EVTSYSID AS c_1, g_0.EVTUTCTOD AS c_2, g_0.EVTSYSTOD AS c_3, g_0.EVTCATCODE AS c_4 FROM ABC.Tab1 AS g_0 LEFT OUTER JOIN ABC.CATEGORY AS g_1 ON g_0.EVTCATCODE = g_1.EVTCATCODE WHERE (g_0.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (g_0.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'}) ORDER BY c_1 LIMIT 8
> *Query 2*: Using only Union without any join, this also works fine.
> SELECT u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode FROM ( 	
> 	select evttypecode, evtsysid, evtutctod, evtsystod, evtcatcode from XYZ.Tab1 
> 	UNION ALL 
> 	select evttypecode, evtsysid, evtutctod, evtsystod, evtcatcode from XYZ.Tab2
> ) as u 
> WHERE (u.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (u.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'}) ORDER BY u.evtsysid LIMIT 8
> PROCESSOR PLAN:
> AccessNode(0) output=[evttypecode AS evttypecode, evtsysid AS evtsysid, evtutctod AS evtutctod, evtsystod AS evtsystod, evtcatcode AS evtcatcode] 
> SELECT g_1.EVTTYPECODE AS c_0, g_1.EVTSYSID AS c_1, g_1.EVTUTCTOD AS c_2, g_1.EVTSYSTOD AS c_3, g_1.EVTCATCODE AS c_4 FROM ABC.Tab1 AS g_1 WHERE (g_1.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (g_1.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'}) UNION ALL 
> SELECT g_0.EVTTYPECODE AS c_0, g_0.EVTSYSID AS c_1, g_0.EVTUTCTOD AS c_2, g_0.EVTSYSTOD AS c_3, g_0.EVTCATCODE AS c_4 FROM ABC.Tab2 AS g_0 WHERE (g_0.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (g_0.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'}) ORDER BY c_1 LIMIT 8
> *Query 3*: Using both Union and join, this does not push down the order by and limit.
> SELECT u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode FROM ( 	
> 	select evttypecode, evtsysid, evtutctod, evtsystod, evtcatcode from XYZ.Tab1 
> 	UNION ALL 
> 	select evttypecode, evtsysid, evtutctod, evtsystod, evtcatcode from XYZ.Tab2
> ) as u 
> LEFT OUTER JOIN XYZ.EVTTYPE AS tp ON tp.evttypecode = u.evttypecode 
> LEFT OUTER JOIN XYZ.CATEGORY AS ct ON u.evtcatcode = ct.evtcatcode 
> WHERE (u.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (u.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'}) ORDER BY u.evtsysid LIMIT 8
> PROCESSOR PLAN:
> ProjectNode(0) output=[u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode] [u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode]
>   LimitNode(1) output=[u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode] limit 8
>     SortNode(2) output=[u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode] [SORT] [u.evtsysid]
>       JoinNode(3) [MERGE JOIN (SORT/ALREADY_SORTED)] [LEFT OUTER JOIN] criteria=[u.evtcatcode=evtcatcode] output=[u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode]
>         JoinNode(4) [MERGE JOIN (SORT/ALREADY_SORTED)] [LEFT OUTER JOIN] criteria=[u.evttypecode=evttypecode] output=[u.evtcatcode, u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod]
>           AccessNode(5) output=[u.evttypecode, u.evtcatcode, u.evtsysid, u.evtutctod, u.evtsystod] 
> 					SELECT g_1.EVTTYPECODE AS c_0, g_1.EVTCATCODE AS c_1, g_1.EVTSYSID AS c_2, g_1.EVTUTCTOD AS c_3, g_1.EVTSYSTOD AS c_4 FROM ABC.Tab1 AS g_1 WHERE (g_1.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (g_1.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'}) 
> 		  UNION ALL SELECT g_0.EVTTYPECODE AS c_0, g_0.EVTCATCODE AS c_1, g_0.EVTSYSID AS c_2, g_0.EVTUTCTOD AS c_3, g_0.EVTSYSTOD AS c_4 FROM ABC.Tab2 AS g_0 WHERE (g_0.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (g_0.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'})
>           AccessNode(6) output=[evttypecode] SELECT g_0.EVTTYPECODE AS c_0 FROM ABC.EVTTYPE AS g_0 ORDER BY c_0
>         AccessNode(7) output=[evtcatcode] SELECT g_0.EVTCATCODE AS c_0 FROM ABC.CATEGORY AS g_0 ORDER BY c_0



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)