[teiid-issues] [JBoss JIRA] (TEIID-5682) Raise aggregation to allow for join pushdown

[Steven Hawkins (Jira)]

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

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)