[
https://issues.apache.org/jira/browse/TRAFODION-2159?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15543446#comment-15543446
]
ASF GitHub Bot commented on TRAFODION-2159:
-------------------------------------------
Github user zellerh commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/739#discussion_r81634915
--- Diff: core/sql/optimizer/NormRelExpr.cpp ---
@@ -3958,9 +3963,12 @@ GroupByAgg* Join::moveUpGroupByTransformation(const
GroupByAgg* topGrby,
GroupByAgg *moveUpGrby;
ValueIdSet emptySet ;
GroupByAgg *movedUpGrbyTail = (GroupByAgg*) topGrby;
+ GroupByAgg *subQGrby = NULL;
--- End diff --
Comment on existing line 3965: This is bad, we take a const GroupByAgg * as
an argument, then we cast away that const-ness and modify the child of the node
later. The topGrby argument cannot be a const.
> 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 JIRA is also used to track a change that will cause two subqueries that
> require LOJ transformation to be unnested, as long as they are not nested. An
> example query is
> -- 2 correlated subqueries that require LOJ transformation. The two subqueries
> -- are NOT nested. Use of exists (i.e. oneTrue) predicate
> -- Q52
> prepare XX from
> select t40.a
> from t40
> where exists (select *
> from t44
> where t40.c = t44.b)
> OR
> exists (select *
> from t41
> where t40.b = t41.b)
> order by 1;
> Please see compGeneral/TEST011 for plan output.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
