[jira] [Commented] (CALCITE-7275) DATEDIFF should be able to handle NULL values

[Ulrich Kramer (Jira)]

    [ 
https://issues.apache.org/jira/browse/CALCITE-7275?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18050352#comment-18050352
 ] 

Ulrich Kramer commented on CALCITE-7275:
----------------------------------------

We implemented a custom {{DATEDIFF}} function because it was not working if the 
function is evaluated in Java.
After digging deeper into the problem, it turned out that the following is 
missing:

In {{StandardConvertletTable}}

{code:java}
  registerOp(SqlLibraryOperators.DATEDIFF,
            new TimestampDiffConvertlet());
{code}

and in {{RexImpTable}}

{code:java}
defineEquiv(DATEDIFF,TIMESTAMP_DIFF)
{code}

> 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)