[
https://issues.apache.org/jira/browse/CALCITE-4743?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17400772#comment-17400772
]
Xurenhe edited comment on CALCITE-4743 at 8/18/21, 4:31 AM:
------------------------------------------------------------
[~julianhyde]
Thanks for your reply.
Please let met to elaborate on my scene.
{code:sql}
SELECT c1,
c2
FROM
(SELECT t1.c1 AS c1,
t2.c2 AS c2
FROM t1
INNER JOIN t2 ON t1.id = t2.id
AND t1.c1 = 'hello'
AND t2.c2 = 'world')
GROUP BY c1,
c2
--------------
LogicalAggregate(group=[{0, 1}])
LogicalProject(c1=[$0], c2=[$2])
LogicalJoin(condition=[AND(=($1, $3), =($0, 'hello'), =($2, 'world'))],
joinType=[inner])
LogicalTableScan(table=[[default, t1]])
LogicalTableScan(table=[[default, t2]])
{code}
After exec optimizer of pullup constants of aggregate, which we extend
`AggregateProjectPullUpConstantsRule` to support aggregate of none-groupby.
Result of this case, as below
{code:sql}
LogicalProject(c1=['hello'], c2=['world'])
LogicalAggregate(group=[{}])
LogicalProject(c1=[$0], c2=[$2])
LogicalJoin(condition=[AND(=($1, $3), =($0, 'hello'), =($2, 'world'))],
joinType=[inner])
LogicalTableScan(table=[[default, t1]])
LogicalTableScan(table=[[default, t2]])
{code}
After executing rel_to_sql.
{code:sql}
SELECT 'hello' AS `c1`, 'world' AS `c2`
FROM `default`.`t1`
INNER JOIN `default`.`t2` ON `t1`.`id` = `t2`.`id` AND (`t1`.`c1` = 'hello' AND
`t2`.`c2` = 'world')
GROUP BY ()
{code}
----------------
So. In the process of rel_to_sql, aggregate may be a empty, which has
none-groupby and none-aggcall, which entry into code of
`org.apache.calcite.rel.rel2sql.RelToSqlConverter#buildAggregate`.
Need it be enhanced here, in order to covert this case?
Could rel_to_sql work right, no matter what optimizer do?
I hope hear your reply again.
Thanks a lot.
was (Author: wojustme):
[~julianhyde]
Thanks for your reply.
Please let met to elaborate on my scene.
{code:sql}
SELECT c1,
c2
FROM
(SELECT t1.c1 AS c1,
t2.c2 AS c2
FROM t1
INNER JOIN t2 ON t1.id = t2.id
AND t1.c1 = 'hello'
AND t2.c2 = 'world')
GROUP BY c1,
c2
--------------
LogicalAggregate(group=[{0, 1}])
LogicalProject(c1=[$0], c2=[$2])
LogicalJoin(condition=[AND(=($1, $3), =($0, 'hello'), =($2, 'world'))],
joinType=[inner])
LogicalTableScan(table=[[default, t1]])
LogicalTableScan(table=[[default, t2]])
{code}
After exec optimizer of pullup constants of aggregate, which we extend
`AggregateProjectPullUpConstantsRule` to support aggregate of none-groupby.
Result of this case, as below
{code:sql}
LogicalProject(c1=['hello'], c2=['world'])
LogicalAggregate(group=[{}])
LogicalProject(c1=[$0], c2=[$2])
LogicalJoin(condition=[AND(=($1, $3), =($0, 'hello'), =($2, 'world'))],
joinType=[inner])
LogicalTableScan(table=[[default, t1]])
LogicalTableScan(table=[[default, t2]])
{code}
After executing rel_to_sql.
{code:sql}
SELECT 'hello' AS `c1`, 'world' AS `c2`
FROM `default`.`t1`
INNER JOIN `default`.`t2` ON `t1`.`id` = `t2`.`id` AND (`t1`.`c1` = 'hello' AND
`t2`.`c2` = 'world')
GROUP BY ()
{code}
----------------
So. In the process of rel_to_sql, aggregate may be a empty, which has
none-groupby and none-aggcall, which entry into code of
`org.apache.calcite.rel.rel2sql.RelToSqlConverter#buildAggregate`.
Need it be enhanced here, in order to covert this case?
Could rel_to_sql work right, no matter what optimizer do.
I hope hear your reply again.
Thanks a lot.
> Convert rel to sql fail, when meeting aggregate of no-group and no-aggcall.
> ---------------------------------------------------------------------------
>
> Key: CALCITE-4743
> URL: https://issues.apache.org/jira/browse/CALCITE-4743
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Xurenhe
> Priority: Major
> Attachments: image-2021-08-17-20-27-45-801.png
>
>
> Hi, all.
> Here, Aggregate of of no-group and no-agg-call coverting to sql is fail.
> For example:
> {code:java}
> @Test void testAggregateWithNoGroupAndNoAggCall() {
> final RelBuilder builder = relBuilder();
> final RelNode relRoot = builder.scan("EMP")
> .aggregate(builder.groupKey())
> .project(ImmutableList.of(builder.literal("hello")),
> ImmutableList.of("constant_field"))
> .build();
> String expected = "SELECT 'hello' AS \"constant_field\"\n"
> + "FROM \"scott\".\"EMP\"";
> relFn(b -> relRoot).ok(expected);
> }
> {code}
> This case will return sql:
> {code:sql}
> SELECT 'hello' AS "constant_field"
> FROM "scott"."EMP"
> GROUP BY ()
> {code}
> --------------------------------------
> Should we use a switch of dialect to control this conversion, which is code
> of `org.apache.calcite.rel.rel2sql.RelToSqlConverter#buildAggregate`?
> !image-2021-08-17-20-27-45-801.png|thumbnail!
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
