Github user sureshsubbiah commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/624#discussion_r72824380
--- 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
--- End diff --
Lack of outer references in {aggr} and {pred3} may indicate that this
transformation may not apply when we more than one 1 level of correlated
subqueries. This set of conditions clearly explains when this transformation
can be applied. I hope we can add the change needed for this soon. The
description here also indicates that NULL preserving semantics is not affected
by this new transformation. So we could use this change for OR subqueries where
the Join could be a Left Join.
---
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.
---
