JackieTien97 opened a new issue, #19283:
URL: https://github.com/apache/iotdb/issues/19283
### Search before asking
- [x] I searched in the issues and found nothing similar.
### Version
master @ `ea7b4ffc5d` (2.0.11-SNAPSHOT). The same code is in v2.0.11 and
v1.3.7 (on `dev/1.3` it lives in
`iotdb-core/datanode/src/main/java/org/apache/iotdb/db/utils/DateTimeUtils.java`);
it dates back to #285, which added the `us`/`ns` precisions. The bug is plain
arithmetic, so it does not depend on the OS. Reproduced on macOS, JDK 21, 1C1D
with `timestamp_precision=us`.
### Describe the bug and provide the minimal reproduce step
With `timestamp_precision=us`, a datetime before `1970-01-01T00:00:00Z` that
has a non-zero fractional second is converted to a timestamp 999,999 us larger
than the right one. Whole seconds, anything from 1970 onwards, and the
`ms`/`ns` precisions are not affected.
The conversion is `DateTimeUtils.getInstantWithPrecision` in
`iotdb-core/node-commons/src/main/java/org/apache/iotdb/commons/queryengine/utils/DateTimeUtils.java`
(lines 566-575). Its `us` branch follows `Instant.toEpochMilli()`: for a
negative instant with a fractional part it multiplies `epochSecond + 1`, and
must then take that extra second back. It takes back 1 us instead of 1,000,000
us:
```java
if (instant.getEpochSecond() < 0 && instant.getNano() > 0) {
// adjustment can reduce the loss of the division
long millis = Math.multiplyExact(instant.getEpochSecond() + 1, 1000_000L);
long adjustment = instant.getNano() / 1000 - 1L;
return Math.addExact(millis, adjustment);
}
```
At the same point `Instant.toEpochMilli()` uses `nanos / 1000_000 - 1000`,
which takes back a whole second.
`convertDatetimeStrToLong` ends in this method, so datetime literals in
tree-model and table-model SQL (INSERT, WHERE, ...) and `CAST(<string> AS
TIMESTAMP)` are all affected. By reading the code, so are other callers that
parse a datetime string with the configured precision, such as the pipe source
`start-time`/`end-time` options.
1C1D, `timestamp_precision=us` set before the first start, CLI started with
`TZ=UTC`.
Tree model:
```sql
CREATE DATABASE root.us_test;
CREATE TIMESERIES root.us_test.d1.s1 INT64;
CREATE TIMESERIES root.us_test.d2.s1 INT64;
INSERT INTO root.us_test.d1(time, s1) VALUES (1969-12-31T23:59:59.5+00:00,
1);
INSERT INTO root.us_test.d1(time, s1) VALUES (1970-01-01T00:00:00.5+00:00,
2);
INSERT INTO root.us_test.d2(time, s1) VALUES (-500000, 3);
SELECT s1 FROM root.us_test.d1;
SELECT s1 FROM root.us_test.d2 WHERE time = 1969-12-31T23:59:59.5+00:00;
SELECT s1 FROM root.us_test.d2 WHERE time >= 1969-12-31T23:59:59.5+00:00 AND
time < 1970-01-01T00:00:00+00:00;
```
Table model:
```sql
CREATE DATABASE us_test;
USE us_test;
CREATE TABLE t (device_id STRING TAG, s INT64 FIELD);
INSERT INTO t (time, device_id, s) VALUES (1969-12-31T23:59:59.5+00:00,
'd1', 1);
INSERT INTO t (time, device_id, s) VALUES (1970-01-01T00:00:00.5+00:00,
'd1', 2);
INSERT INTO t (time, device_id, s) VALUES (-500000, 'd2', 3);
SELECT time, CAST(time AS INT64) AS raw_time, device_id, s FROM t WHERE
device_id = 'd1' ORDER BY time;
SELECT time, CAST(time AS INT64) AS raw_time, s FROM t WHERE device_id =
'd2' AND time = 1969-12-31T23:59:59.5+00:00;
SELECT CAST('1969-12-31T23:59:59.5Z' AS TIMESTAMP) AS ts,
CAST(CAST('1969-12-31T23:59:59.5Z' AS TIMESTAMP) AS INT64) AS raw_ts FROM t
WHERE device_id = 'd2';
```
### What did you expect to see?
`1969-12-31T23:59:59.5Z` is `-500000` us, one second before
`1970-01-01T00:00:00.5Z` (`500000`).
`SELECT s1 FROM root.us_test.d1`:
| Time | root.us_test.d1.s1 |
|---|---|
| 1969-12-31T23:59:59.500000Z (`-500000`) | 1 |
| 1970-01-01T00:00:00.500000Z (`500000`) | 2 |
Both queries on `d2` return the row written as `-500000`. In the table model
the first row is `-500000`, the `d2` query returns its row, and the CAST gives
`-500000`.
### What did you see instead?
The pre-1970 row is stored 1 us before the 1970 row:
| Time | root.us_test.d1.s1 |
|---|---|
| 1970-01-01T00:00:00.499999Z (`499999`) | 1 |
| 1970-01-01T00:00:00.500000Z (`500000`) | 2 |
Both queries on `d2` return an empty set (in the range query the lower bound
becomes `499999`, after the upper bound `0`). The table model shows the same
`499999` for the inserted row and for the CAST, and its `WHERE time =
1969-12-31T23:59:59.5+00:00` is empty too.
More `us` values, from `DateTimeUtils.convertDatetimeStrToLong(input,
ZoneOffset.UTC, 0, "us")` on master:
| input | expected | actual |
|---|---|---|
| `1969-12-31T23:59:59.5Z` | -500000 | 499999 |
| `1969-12-31T23:59:59.999999Z` | -1 | 999998 |
| `1969-12-31T23:59:58.999999Z` | -1000001 | -2 |
| `1969-12-31T23:59:59Z` | -1000000 | -1000000 |
| `1970-01-01T00:00:00.5Z` | 500000 | 500000 |
In a UTC+8 session, a local time on 1970-01-01 before 08:00 is affected too:
`1970-01-01 07:59:59.5` is the same instant as `1969-12-31T23:59:59.5Z` and
also gives `499999`.
### Anything else?
Subtracting `1_000_000L` instead of `1L`, as `Instant.toEpochMilli()` does
with `1000`, fixes it. The existing
`DateTimeUtilsTest.getInstantWithPrecisionTest` never calls this method (its
body is a copy of `convertDurationStrToLongTest3`), so nothing caught it.
### Are you willing to submit a PR?
- [x] I'm willing to submit a PR!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]