Karman580 opened a new issue, #19210:
URL: https://github.com/apache/iotdb/issues/19210

   ### Search before asking
   
   - [x] I searched in the issues and found nothing similar.
   
   ### Version
   
   master @ `a4568b6e90` (2.0.11-SNAPSHOT). The reproduction below used a 
standalone distribution built from `cd31721c14`; `DataNodeDateTimeUtils`, 
`ASTVisitor.parseDateExpression` and `AstBuilder.parseDateExpression` are 
identical between the two commits.
   
   Environment: macOS 26.6.2, Java 27, server and CLI started with `TZ=UTC`, 
fresh data directory per run.
   
   ### Describe the bug and provide the minimal reproduce step
   
   Subtracting natural months in a date expression does not move back by a 
calendar month. When it handles `<time> - Nmo`, the parser computes the length 
of the N months **after** `<time>` and subtracts that length. So the result 
depends on how long the following month is:
   
   - `2024-03-15 - 1mo` gives `2024-02-13`, because it subtracts April's 31 
days.
   - `2024-03-31 - 1mo` gives `2024-03-01`, because it subtracts 30 days for 
Mar 31 to Apr 30.
   - `2024-02-15 + 1mo - 1mo` gives `2024-02-13`, so adding and then 
subtracting a month does not return the original time.
   
   The same code is used by the tree model (`ASTVisitor.parseDateExpression`) 
and the table model (`AstBuilder.parseDateExpression`). Both return the same 
wrong rows.
   
   Tree model (`timestamp_precision=ms`):
   
   ```sql
   CREATE TIMESERIES root.test.d1.s1 INT32
   INSERT INTO root.test.d1(time, s1) VALUES (2024-02-12T00:00:00+00:00, 12), 
(2024-02-13T00:00:00+00:00, 13), (2024-02-14T00:00:00+00:00, 14), 
(2024-02-15T00:00:00+00:00, 15), (2024-02-16T00:00:00+00:00, 16), 
(2024-02-28T00:00:00+00:00, 28), (2024-02-29T00:00:00+00:00, 29), 
(2024-03-01T00:00:00+00:00, 301)
   SELECT s1 FROM root.test.d1 WHERE time <= 2024-03-15T00:00:00+00:00 - 1mo
   SELECT s1 FROM root.test.d1 WHERE time >= 2024-03-31T00:00:00+00:00 - 1mo
   SELECT s1 FROM root.test.d1 WHERE time >= 2024-02-15T00:00:00+00:00 + 1mo - 
1mo AND time <= 2024-02-20T00:00:00+00:00
   ```
   
   Table model: the same data in `tdb.t(s1 INT32 FIELD)` and the same three 
`WHERE` clauses.
   
   ### What did you expect to see?
   
   `- Nmo` should be the inverse of `+ Nmo`, which already uses calendar 
months: it keeps the time of day and clamps to the end of the month, e.g. 
`2024-01-31 + 1mo` = `2024-02-29`.
   
   | Expression | Expected bound | Expected rows |
   |---|---|---|
   | `2024-03-15 - 1mo` | 2024-02-15 | 12, 13, 14, 15 |
   | `2024-03-31 - 1mo` | 2024-02-29 | 29, 301 |
   | `2024-02-15 + 1mo - 1mo` | 2024-02-15 | 15, 16 |
   
   ### What did you see instead?
   
   Actual output from a running server (`timestamp_precision=ms`, tree model; 
the table model returned the same rows):
   
   ```
   >>> SELECT s1 FROM root.test.d1 WHERE time <= 2024-03-15T00:00:00+00:00 - 1mo
   +------------------------+---------------+
   |                    Time|root.test.d1.s1|
   +------------------------+---------------+
   |2024-02-12T00:00:00.000Z|             12|
   |2024-02-13T00:00:00.000Z|             13|
   +------------------------+---------------+
   Total line number = 2
   >>> SELECT s1 FROM root.test.d1 WHERE time >= 2024-03-31T00:00:00+00:00 - 1mo
   +------------------------+---------------+
   |                    Time|root.test.d1.s1|
   +------------------------+---------------+
   |2024-03-01T00:00:00.000Z|            301|
   +------------------------+---------------+
   Total line number = 1
   >>> SELECT s1 FROM root.test.d1 WHERE time >= 2024-02-15T00:00:00+00:00 + 
1mo - 1mo AND time <= 2024-02-20T00:00:00+00:00
   +------------------------+---------------+
   |                    Time|root.test.d1.s1|
   +------------------------+---------------+
   |2024-02-13T00:00:00.000Z|             13|
   |2024-02-14T00:00:00.000Z|             14|
   |2024-02-15T00:00:00.000Z|             15|
   |2024-02-16T00:00:00.000Z|             16|
   +------------------------+---------------+
   Total line number = 4
   ```
   
   Output of the compiled `DataNodeDateTimeUtils.parseDateTimeExpressionToLong` 
