[
https://issues.apache.org/jira/browse/TRAFODION-2130?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15399656#comment-15399656
]
ASF GitHub Bot commented on TRAFODION-2130:
-------------------------------------------
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.
> Incorrect subquery transformation for tables w/o key
> ----------------------------------------------------
>
> Key: TRAFODION-2130
> URL: https://issues.apache.org/jira/browse/TRAFODION-2130
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 1.2-incubating
> Reporter: Hans Zeller
> Assignee: Hans Zeller
>
> Some of the subquery unnesting transformations rely on a unique key on the
> result of the main query. It turns out, however, that the transformation
> happens even if there is no unique key. Example:
> select *
> from (values (1,1), (1,1), (2,2)) T(a,b)
> where t.a in (select max(a)
> from (values (1,1), (1,1), (3,3)) S(a,b)
> where S.b = T.b);
> This should return two rows, but it actually just returns one, because it
> does not include a unique key for the main query, T.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
