[
https://issues.apache.org/jira/browse/CALCITE-4543?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18110322#comment-18110322
]
Aleksandr Efimov edited comment on CALCITE-4543 at 9/1/26 2:44 PM:
-------------------------------------------------------------------
Still reproduces on 1.42.0, and the code that does the narrowing reads the same
on main today.
{code}
SELECT INTERVAL '1.123456789' SECOND(1,9)
validated: SELECT INTERVAL '1.123456789' SECOND(1, 9)
row type: RecordType(INTERVAL SECOND(1, 9) EXPR$0)
plan: LogicalValues(tuples=[[{ 1123 }]])
{code}
The nine declared digits are accepted and three survive.
Nothing rejects the extra digits on the way in:
{{SqlIntervalQualifier.isFractionalSecondFieldInRange}} returns true
unconditionally, on the grounds that precision has already been checked by
pattern matching. The value is reduced in {{normalizeSecondFraction}}, which
multiplies the fraction by 1000 ("Decimal value can be more than 3 digits. So
just get the millisecond part"), and {{fillDayTimeIntervalValueArray}} stores
{{secondFrac.intValue()}}, so 0.123456789 becomes 123.456789 becomes 123.
{{SqlParserUtil.intervalToMillis}} bottoms out at {{conv[4] = 1; //
millisecond}}, and {{SqlLiteral.value(SqlNode)}} and {{getValueAs(Long.class)}}
for {{INTERVAL_DAY_TIME}} hand that long onward.
One thing that might keep a fix small: the Rex-side representation already
holds sub-millisecond values. {{RexBuilder.makeIntervalLiteral(new
BigDecimal("1123.456789"), SECOND(1,9))}} produces a literal whose
{{getValueAs(BigDecimal.class)}} is {{1123.456789}}; only
{{getValueAs(Long.class)}} drops the fraction. So the literal itself does not
need a representation change — the loss happens on the way in from SQL.
Where should the fix live, then? {{intervalToMillis}} is public and returns
{{long}}, so preserving the fraction means a sibling method with the millis one
delegating to it, rather than a change in place. Is that shape acceptable here,
or is the intent to keep this behind CALCITE-5266 and the wider representation
work? CALCITE-7529 fixed the analogous loss for TIME/TIMESTAMP casts without
touching the type system, so there may be room for the same here.
Happy to take it if the scoped version is welcome. I came at this from the
other side, converting a Substrait {{interval_day<9>}} into Calcite, where the
type survives the trip and the value does not.
was (Author: JIRAUSER313444):
Still reproduces on 1.42.0, and the code that does the narrowing reads the same
on main today.
{code:sql}
SELECT INTERVAL '1.123456789' SECOND(1,9)
{code}
validates, the row type comes back as {{INTERVAL SECOND(1, 9)}}, and the plan
is {{LogicalValues(tuples=[[{ 1123 }]])}}, so the nine declared digits are
accepted and three survive.
Nothing rejects the extra digits on the way in:
{{SqlIntervalQualifier.isFractionalSecondFieldInRange}} returns true
unconditionally, on the grounds that precision has already been checked by
pattern matching. The value is reduced in {{normalizeSecondFraction}}, which
multiplies the fraction by 1000 ("Decimal value can be more than 3 digits. So
just get the millisecond part"), and {{fillDayTimeIntervalValueArray}} stores
{{secondFrac.intValue()}}, so 0.123456789 becomes 123.456789 becomes 123.
{{SqlParserUtil.intervalToMillis}} bottoms out at {{conv[4] = 1; //
millisecond}}, and {{SqlLiteral.value(SqlNode)}} and {{getValueAs(Long.class)}}
for {{INTERVAL_DAY_TIME}} hand that long onward.
One thing that might keep a fix small: the Rex-side representation already
holds sub-millisecond values. {{RexBuilder.makeIntervalLiteral(new
BigDecimal("1123.456789"), SECOND(1,9))}} produces a literal whose
{{getValueAs(BigDecimal.class)}} is {{1123.456789}}; only
{{getValueAs(Long.class)}} drops the fraction. So the literal itself does not
need a representation change — the loss happens on the way in from SQL.
Where should the fix live, then? {{intervalToMillis}} is public and returns
{{long}}, so preserving the fraction means a sibling method with the millis one
delegating to it, rather than a change in place. Is that shape acceptable here,
or is the intent to keep this behind CALCITE-5266 and the wider representation
work? CALCITE-7529 fixed the analogous loss for TIME/TIMESTAMP casts without
touching the type system, so there may be room for the same here.
Happy to take it if the scoped version is welcome. I came at this from the
other side, converting a Substrait {{interval_day<9>}} into Calcite, where the
type survives the trip and the value does not.
> Interval literal loses 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
> Priority: Major
>
> 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.20.10#820010)
