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)
