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

[Steven Hawkins (JIRA)]

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

Steven Hawkins closed TEIID-3568.
---------------------------------


> 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: Enhancement
>          Components: Query Engine
>    Affects Versions: 8.1
>            Reporter: Guru Prasad
>            Assignee: Steven Hawkins
>             Fix For: 8.12
>
>         Attachments: Jars in build path.txt, PushOrderByLimitTest1.java, 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.4.11#64026)