[
https://issues.apache.org/jira/browse/CALCITE-3466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16967016#comment-16967016
]
Julian Hyde commented on CALCITE-3466:
--------------------------------------
I agree that this is a bug. Dropping the Aggregate changes the semantics,
because it changes the COUNT used by the AVG.
Do you know which component is dropping the Aggregate? I'd be surprised if it
is the JDBC adapter. Is it perhaps RelBuilder, or a planner rule?
> JDBC adapter dropped group by statement in subquery
> ---------------------------------------------------
>
> Key: CALCITE-3466
> URL: https://issues.apache.org/jira/browse/CALCITE-3466
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.21.0, 1.22.0
> Reporter: Wang Weidong
> Priority: Major
> Fix For: 1.22.0
>
> Attachments: screenshot-1.png
>
>
> While convert RelNode to SqlNode, the "group by" statement in subquery was
> dropped unexpectedly. For example,
> original sql is:
> {code:sql}
> select a, avg(c1) from
> (
> select a,sum(d),b as c1 from t group by b,a
> ) as t
> group by a
> {code}
> RelNode converted by SqlNode is
> {code:sql}
> LogicalAggregate(group=[{0}], EXPR$1=[AVG($1)])
> LogicalProject(A=[$1], C1=[$0])
> LogicalAggregate(group=[{0, 1}], EXPR$1=[SUM($2)])
> LogicalProject(B=[$1], A=[$0], D=[$3])
> EnumerableTableScan(table=[[T_TESTPROJECTMERGE]])
> {code}
> And then convert this RelNode to SqlNode, the sql of SqlNode is like this:
> {code:sql}
> SELECT `A`, AVG(`B`)
> FROM `T`
> GROUP BY `A`
> {code}
> We can find that "group by" in subquery was dropped which may cause SqlNode
> converted by RelNode being not equivalent to RelNode.
> After debugging, I've found code
> at
> https://github.com/apache/calcite/blob/aa98c1575762718b238af8ddd44a390607de7602/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java#L333
>
> calls org.apache.calcite.rel.rel2sql.SqlImplementor.Builder#setGroupBy
> without judge whether "group by" already exists.
> Finally, I think in this case, the main select and sub-select shouldn't be
> merged.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
