[
https://issues.apache.org/jira/browse/CALCITE-7134?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18013473#comment-18013473
]
Julian Hyde commented on CALCITE-7134:
--------------------------------------
And my comments to the effect of “you can probably use groupCount” (and
therefore don’t need the new allowChangeNullable field) were probably correct.
> 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
> Assignee: 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)
