[jira] [Created] (CALCITE-7134) Incorrect type inference for some aggregate functions when groupSets contains '{}'

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

Silun Dong created CALCITE-7134:
-----------------------------------

             Summary: Incorrect type inference for some aggregate functions when groupSets contains '{}'
                 Key: CALCITE-7134
                 URL: https://issues.apache.org/jira/browse/CALCITE-7134
             Project: Calcite
          Issue Type: Bug
          Components: core
    Affects Versions: 1.40.0
            Reporter: Silun Dong
         Attachments: image-2025-08-12-20-09-50-273.png

Taking SUM as an example, the strategy for SUM type inference is (in ReturnTypes.java):
{code:java}
public static final SqlReturnTypeInference AGG_SUM = opBinding -> {
  final RelDataTypeFactory typeFactory = opBinding.getTypeFactory();
  final RelDataType type = typeFactory.getTypeSystem()
      .deriveSumType(typeFactory, opBinding.getOperandType(0));
  if (opBinding.getGroupCount() == 0 || opBinding.hasFilter()) {
    return typeFactory.createTypeWithNullability(type, true);
  } else {
    return type;
  }
}; {code}
As mentioned in [CALCITE-845|https://issues.apache.org/jira/browse/CALCITE-845]:
??e.g. that 'select sum( x ) from t group by g' should be not null and 'select sum( x ) from t' should be nullable??
The current implementation will check {{{}groupCount{}}}. If {{groupCount}} is 0, it is considered that there is no group. Similarly, when {{{}groupSets=[\{0, 1}, \{0}, {}]{}}}, that is, {{groupSets}} contains empty group, sum should also be nullable, but now it will be inferred as not nullable. 
The following figure shows the test results on pglite:
!image-2025-08-12-20-09-50-273.png!
the test results on Calcite:
{code:java}
!use scott
!set outputformat mysql

select sum(empno) from emp where 1=2;
+--------+
| EXPR$0 |
+--------+
|        |
+--------+
(1 row)

!ok

select sum(empno) from emp where 1=2 group by deptno, job;
+--------+
| EXPR$0 |
+--------+
+--------+
(0 rows)

!ok

select sum(empno) from emp where 1=2 group by rollup(deptno, job);
+--------+
| EXPR$0 |
+--------+
+--------+
(0 rows)

!ok {code}
Is this a bug? If it is a bug, then in addition to SUM, there may also be AVG.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)