[
https://issues.apache.org/jira/browse/CALCITE-7679?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18100280#comment-18100280
]
DongShengHe edited comment on CALCITE-7679 at 7/30/26 3:43 AM:
---------------------------------------------------------------
Based on the discussion in the
[PR|https://github.com/apache/calcite/pull/5130#discussion_r3671282579]， I
proposed a general solution to fix this issue.
When determining whether a subquery is needed for an aggregate, we should check
whether the current dialect supports grouping by literals. If it does not and
the grouping keys contain a literal, we need to wrap the input in a subquery to
prevent the literal from appearing directly in the {{GROUP BY}} clause.
For this plan:
{code:java}
LogicalAggregate(group=[{0, 1}])
LogicalProject(id=[$1], employee_id=[$0])
LogicalProject(employee_id=[$0], id=[null:NULL])
JdbcTableScan(table=[[foodmart, employee]]) {code}
will translated to :
{code:java}
SELECT employee_id, id
FROM (SELECT employee_id, null as id from foodmart.employee) as t
group by t.employee_id, t.id {code}
Another point that needs to be discussed is whether we should retain the
{{AggregateProjectConstantToDummyJoinRule}} introduced by CALCITE-4702.
If we remove `{{{}AggregateProjectConstantToDummyJoinRule`, for this plan:{}}}
{code:java}
LogicalProject(EXPR$0=[$1])
LogicalAggregate(group=[{0}], EXPR$0=[AVG($1)])
LogicalProject($f0=[true], salary=[$11])
JdbcTableScan(table=[[foodmart, employee]]) {code}
{{will be translated to:}}
{code:java}
SELECT AVG(salary)
FROM
(SELECT TRUE AS $f0, salary FROM foodmart.employee) AS t
GROUP BY $f0{code}
{{which is equivalent to}}
{code:java}
SELECT AVG(employee.salary)
FROM foodmart.employee,(SELECT TRUE AS $f0) AS t
GROUP BY t.$f0 {code}
[~mbudiu] [~jensen] Could you please help review this?
was (Author: JIRAUSER307201):
Based on the discussion in the
[PR|https://github.com/apache/calcite/pull/5130#discussion_r3671282579]， I
proposed a general solution to fix this issue.
When determining whether a subquery is needed for an aggregate, we should check
whether the current dialect supports grouping by literals. If it does not and
the grouping keys contain a literal, we need to wrap the input in a subquery to
prevent the literal from appearing directly in the {{GROUP BY}} clause.
For this plan:
{code:java}
LogicalAggregate(group=[{0, 1}])
LogicalProject(id=[$1], employee_id=[$0])
LogicalProject(employee_id=[$0], id=[null:NULL])
JdbcTableScan(table=[[foodmart, employee]]) {code}
will translated to :
{code:java}
SELECT employee_id, id
FROM (SELECT employee_id, null as id from foodmart.employee) as t
group by t.employee_id, t.id {code}
Another point that needs to be discussed is whether we should retain the
{{AggregateProjectConstantToDummyJoinRule}} introduced by CALCITE-4702.
If we remove `{{{}AggregateProjectConstantToDummyJoinRule`, for this plan:{}}}
{code:java}
LogicalProject(EXPR$0=[$1])
LogicalAggregate(group=[{0}], EXPR$0=[AVG($1)])
LogicalProject($f0=[true], salary=[$11])
JdbcTableScan(table=[[foodmart, employee]]) {code}
{{will be translated to:}}
{code:java}
SELECT AVG(salary)
FROM
(SELECT TRUE AS $f0, salary FROM foodmart.employee) AS t
GROUP BY $f0{code}
{{which is equivalent to}}
{code:java}
SELECT AVG(employee.salary)
FROM foodmart.employee,(SELECT TRUE AS $f0) AS t
GROUP BY t.$f0 {code}
[~mbudiu] [~jensen]
> RelToSqlConverter generates GROUP BY literals for dialects that do not
> support them when the constant is hidden by nested Projects
> ----------------------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7679
> URL: https://issues.apache.org/jira/browse/CALCITE-7679
> Project: Calcite
> Issue Type: Bug
> Reporter: DongShengHe
> Priority: Minor
> Labels: pull-request-available
>
> h3. Description
> {{RelToSqlConverter}} may generate a literal in the {{GROUP BY}} clause for
> dialects that do not support them when the grouping expression is hidden
> behind multiple {{Project}} nodes.
> This is related to CALCITE-4702. Its fix uses
> {{AggregateProjectConstantToDummyJoinRule}} for dialects that do not support
> grouping by literals. However, the rule only examines the {{Project}}
> directly below the {{{}Aggregate{}}}.
>
> For example, consider the following relational plan:
> {code:java}
> LogicalAggregate(group=[{0}])
> LogicalProject(ID=[$0])
> LogicalProject(ID=[null:NULL])
> LogicalTableScan(table=[[hr, emps]]) {code}
>
> The direct project expression is {{{}$0{}}}, so
> {{AggregateProjectConstantToDummyJoinRule}} does not recognize it as a
> literal.
> As a result, the PostgreSQL dialect generates:
>
> {code:java}
> SELECT NULL AS "ID"
> FROM "hr"."emps"
> GROUP BY NULL {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
