[
https://issues.apache.org/jira/browse/CALCITE-4738?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17399292#comment-17399292
]
duan xiong commented on CALCITE-4738:
-------------------------------------
[~wojustme] Yes. This is a bug. The generated SQL includes nested aggregate
function calls. So, please update your ISSUE summary.
And in my test. I write a UT that can generate the right SQL:
{code:java}
@Test void testSuccessiveAggregateProjectAggregate() {
final Function<RelBuilder, RelNode> relFn = b -> b
.scan("EMP")
.aggregate(b.groupKey(b.field("DEPTNO")),
b.count(true, "CNT_D", b.field("MGR")))
.project(ImmutableList.of(b.field(0),b.field(1)))
.aggregate(b.groupKey(), b.sum(false, "COL_SUM", b.field(1)))
.build();
final String expected = "SELECT SUM(\"CNT_D\") AS \"COL_SUM\"\n"
+ "FROM (SELECT \"DEPTNO\", COUNT(DISTINCT \"MGR\") AS \"CNT_D\"\n"
+ "FROM \"scott\".\"EMP\"\n"
+ "GROUP BY \"DEPTNO\") AS \"t\"";
relFn(relFn).ok(expected);
}{code}
Expect your contribution!
> Pattern of RelNode: `Aggregate-Project-Aggregate` executing RelToSqlConverter
> error.
> ------------------------------------------------------------------------------------
>
> Key: CALCITE-4738
> URL: https://issues.apache.org/jira/browse/CALCITE-4738
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Xurenhe
> Priority: Critical
>
> Here, some bug maybe exist in `RelToSqlConverter`, when handling for
> `Aggregate-Project-Aggregate`.
> Pattern1: Aggregate-Project-Aggregate(failed)
> we find returned sql cannot be analyzed.
> {code:java}
> @Test void testSuccessiveAggregateProjectAggregate() {
> final Function<RelBuilder, RelNode> relFn = b -> b
> .scan("EMP")
> .aggregate(b.groupKey(b.field("DEPTNO")),
> b.count(true, "CNT_D", b.field("MGR")))
> .project(ImmutableList.of(b.field(1)), ImmutableList.of("CNT_D"))
> .aggregate(b.groupKey(), b.sum(false, "COL_SUM", b.field(0)))
> .build();
> final String expected = "SELECT SUM(\"CNT_D\") AS \"COL_SUM\"\n"
> + "FROM (SELECT \"DEPTNO\", COUNT(DISTINCT \"MGR\") AS \"CNT_D\"\n"
> + "FROM \"scott\".\"EMP\"\n"
> + "GROUP BY \"DEPTNO\") AS \"t\"";
> relFn(relFn).ok(expected);
> }
> {code}
> {code:sql}
> -- return sql, after executing rel_to_sql
> SELECT SUM(COUNT(DISTINCT "MGR")) AS "COL_SUM"
> FROM "scott"."EMP"
> GROUP BY "DEPTNO"
> {code}
> ----------------------------------------------
> Pattern2: Aggregate-Aggregate(ok)
> {code:java}
> @Test void testSuccessiveAggregateAggregate() {
> final Function<RelBuilder, RelNode> relFn = b -> b
> .scan("EMP")
> .aggregate(b.groupKey(b.field("DEPTNO")),
> b.count(true, "CNT_D", b.field("MGR")))
> .aggregate(b.groupKey(), b.sum(false, "COL_SUM", b.field(1)))
> .build();
> final String expected = "SELECT SUM(\"CNT_D\") AS \"COL_SUM\"\n"
> + "FROM (SELECT \"DEPTNO\", COUNT(DISTINCT \"MGR\") AS \"CNT_D\"\n"
> + "FROM \"scott\".\"EMP\"\n"
> + "GROUP BY \"DEPTNO\") AS \"t1\"";
> relFn(relFn).ok(expected);
> }
> {code}
> {code:sql}
> -- return sql, after executing rel_to_sql
> SELECT SUM("CNT_D") AS "COL_SUM"
> FROM (SELECT "DEPTNO", COUNT(DISTINCT "MGR") AS "CNT_D"
> FROM "scott"."EMP"
> GROUP BY "DEPTNO") AS "t1"
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
