revisit function registry #9641
Description
several draft PR has been created to unify the function registry (e.g. #9602 , #9623)
However there are multiple issues with the current way how Pinot interacts with Calcite's function / Sql Operator / function lookup
several overall considerations need attention on
- validation phase (SqlOpTable),
- logical plan phase (function and type system) and
- post logical plan phase (physical plan generation matches logical plan on type usage, mainly v1/v2 discrepancy)
Some items I can think
- ensure all directly supported SqlOps are not being rewrite: for example SqlCoalesceFunction from calcite rewrites coalesce into CASE WHEN, however Pinot directly support coalesce scalar function, so no need for the override
- ensure that all functions can be found on SqlOps with proper type system support, similar to how the function registry PRs intended to do but
- need extremely specific type conversion documentation
- need to be backward compatible with behavior with V1 - this can either be achieved with Pinot specific rewrite rules, (so that the converted version no longer requires special treatment on V1, or with special treatment on V1 vs v2, or with v1 rewrite on ServerRequestUtils)
- ensure essential behavior are the same on physical plan: for example,
- duplicate column references will be removed on server while generating datablocks, this is not conforming with the logical plan.
- case sensitivity and underscore splitter function naming is not being supported.
- connection between v1 and v2 engine
- currently v1 engine doesn't support strong-typed planning (as it works off SqlNode) in fact, v2 engine has to back up from RelNode to SqlNode inorder to make it compatible with V1.
- we need a strong-typed, PlanMaker to generate strong-type query operator chain
We also want to make this extensible to support UDAF and UDTF