Github user sureshsubbiah commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/624#discussion_r72823645
--- Diff: core/sql/optimizer/NormRelExpr.cpp ---
@@ -3062,6 +3071,35 @@ GroupByAgg*
Join::pullUpGroupByTransformation(NormWA& normWARef)
if (CmpCommon::getDefault(SUBQUERY_UNNESTING) == DF_DEBUG)
*CmpCommon::diags() << DgSqlCode(2997)
<< DgString1("Subquery was not unnested. Reason: Left child does not
have a unique constraint");
+
+ // Things to consider (referring to the picture above): If the all of
the
+ // following are true:
+ // * {pred2} has only equals/VEG predicates of the form X.col = Y.col
+ // * {aggr} does not have any outer references
+ // * {pred3} does not have any outer references
+ //
+ // then we could do an alternative transformation, not yet implemented:
+ //
+ // TSJ Join {pred2: X.a=Y.b,
...}
+ // / \ / \
+ // / \ / \
+ // X ScalarAgg {pred3} --> X grby {Y.b, ...}
{pred3}
+ // | {aggr} \ {aggr}
+ // | \
+ // Filter {pred2: X.a=Y.b, ...} Y {pred1}
+ // |
+ // |
+ // Y {pred1}
+ //
+ // Pros: - The groupby is already at a place where it will likely
+ // end up in the optimal plan
+ // Cons: - We don't get a nice join backbone with all base tables
+ //
+ // Cases where we could attempt this transformation:
+ // - We fail to find a unique key for X (i.e. we reach here)
+ // - pred2 has a very high selectivity, making newJoin (in the
picture
--- End diff --
If pred2 is any thing other than an equality predicate, or an equality
predicate involving complex expressions, then the join effectively becomes a
cartesian product. In such cases doing the groupby early will help reduce
dataflow.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at [email protected] or file a JIRA ticket
with INFRA.
---
