I suspect MIN and MAX do not need to derive a different type; what else can the result be if not the input type? If this is the only thing you cannot do, overriding the type system is the right approach.
If there are aggregate functions that are not handled by the type system, we could consider this a bug. You could file an issue to move their type inference in the type system. Hopefully this can be done without breaking existing users. Using your own functions could work, but many optimizations only work for the standard aggregates. Hopefully these optimizations do not also make assumptions about the return types... Mihai ________________________________ From: Lino Rosa via dev <[email protected]> Sent: Thursday, September 17, 2026 8:21 AM To: [email protected] <[email protected]> Cc: Lino Rosa <[email protected]> Subject: [Question] Overriding the return type of a built-in SqlFunction on SqlStdOperatorTable For lots of built-in SQL functions, the return type doesn't match the equivalent return type in every dialect. For example, in Calcite SUM may return a NOT NULL value, whereas on Spark a SUM is always nullable. There are many other cases, such as AVG, MIN, and MAX. I'd like to override the `returnTypeInferrence` of these build-in functions in order to match the dialect's type signature, but I'm struggling finding the right approach to do so with Calcite. I've considered several avenues to solve this, but I'd appreciate some guidance before I forge ahead. I may be missing or overstating some of the risks and I might have failed to see better options. (Option 1) Override RelDataTypeSystem.deriveSumType / deriveAvgAggType This is tempting, but it doesn't cover all cases. For example, MIN/MAX don't have a corresponding `derive...` method and I don't think I should expect Calcite to expose a `derive` method for every built-in function. (Option 2) Shadow the built-in function with our own wrapper implementation We could have wrappers (e.g. final class NullableSum extends SqlSumAggFunction) and ensure our `SqlOperatorTable` loads these instead of the default ones. I'm leaning more toward this direction, but two things give me pause: 1. Sometimes Calcite instantiates the built-in functions directly without going through the SqlOperatorTable. This would instantiate the default ones, not our wrappers. 2. Sometimes Calcite performs by-reference equality checks (e.g., `aggOp == SqlStdOperatorTable.SUM`). In cases where we replace a built-in function with a wrapper, these checks would stop matching. ( Option 3) Classpath shadowing I don't really want to go there, but one possible direction here would be to have our own `org.apache.sql.fun.*` package and make sure it's loaded first, which would shadow Calcite's built-in implementation. I didn't go too far considering this avenue, but it feels like a possibility.
