You are viewing a plain text version of this content. The canonical link for it is here.

Posted to issues@calcite.apache.org by "Zhenghua Gao (Jira)" <ji...@apache.org> on 2019/11/21 09:11:00 UTC

[jira] [Created] (CALCITE-3529) TIMESTAMPDIFF function may overflow when handle unit of MICROSECOND/NANOSECOND

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)