[
https://issues.apache.org/jira/browse/CALCITE-5266?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18110499#comment-18110499
]
Aleksandr Efimov commented on CALCITE-5266:
-------------------------------------------
Checking on main at 1f94e23fa, the millisecond representation CALCITE-5919
points at is now the only place the precision goes. The type, the literal and
the plan all carry it — a row prints as {{13:30:25.575401:TIME(6)}} — and since
CALCITE-7529 landed in 1.43.0, casts between literals carry it too.
Which makes the symptoms filed separately one bug at one boundary. Reduced with
{{PROJECT_REDUCE_EXPRESSIONS}}, with the first line being the example from
CALCITE-5919:
{code}
EXTRACT(MICROSECOND FROM TIME '13:30:25.575401') -> 25575000 (expected
25575401) CALCITE-5919
EXTRACT(MICROSECOND FROM TIME '13:30:25.575') -> 25575000 TIME(6) and
TIME(3) agree
TIMESTAMPADD(MICROSECOND, 1, ...00:00:00.000000) -> unchanged
CALCITE-3530
TIMESTAMPDIFF(MICROSECOND, ...000001, ...000002) -> 0 (expected 1)
CALCITE-3530
TIMESTAMPDIFF(NANOSECOND, 1500-01-01, 2500-01-01) -> -5336492947419103232
CALCITE-3529
{code}
Anything at millisecond granularity or coarser is right: {{EXTRACT(MILLISECOND
...)}} gives 25575 and {{EXTRACT(SECOND ...)}} gives 25. The last line is a
positive span returning a negative number, silently, because nanoseconds
overflow a long.
On the representation itself, CALCITE-5919 already suggests switching
implementation once a type needs more than about 31 bits. Counting the units
each type would actually need puts a number on that criterion:
||type||today||precision 6||precision 9||
|TIME|int, ms of day, 27 bits|needs long, 37 bits|long, 47 bits|
|TIMESTAMP|long ms, +/-292M years|long us, +/-292k years|long ns, 1678..2262
only|
|INTERVAL day-time|long ms, fits to start precision 10|long us, fits to 8|long
ns, fits to 5|
Widening int to long therefore covers TIME all the way to TIME(9), without wide
arithmetic. A representation that cannot be an integral type is needed in
exactly two corners: TIMESTAMP(9) outside 1678..2262, and a day-time interval
whose leading precision is above 5 — Calcite's own limit is
MAX_INTERVAL_START_PRECISION = 10.
On the Avatica side, which this issue already flags as needing its own answer,
the numbers are worth having. Core calls DateTimeUtils 142 times across 23
files, and its public signatures are typed in milliseconds:
{{unixTimeExtract(TimeUnitRange, int time)}}, {{unixTimestampToString(long
timestamp, int precision)}} — where the precision argument only affects
formatting, so a TIMESTAMP(6) rendered from that value can only have zeros
after the third digit. The whole file mentions microseconds or nanoseconds
once. So the representation question reaches a separate release cycle and a
wire protocol with its own compatibility promises, which seems a bigger
constraint than the 31 files in core that assume MILLIS_PER_*.
Two things I cannot settle from the code, and would rather ask than assume:
Is limiting TIMESTAMP(9) to 1678..2262 acceptable? Arrow and pandas both made
that trade for nanosecond timestamps. If it is, an integral runtime unit chosen
from the declared precision covers everything except very wide day-time
intervals, and the wide-arithmetic path shrinks to a corner rather than being
the design.
And should the change go through Avatica's public API, or should core stop
routing high-precision paths through DateTimeUtils and keep its own helpers?
The first coordinates two release cycles; the second duplicates logic but
leaves the wire protocol alone.
I came here from CALCITE-4543, the same root cause on the literal side, and
would rather build on a decision than guess at one.
> Increase precision of TIMESTAMP, INTERVAL types to microseconds, nanoseconds
> ----------------------------------------------------------------------------
>
> Key: CALCITE-5266
> URL: https://issues.apache.org/jira/browse/CALCITE-5266
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Reporter: Dmitry Sysolyatin
> Priority: Major
>
> TIMESTAMP, INTERVAL types can preserve only milliseconds, because internal
> TIMESTAMP representation is Long. But it would be good to extend precision to
> microseconds and nanoseconds.
> I suggest to use standard java.time classes for representing TIMESTAMP and
> INTERVAL type internally:
> # TIMESTAMP type can be represent like java.time.Instant
> # INTERVAL type can be represent like java.time.Period + java.time.Duration
> What do you guys think ?
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
