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]

Reply via email to