Zhenghua Gao created CALCITE-3529:
-------------------------------------
Summary: TIMESTAMPDIFF function may overflow when handle unit of
MICROSECOND/NANOSECOND
Key: CALCITE-3529
URL: https://issues.apache.org/jira/browse/CALCITE-3529
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.21.0
Reporter: Zhenghua Gao
For unit of NANOSECOND, the TIMESTAMPDIFF function returns a BIGINT, which may
overflow since BIGINT can't cover the huge range of nanosecond.
For unit of MICROSECOND, the TIMESTAMPDIFF function returns a INTEGER, which
may overflow too.
TIMESTAMPDIFF(MICROSECOND, TIMESTAMP '9999-12-31 23:59:59.999', TIMESTAMP
'0001-01-01 00:00:00.000') should returns '315537897599999000' [1], calcite
returns '879764032'.
TIMESTAMPDIFF(NANOSECOND, TIMESTAMP '9999-12-31 23:59:59.999', TIMESTAMP
'0001-01-01 00:00:00.000') should returns '315537897599999000*000*' which is
bigger than LONG.MAX_VALUE, calcite returns '-1943248345937622528'.
My suggestion is:
# Change the return type to BIGINT for unit of MICROSECOND
# Disable support for unit of NANOSECOND or give a RISK message in the
document of TIMESTAMPDIFF
[1] the value is calculated by MySQL 5.6 from [http://sqlfiddle.com/]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
