[
https://issues.apache.org/jira/browse/CALCITE-7134?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18013444#comment-18013444
]
Julian Hyde commented on CALCITE-7134:
--------------------------------------
If I read the results correctly, Calcite gives the same result as Postgres
except for the ROLLUP query. Calcite returns zero rows, Postgres returns one
row with null. Postgres is correct, so we have a bug.
[~jensen], I agree that this is connected to CALCITE-7126. In fact, I think
that this bug should be factored out and fixed first, because it occurs without
GROUP_ID.
The solution may be to set groupCount = 0 in all cases where the empty group
“()” is present, including “GROUPING SETS (x), (y), ()”; and “ROLLUP (x, y)”,
which becomes “GROUPING SETS (x, y), (x), ()”. Some functions - including SUM,
MIN, MAX, but not including COUNT and GROUP_ID - return null when applied to
zero rows, and therefore become nullable when one of the groups is “()”, or
when the “FILTER (WHERE)” clause is present.
> 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)
