[
https://issues.apache.org/jira/browse/TRAFODION-2159?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15419806#comment-15419806
]
ASF GitHub Bot commented on TRAFODION-2159:
-------------------------------------------
GitHub user sureshsubbiah opened a pull request:
https://github.com/apache/incubator-trafodion/pull/657
[TRAFODION-2159] Unnest correlated subquery with explicit groupby
Add support for unnesting subqueries with a groupby in addition to the
scalar aggregate.
Only 1 level of subquery is current supported.
Also unnesting that requires left join is excluded from this change.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/sureshsubbiah/incubator-trafodion unnest1
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/incubator-trafodion/pull/657.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #657
----
commit 24e252c1c00c9a4d26d0b233d8047c25b089c845
Author: Suresh Subbiah <[email protected]>
Date: 2016-08-05T21:50:58Z
Intial change for unnesting
commit f0cae7f83395f1f16c702efda4f1dbc427bebefa
Author: Suresh Subbiah <[email protected]>
Date: 2016-08-13T04:53:48Z
Subquery unnest for subquery with explicit groupby
----
> 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)
