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)