[
https://issues.apache.org/jira/browse/CALCITE-7275?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18050668#comment-18050668
]
Mihai Budiu commented on CALCITE-7275:
--------------------------------------
Let me tell you how we handle functions in our compiler, maybe you will find it
useful.
We do not want to support all the Calcite functions, and we want to provide
some additional functions that do not exist in the Calcite implementation, and
we want to support modified versions of functions that Calcite provides. We
also want users to be able to write user-defined functions, both in SQL and in
Rust, which are added dynamically, as the program is parsed.
So we build a custom SqlOperatorTable, in which we insert explicitly all the
functions that we want to provide (be they from Calcite or from our own list).
If users declare new user-defined functions, we add these to the operator table
as well; after that we create a new validator (also a new catalog reader, and a
new SqlToRelConverter), which use the new SqlOperatorTable (maybe creating new
objects is not necessary, but I couldn't figure out how to update an existing
validator).
Also, our operator table class derives from ListSqlOperatorTable because we
want to always do case-insensitive lookups for function names, irrespective of
how other objects are looked up.
If you think any of these tools will help, I can point you to the
implementations.
But essentially we found Calcite flexible enough to allow us to mix and match
existing pieces with new pieces to accomplish what we needed.
I think it should be possible to reuse the existing DATEDIFF function; in fact,
we do.
> DATEDIFF should be able to handle NULL values
> ---------------------------------------------
>
> Key: CALCITE-7275
> URL: https://issues.apache.org/jira/browse/CALCITE-7275
> Project: Calcite
> Issue Type: Bug
> Reporter: Ulrich Kramer
> Priority: Major
>
> Normally DATEDIFF handles NULL values by returning NULL.
> {noformat}
> SELECT DATEDIFF(day, NULL, '2024-01-15') -- Result: NULL
> SELECT DATEDIFF(day, '2024-01-01', NULL) -- Result: NULL
> SELECT DATEDIFF(day, NULL, NULL) -- Result: NULL
> {noformat}
> But the builtin Method {{SqlFunctions.customDateDiff}} has only parameters
> and a return code, which doesn't allow to handle this:
> {code}
> public static int customDateDiff(DataContext root,
> String timeFrameName, int date, int date2)
> {code}
> {code:SQL}
> SELECT DATEDIFF(DAY,NULL,NULL)
> {code}
> generates invalid code:
> {noformat}
> ...
> public Object current() {
> return
> Integer.valueOf(com.sap.sva.nucleus.resources.calcite.base.DateTimeUtil.dateDifference(org.apache.calcite.avatica.util.TimeUnitRange.DAY,
> null, null));
> }
> ...
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
