[
https://issues.apache.org/jira/browse/TRAFODION-2159?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15425461#comment-15425461
]
ASF GitHub Bot commented on TRAFODION-2159:
-------------------------------------------
Github user sureshsubbiah commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/657#discussion_r75215054
--- Diff: core/sql/optimizer/NormRelExpr.cpp ---
@@ -2436,15 +2449,21 @@ void Join::createAFilterGrandChildIfNeeded(NormWA &
normWARef)
if ((doNotUnnest == FALSE) && candidateForLeftJoin &&
(CmpCommon::getDefault(SUBQUERY_UNNESTING_P2) != DF_INTERNAL) &&
- (normWARef.getLeftJoinConversionCount() >= 1))
+ ((normWARef.getLeftJoinConversionCount() >= 1)||nestedAggInSubQ))
{
doNotUnnest = TRUE;
// For phase 2 we only unnest 1 level of subqueries
// containing NonNullRejecting Predicates
- if (CmpCommon::getDefault(SUBQUERY_UNNESTING) == DF_DEBUG)
+ if (CmpCommon::getDefault(SUBQUERY_UNNESTING) == DF_DEBUG)
+ {
+ if (!nestedAggInSubQ)
*CmpCommon::diags() << DgSqlCode(2997)
<< DgString1("Skipping unnesting of Subquery due to
NonNullRejecting Predicates in more than one subquery");
+ else
+ *CmpCommon::diags() << DgSqlCode(2997)
+ << DgString1("Skipping unnesting of Subquery since we have
both NonNullRejecting predicate and nested nested aggregate in subquery.");
--- End diff --
Thanks for catching the typo, it is fixed now.
> Unnest correlated subquery with explicit groupby
> ------------------------------------------------
>
> Key: TRAFODION-2159
> URL: https://issues.apache.org/jira/browse/TRAFODION-2159
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 2.0-incubating
> Reporter: Suresh Subbiah
> Assignee: Suresh Subbiah
> Fix For: 2.1-incubating
>
> Attachments: GroupByUnnest.pdf
>
>
> Correlated subqueries with the pattern shown on the left side of the
> attachment are currently not unnested. When cqd subquery_unnesting is set to
> 'DEBUG' this warning message is seen
> *** WARNING[2997] (Subquery was not unnested. Reason: Right grandchild of
> TSJ is a semijoin or a group by)
> An example query is
> prepare XX from
> select t40.a
> from t40
> where t40.b >= (select avg(counta) from (select count(a) as counta from t44
> where t40.c = t44.b group by t44.c)) ;
>
> Here the "group by t44.c" in the subquery prevents it from being unnested,
> while "t40.c = t44.b" is the correlation predicate.
> With the transformation shown in the right side of the attachment this query
> will be unnested to this equivalent form, which has no explicit correlation
> select a0 from
> (select a0, avg(counta4) avga4 from
> (select a0, b0, count(a4) counta4 from
> (select t40.a a0, t40.b b0, t44.a a4, t44.c c4 from
> t40,t44
> where t40.c = t44.b )T1(a0,b0,a4,c4)
> group by (a0,b0,c4)) T2(a0,b0,counta4)
> group by (a0,b0)
> having b0 >= avga4) T3(a0, avga4) ;
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
