[
https://issues.apache.org/jira/browse/CALCITE-7134?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Silun Dong updated CALCITE-7134:
--------------------------------
Description:
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:
??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 comparison may clearly illustrate this problem.
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.
was:
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:
??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.
> 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
> Priority: Major
> 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:
> ??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 comparison may clearly illustrate this problem.
> 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)
