[JBoss JIRA] (TEIID-3568) Order By and Limit are not getting pushed to the database, when Union and join are used together.
7:42 a.m.
[
https://issues.jboss.org/browse/TEIID-3568?page=com.atlassian.jira.plugin...
]
Guru Prasad commented on TEIID-3568:
------------------------------------
In this condition if the joins are left outer then as per my understanding we would not
ending with fewer rows if the query supports ordered limit.
*For translator not supporting in line views*
1. Currently we are executing the right query independent of the left query
2. Executing the left query with the filter condition, but without order by and limit
(Single query is executed as this supports union)
3. Merge the results of left and right query based on the join criteria
4. Sort the results based on the sort column
5. Apply limit
If we apply the order by and limit in step 2, it will discarded the same rows which step 5
is performing. Am I missing something?
*For translator not supporting union*
1. Currently we are executing the right query independent of the left query
2. Executing the left query with the filter condition, but without order by and limit for
each table/view separately
3. Union of the results from step 2
4. Merge the results of step 3 and step 1 based on the join criteria
5. Sort the results based on the sort column
6. Apply limit
If we have applied the order by and limit in step 2 individually for both the queries and
then performed the rest of the steps we would be getting the same rows, these steps are
being followed if the query has no joins but has only union command.
Below is the plan if the query has union without the join
LimitNode(0) output=[convert(evttypecode, integer), evtsysid AS evtsysid, evtutctod AS
evtutctod, evtsystod AS evtsystod, convert(evtcatcode, integer)] limit 8
SortNode(1) output=[convert(evttypecode, integer), evtsysid AS evtsysid, evtutctod AS
evtutctod, evtsystod AS evtsystod, convert(evtcatcode, integer)] [SORT] [evtsysid]
UnionAllNode(2) output=[convert(evttypecode, integer), evtsysid AS evtsysid, evtutctod
AS evtutctod, evtsystod AS evtsystod, convert(evtcatcode, integer)]
ProjectNode(3) output=[convert(evttypecode, integer), evtsysid AS evtsysid,
evtutctod AS evtutctod, evtsystod AS evtsystod, convert(evtcatcode, integer)]
[convert(evttypecode, integer), evtsysid AS evtsysid, evtutctod AS evtutctod, evtsystod AS
evtsystod, convert(evtcatcode, integer)]
AccessNode(4) output=[evttypecode, evtsysid, evtutctod, evtsystod, 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 DB2DS.VIEWOBJ1 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
ProjectNode(5) output=[convert(evttypecode, integer), evtsysid AS evtsysid,
evtutctod AS evtutctod, evtsystod AS evtsystod, convert(evtcatcode, integer)]
[convert(evttypecode, integer), evtsysid AS evtsysid, evtutctod AS evtutctod, evtsystod AS
evtsystod, convert(evtcatcode, integer)]
AccessNode(6) output=[evttypecode, evtsysid, evtutctod, evtsystod, 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 DB2DS.VIEWADM1 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
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)
3462
days inactive
3462
days old
0 comments
1 participants
participants (1)
-
Guru Prasad (JIRA)