(tree parser), `ms`, JVM zone UTC:
   
   ```
   2024-03-15T00:00:00 - 1mo      actual=2024-02-13T00:00   (calendar 
2024-02-15)
   2024-02-15T00:00:00 - 1mo      actual=2024-01-17T00:00   (calendar 
2024-01-15)
   2024-05-31T00:00:00 - 1mo      actual=2024-05-01T00:00   (calendar 
2024-04-30)
   2024-03-31T00:00:00 - 1mo      actual=2024-03-01T00:00   (calendar 
2024-02-29)
   2024-03-15T00:00:00 - 2mo      actual=2024-01-14T00:00   (calendar 
2024-01-15)
   2025-03-15T00:00:00 - 1mo      actual=2025-02-12T00:00   (calendar 
2025-02-15)
   1969-03-15T00:00:00 - 1mo      actual=1969-02-12T00:00   (calendar 
1969-02-15)
   2024-02-15T00:00:00 + 1mo      actual=2024-03-15T00:00   (calendar 
2024-03-15, addition is correct)
   2024-01-31T00:00:00 + 1mo      actual=2024-02-29T00:00   (calendar 
2024-02-29, addition is correct)
   ```
   
   With the zone set to `America/New_York`, the result also moves off the 
original time of day when the following month contains a DST change. For 
example, `2024-04-15T12:00:00 - 1mo` returns `2024-03-16T13:00`. I got the same 
results with `Asia/Kolkata`.
   
   ### Anything else?
   
   **Root cause.** In both `ASTVisitor.parseDateExpression` overloads and 
`AstBuilder.parseDateExpression`:
   
   ```java
   long duration = DataNodeDateTimeUtils.convertDurationStrToLong(time, 
durationText, false);
   time = "+".equals(op) ? Math.addExact(time, duration) : 
Math.subtractExact(time, duration);
   ```
   
   `convertDurationStrToLong` computes the `mo` part as 
`calendar.add(Calendar.MONTH, +N)` measured from `time`. This is a positive, 
forward-looking length, and it is then subtracted. The calendar step for `-` 
should be `Calendar.MONTH, -N`.
   
   **Documented semantics.** The relative-time table in the user guide says 
`1mo=30 days`, which matches the context-free fallback when `currentTime == 
-1`. Since #3139 (ISSUE-3116), date expressions use natural months when a 
reference time is available, and `+ 1mo` behaves as a calendar month. The 
current `- 1mo` result matches neither reading: `2024-03-15 - 1mo` gives Feb 
13, not Feb 14 (30 days) or Feb 15 (calendar month), and `x + 1mo - 1mo != x` 
under either. This report assumes `- Nmo` should mirror the existing 
calendar-month addition. If maintainers prefer a different definition, the fix 
and tests can follow it.
   
   **Proposed fix.** Pass the operator sign through to the duration conversion, 
so the month step is applied backwards (`Calendar.MONTH, -N`) from the original 
time. Mixed durations such as `1mo1d` keep their current left-to-right order. 
Addition, the other units, the `currentTime == -1` path (GROUP BY / CQ fixed 
durations) and the existing overflow checks are not changed.
   
   **Regression tests.** Unit tests on the tree parser for the cases above (ms 
precision, UTC session zone), plus a table-model parser case.
   
   **Related work.**
   - IOTDB-6224 / `ff19689fa6` fixed natural months for GROUP BY under non-ms 
precision through `TimeDuration`, which is a different code path.
   - #17893 / #18589 only added overflow checks here.
   - #18428 / #18547 are about CQ `EVERY`/`RANGE`.
   - I didn't find any existing issue or PR about the sign of month subtraction.
   
   **Precision note.** With `timestamp_precision=us` or `ns`, `2024-03-15 - 
1mo` also returns `2024-02-13`. In those precisions the `mo` length has a 
separate, independent problem: the precision-scaled timestamp is passed to 
`Calendar.setTimeInMillis`. That affects `+ 1mo` as well, and I'll report it 
separately. This issue covers only the subtraction direction.
   
   **dev/1.3** has the same `time -= convertDurationStrToLong(time, ...)` 
pattern in `ASTVisitor`.
   
   ### 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