Adding intervals of a fraction of a second to a datetime gives incorrect result #45779
Description
Describe what's wrong
Adding millisecond, microsecond or nanosecond intervals to a DateTime seems totally broken:
SELECT
CAST(today(), 'DateTime') AS t,
t + toIntervalSecond(1),
t + toIntervalMillisecond(1),
t + toIntervalMicrosecond(1),
t + toIntervalNanosecond(1)
Query id: d76c3515-6ea3-4997-885d-1c2b5fcf9c7f
Row 1:
──────
t: 2023-01-30 00:00:00
plus(CAST(today(), 'DateTime'), toIntervalSecond(1)): 2023-01-30 00:00:01
plus(CAST(today(), 'DateTime'), toIntervalMillisecond(1)): 2105-12-22 11:44:17
plus(CAST(today(), 'DateTime'), toIntervalMicrosecond(1)): 1977-11-28 02:08:17
plus(CAST(today(), 'DateTime'), toIntervalNanosecond(1)): 1983-04-14 20:27:13
I would expect to not see any changes to the Datetime (as the rounding goes to 0), and to see t+toIntervalMillisecond(1000) add 1 second to the DateTime.
It works as expected with DateTime64:
SELECT
CAST(today(), 'DateTime64') AS t,
t + toIntervalSecond(1),
t + toIntervalMillisecond(1),
t + toIntervalMicrosecond(1),
t + toIntervalNanosecond(1)
Query id: 1225a403-4f78-4130-b251-7235306b6be3
Row 1:
──────
t: 2023-01-30 00:00:00.000
plus(CAST(today(), 'DateTime64'), toIntervalSecond(1)): 2023-01-30 00:00:01.000
plus(CAST(today(), 'DateTime64'), toIntervalMillisecond(1)): 2023-01-30 00:00:00.001
plus(CAST(today(), 'DateTime64'), toIntervalMicrosecond(1)): 2023-01-30 00:00:00.000001
plus(CAST(today(), 'DateTime64'), toIntervalNanosecond(1)): 2023-01-30 00:00:00.000000001
Does it reproduce on recent release?
- 22.3: Not supported
- 22.8.11.15: Fails
- Master: Fails