[jira] [Commented] (TRAFODION-2159) Unnest correlated subquery with explicit groupby

["ASF GitHub Bot (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TRAFODION-2159?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15543247#comment-15543247 ] 

ASF GitHub Bot commented on TRAFODION-2159:
-------------------------------------------

GitHub user sureshsubbiah opened a pull request:

    https://github.com/apache/incubator-trafodion/pull/739

    [TRAFODION-2159] Outputs not promoted when MapValueId node is present

    

You can merge this pull request into a Git repository by running:

    $ git pull https://github.com/sureshsubbiah/incubator-trafodion unnestfix1

Alternatively you can review and apply these changes as the patch at:

    https://github.com/apache/incubator-trafodion/pull/739.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 #739
    
----
commit 22cd2cbf373f5a2622378f5871cd28ecf64f89e3
Author: Suresh Subbiah <su...@apache.org>
Date:   2016-10-03T19:48:12Z

    [TRAFODION-2159] Promoting outputs of child with MapValueId node
    
    Promoting outputs of child was not working correctly when the child is a
    MapValueId node. Since the promoted outputs are not part of the map, MVI
    could not produce the promoted outputs though its child was willing to.
    Also fixed another bug recently introduced in the moveUpGrbyTransformation,
    when there is a subquery groupby in the correlated subquery.

----


> 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)