[jira] [Created] (IGNITE-23745) Calcite engine. Add ability to extent SQL engine with new system functions/operators

["Aleksey Plekhanov (Jira)" <ji...@apache.org>]

Aleksey Plekhanov created IGNITE-23745:
------------------------------------------

             Summary: Calcite engine. Add ability to extent SQL engine with new system functions/operators
                 Key: IGNITE-23745
                 URL: https://issues.apache.org/jira/browse/IGNITE-23745
             Project: Ignite
          Issue Type: Improvement
            Reporter: Aleksey Plekhanov
            Assignee: Aleksey Plekhanov


Old H2-based SQL engine has a lot of functions and operators, which are currently not supported by Calcite-based SQL engine. Some of these functions/operators have another syntax and duplicating it just to provide compatibility is not a good idea. For example, there is a function {{SUBSTR(a, b, c)}}, supported by H2-engine and not supported by Calcite-engine. Instead Calcite-engine supports {{SUBSTRING}} function (also described by SQL standard). Adding {{SUBSTR}} to the Calcite-engine will lead to overloading functions list with duplicates. User defined functions can't fully cover all features that Calcite has to implement system functions. So, it's proposed to add an ability to implement new system functions by plugins or extensions.  
I see several points of extension on different stages of query execution:
* {{IgniteStdSqlOperatorTable}} - a table of operators which can be used by SQL engine. We already have public method {{register}} here.
* {{IgniteSqlCallRewriteTable}} - a table of rewriters for operators (before validation, SqlNode to SqlNode). We already have public methid {{register}} here too.
* IgniteConvertletTable - a table of convertlet (validated SqlNode to RexNode). Protected methods {{registerOp}} and {{addAlias}} can be converted to public.
* {{RexImpTable}} - a table of implementors (RexNode to Expression). Here we have some private methods (all they just register implementor in {{map}}), and a set of predefined implementors, some of them can be converted to public.
* {{Accumulators}} - a table of aggregate functions. Refactoring required here. Currently the list of aggregate functions is hardcoded with switch/case block. (Perhaps we can fix {{Accumulators}} by another ticket).
Also, it will be useful to somehow extend grammer, but I see no simple way how to do it right now.




--
This message was sent by Atlassian Jira
(v8.20.10#820010)