[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:all-tabpanel ]

Steven Hawkins resolved TEIID-5682.
-----------------------------------
    Fix Version/s:     (was: 12.2)
       Resolution: Deferred


Adding this into the optimizer seems too special purpose.  A sketch of what this looks like in RuleRaiseAccess when you can't raise over a grouping:

{code}
//perform the pullup of aggregation if necessary
                PlanNode source = NodeEditor.findParent(accessNode, NodeConstants.Types.SOURCE, NodeConstants.Types.JOIN);
                if (source == null) {
                    return null;
                }
                
                //intervening nodes ...
                //project node must be simple 
                
                PlanNode join = source.getParent();
                if (join == null || join.getType() != NodeConstants.Types.JOIN) {
                    return null;
                }
                
                JoinType type = (JoinType) join.getProperty(NodeConstants.Info.JOIN_TYPE);
                //what about correlated references / lateral join
                boolean left = join.getFirstChild() == source;
                PlanNode other = left?join.getLastChild():join.getFirstChild();
                if (other.getType() != NodeConstants.Types.ACCESS) {
                    return null;
                }
                List<Criteria> criteria = (List<Criteria>) join.getProperty(NodeConstants.Info.JOIN_CRITERIA);
                if (canRaiseOverJoin(
                    Arrays.asList(left ? accessNode : other, left ? other : accessNode),
                    metadata, capFinder,
                    criteria,
                    type, record, context, false, false) == null) {
                    return null;
                }
                // on can't be on the aggregates
                for (Criteria crit : criteria) {
                    //look at the symbol map to find aggregates, check against the symbols here
                    //...
                }

                //the other select columns must be sortable
{code}

Altering the expand logic sql generation is possible - but adds a lot of additional logic and is only valid for a single expand.

So I'm marking as deferred.

Instead I've added a makeind hint to the outer side.  For sources that don't support array_agg this ensures a relatively bounded set is used to perform the join.  A potential refinement is to look for the inner cardinality and not use the hint if it is "small".

> 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
>
> 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)