[IGNITE-15418] Calcite engine. TIMESTAMPDIFF function returns INTEGER type for MICROSECOND time unit

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Labels: calcite2-required calcite3-required
• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Unassigned
• Reporter: Aleksey Plekhanov
• Created: 01/Sep/21 10:20
• Updated: 05/Apr/23 07:19

Description

SQL function TIMESTAMPDIFF returns INTEGER type for MICROSECOND time unit, but sometimes precession of int is not enough to store value. For example, value for

SELECT TIMESTAMPDIFF(MICROSECOND, TIMESTAMP '2022-02-01 10:30:26.000', TIMESTAMP '2022-05-01 10:30:28.122')

is 7689602122000, and it's more then maximum int value. It's better to extend TIMESTAMPDIFF return type to BIGINT for MICROSECOND time unit (SqlTimestampDiffFunction.RETURN_TYPE_INFERENCE should be changed). 

The main problem - this function is defined in Calcite and hardcoded in the parser (see TimestampDiffFunctionCall in Parser.jj), so it's not so easy to provide our own implementation.

Issue Links

• is part of: IGNITE-19162 Sql. Trim all less than millisecond information from timestamp.
• is part of: IGNITE-14535 Caclite SQL engine capabilities