[
https://issues.apache.org/jira/browse/TRAFODION-2159?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Suresh Subbiah updated TRAFODION-2159:
--------------------------------------
Description:
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.
was:
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) ;
> 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)
