[
https://issues.apache.org/jira/browse/TRAFODION-2130?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15399662#comment-15399662
]
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_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.
> 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)
