Oracle mode - trunc(date, datePart) does not work in 1.4.200

[Sveinung Rekaa]

It worked in 1.2.197.

Are there any plan to fix it?

I have tried to override with an ALIAS user function, but how to override an existing function?

Regards

Sveinung Rekaa

[Evgenij Ryazanov]

Hello.

I guess you meant 1.4.197.

This function with such parameters was never supported by H2. But H2 silently ignores the value of the second parameter if the first parameter is a DATE, TIMESTAMP, or TIMESTAMP WITH TIME ZONE.

The code for these data types is the same in both versions:

https://github.com/h2database/h2database/blob/version-1.4.197/h2/src/main/org/h2/expression/Function.java#L1182

https://github.com/h2database/h2database/blob/version-1.4.200/h2/src/main/org/h2/expression/function/Function.java#L1805

I think you need to provide a simple compete test case.

If you want to override a built-in function with own one, you need to add

;BUILTIN_ALIAS_OVERRIDE=TRUE

to the connection URL.

[Pavel Shaidrov]

Hi Evgenij,

Although the implementation is the same in both versions, the c4780480fd commit brought additional limitation on number of arguments if the first argument is date/time

In 1.4.200 this Oracle-specific usage of trunc(date, datePart) causes INVALID_PARAMETER_COUNT_2 exception that is not what was happening in previous versions

If I understand correctly "regular" functions are always checked before mode-specific (that is kind of strange) and this makes not possible to have trunc(...) be "overloaded" for Oracle mode:

https://github.com/h2database/h2database/blob/master/h2/src/main/org/h2/expression/function/Function.java#L315

Don't you think it would be more convenient to check mode-specific functions first and then look for a regular one? Or am I missing something here?

--

BR,

Pavel Shaidrov

[Evgenij Ryazanov]

Hello.

H2 never had real support of Oracle-style TRUNC function. H2 has only own function and its optional second argument is a number of digits.

Recent releases of H2 are more pedantic and they correctly reject the second argument when it isn't supported due to type of the first argument, I forget about this difference.

Old versions ignore this unexpected argument and may silently return an incorrect result. It's better to have an explicit error.

This function with datetime argument will be deprecated in the next version H2 due to its weird behavior and low usability, H2 has another function for that purpose with sane implementation.

Own functions are intentionally resolved first, but the whole Function class and its name resolution logic are going to be removed from H2. Many functions were already moved out into own classes.

H2 can have a specialized implementation of TRUNC function for Oracle mode or some conditional code in the current implementation for that mode, but I think we need to complete the separation of the Function class first.