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)