[
https://issues.jboss.org/browse/TEIID-5682?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-5682:
---------------------------------------
This seems a little involved for the problem that it is solving. With an incoming query
such as:
SELECT g0.e1, g0.e2, g0.e3, /*+ MJ */ (SELECT ARRAY_AGG((g1.e1, g1.e2) ORDER BY g1.e1)
FROM PM1.G2 AS g1 WHERE g0.e2 = g1.e2) FROM PM1.G1 AS g0 ORDER BY g0.e2
SELECT g0.e1, g0.e2, g0.e3, X__1.expr1 AS expr4
FROM PM1.G1 AS g0 LEFT OUTER JOIN
(SELECT ARRAY_AGG((g1.e1, g1.e2) ORDER BY g1.e1) AS expr1, g1.e2 FROM PM1.G2 AS g1 GROUP
BY g1.e2) AS X__1 ON g0.e2 = X__1.e2
ORDER BY g0.e2
We need to get to:
SELECT g0.e1, g0.e2, g0.e3, ARRAY_AGG((X__1.e1, X__1.e2) ORDER BY X__1.e1) ) FILTER (WHERE
X__1.e1 IS NOT NULL) AS expr4
FROM PM1.G1 AS g0 LEFT OUTER JOIN
(SELECT g1.e1, g1.e2 FROM PM1.G2 AS g1) AS X__1 ON g0.e2 = X__1.e2
GROUP BY g0.e1, g0.e2, g0.e3 ORDER BY g0.e2
Note that all projected g0 columns must be sortable - this is not always the case. It
also requires attaching a filter to aggregates that can be null dependent when there is an
outer join. And finally it can't have any intervening usage of aggregate values or
even complex projection between the logical subquery grouping root and it join parent -
this will generally be the case for expand, but can't generally be assumed.
A simpler approach would either be to either already aggregate the other top level
columns: select g0.e2, array_agg(g0.e1 ...)
Or not use the nested aggregation and process the expand like the older style - but that
only makes sense for a single expand.
Raise aggregation to allow for join pushdown
--------------------------------------------
Key: TEIID-5682
URL:
https://issues.jboss.org/browse/TEIID-5682
Project: Teiid
Issue Type: Enhancement
Components: Query Engine
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Priority: Major
Fix For: 12.2
In some plans, for example those created by odata expand see TEIID-5680, the resulting
query has the form of:
select tbl1-cols from tbl1 left outer join (select cols, aggregates ... from tbl2 group
by cols) on (some non-aggregate predicate)
If the aggregates or other intervening constructs are not able to be pushed the result
will be a federated join.
In most instances (where the tbl1 columns are sortable) it's possible to change this
to:
select tbl1-cols aggregates from tbl1 left outer join tbl1 on (...) group by tbl1-cols,
cols
which can allow the pushdown of the join to proceed.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)