[
https://issues.apache.org/jira/browse/TRAFODION-2159?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15425456#comment-15425456
]
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_r75214692
--- Diff: core/sql/optimizer/NormRelExpr.cpp ---
@@ -2404,8 +2412,13 @@ void Join::createAFilterGrandChildIfNeeded(NormWA &
normWARef)
if (doNotUnnest == FALSE)
{
nonLocalPreds.clear();
- if (oldRightGrandChild->selectionPred().getReferencedPredicates
- (outerReferences, nonLocalPreds))
+ oldRightGrandChild->selectionPred().getReferencedPredicates
+ (outerReferences, nonLocalPreds) ;
+ if (nestedAggInSubQ)
+ subQGby->selectionPred().getReferencedPredicates
+ (outerReferences, nonLocalPreds);
+
+ if (!nonLocalPreds.isEmpty())
--- End diff --
I think for a groupby by the time we get past the binder the having clause
is the same as the where clause. In other words the predicate is kept in
selectionPred() of RelExpr base class. It is applied at the end of the work
method before a result row is sent to parent. From a SQL syntax point of view
this works for HAVING, since now the selectionPred() can reference aggregates
evaluated in this GB.
> 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)
