[
https://issues.apache.org/jira/browse/TRAFODION-2159?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15419109#comment-15419109
]
Suresh Subbiah commented on TRAFODION-2159:
-------------------------------------------
The transformation will be done in the PullUpGroupByTransformation method,
which is part of the SemanticQueryOptimization (SQO) phase.
During this phase we currently pull up a scalar aggregate (Group by with no
grouping cols) over TSJ, absorb Filter predicate into newJoin, and convert TSJ
into newJoin. With this change we will in addition pullup the explicit subquery
groupBy too (along with the scalar aggregate). So two groupbys which where on
the right side of TSJ will now will move to be parent of a join. They will keep
their relative order. Key of left side of join will be ADDED to grouping column
of both GBs. Its a simple transformation, pullup the explicit groupby in the
exact same way as the scalar aggregate groupby, with a similar change to its
grouping cols (i.e. addition of left side key)
A small change is needed in createFilterChildIfNecessary method, since we want
to place the Filter node below the explicit groupby node. Current code expects
Filter to be right grandchild of TSJ, but now it could be right grandchild or
right grand-grand child.
If the subquery requires a moveUpgroupby transformation (used when there are
two or more levels of nested subqueries) then this change is disabled. Moveup
groupby transformation does something similar, but here the second groupby
comes from a different subquery. This restriction can be relaxed later.
This change will also not kick in if the subquery does not contain a null
rejecting predicate (i.e. contains an OR predicate for example). In this case
we need a Left join to unnest, which require a MapValueId node near the
groupby. Since the interaction between MapvalueId and this new groupby has not
been fully explored, this is restricted for 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)
