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.
