Suresh Subbiah created TRAFODION-2159:
-----------------------------------------
Summary: 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
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)
