[jira] [Created] (CALCITE-4543) Interval literal looses a fractional second when it has scale greater than 3

["Vladimir Ozerov (Jira)" <ji...@apache.org>]

Vladimir Ozerov created CALCITE-4543:
----------------------------------------

             Summary: Interval literal looses a fractional second when it has scale greater than 3
                 Key: CALCITE-4543
                 URL: https://issues.apache.org/jira/browse/CALCITE-4543
             Project: Calcite
          Issue Type: Bug
          Components: core
    Affects Versions: 1.26.0
            Reporter: Vladimir Ozerov


The allowed fractional second precision for literals is between 1 and 9, with the default value set to 6. The relevant constants are defined in the {{SqlTypeName}} class, see {{DEFAULT_INTERVAL_FRACTIONAL_SECOND_PRECISION}} and {{MAX_INTERVAL_FRACTIONAL_SECOND_PRECISION}}.

At the same time, the {{DAY-SECOND}} literals are converted to milliseconds during actual processing, see {{SqlParserUtil.intervalToMillis}}. As a result, the sub-millisecond part is lost silently:
{code}
CAST('2021-01-01 10:00:00' as TIMESTAMP) + INTERVAL '0.001' SECOND => 2021-01-01 10:00:00.001
CAST('2021-01-01 10:00:00' as TIMESTAMP) + INTERVAL '0.0001' SECOND => 2021-01-01 10:00:00.0
{code}

There are two possible solutions here, which are orthogonal to each other:
# Since Apache Calcite claims to support up to 9 fractional positions, it should work with nanoseconds, rather than milliseconds.
# Provide a way for products to override the default and maximum scale for intervals.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)