[jira] [Comment Edited] (CALCITE-7679) RelToSqlConverter generates GROUP BY literals for dialects that do not support them when the constant is hidden by nested Projects

["DongShengHe (Jira)" <ji...@apache.org>]

    [ 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:34 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}
 


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}
{{}}

> 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)