Skip to content

Fix natural-month subtraction in date expressions - #19211

Open
Karman580 wants to merge 2 commits into
apache:masterfrom
Karman580:fix-natural-month-subtraction
Open

Karman580 wants to merge 2 commits into
apache:masterfrom
Karman580:fix-natural-month-subtraction

Conversation

@Karman580

@Karman580 Karman580 commented Nov 10, 2026 •

Copy link
Copy Markdown
Contributor

Fixes #19210

Summary

Subtracting natural months in a date expression lands on the wrong day. <time> - Nmo computed the length of the N months after <time> and subtracted it, so the result depended on how long the following month is:

  • 2024-03-15 - 1mo gave 2024-02-13.
  • 2024-03-31 - 1mo gave 2024-03-01.
  • 2024-02-15 + 1mo - 1mo gave 2024-02-13.

With this change, - Nmo goes back N calendar months from the original time, the same way + Nmo already goes forward: it keeps the time of day in the session time zone and clamps to the end of the month.

Root cause

All three date-expression parsers do the same thing:

  • ASTVisitor.parseDateExpression(ctx, String precision) and ASTVisitor.parseDateExpression(ctx, long currentTime) (tree)
  • AstBuilder.parseDateExpression (table)

They call DataNodeDateTimeUtils.convertDurationStrToLong(time, duration, false) and then apply Math.subtractExact(time, duration) for -. For mo, that conversion runs calendar.add(Calendar.MONTH, +N) from time. It always measures forward, even when the operator is -.

Changes

  • DataNodeDateTimeUtils: added convertDateExpressionOffset(time, duration, [precision,] negative), the entry point the date-expression parsers use. When negative is true, each unit in the duration string is converted with a negated value, so the month step becomes Calendar.MONTH, -N from the original time, and the returned offset is negative.
  • The month branch now anchors the calendar at the millisecond the timestamp falls in (Math.floorDiv(currentTime, ticksPerMillisecond)), because currentTime is in timestampPrecision units while Calendar works in milliseconds. The offset it returns is a whole number of milliseconds, so the sub-millisecond part of the timestamp is carried over to the result untouched.
  • Whether an anchor is present is now tracked separately from its value. Previously currentTime == -1 meant both "no anchor" and the timestamp 1969-12-31T23:59:59.999, so an intermediate value of -1 fell back to a flat 30 days. convertDateExpressionOffset always has a real anchor; the -1 convention of the other overloads is unchanged, so GROUP BY, CQ, IoTDBDescriptor and constructTimeDuration behave exactly as before.
  • The three parsers pass "-".equals(op) and then do Math.addExact(time, offset). Addition produces the same values as before at ms precision, where the new anchor arithmetic is an identity.
  • Mixed durations are still applied left to right (2024-03-31 - 1mo1d = 2024-02-29 - 1d = 2024-02-28). Fixed-length units (d, w, h, ...) give the same result as before, and the precision conversion for ms/us/ns units is symmetric for negative values.

Before and after, standalone server, timestamp_precision=ms, server and CLI in UTC. The tree and table models returned the same rows in both builds:

Query Before After
time <= 2024-03-15T00:00:00+00:00 - 1mo 2024-02-12, 02-13 2024-02-12, 02-13, 02-14, 02-15
time >= 2024-03-31T00:00:00+00:00 - 1mo 2024-03-01 2024-02-29, 03-01
time >= 2024-02-15T00:00:00+00:00 + 1mo - 1mo AND time <= 2024-02-20T00:00:00+00:00 2024-02-13 ... 02-16 2024-02-15, 02-16

Output after the fix (tree):

>>> SELECT s1 FROM root.test.d1 WHERE time <= 2024-03-15T00:00:00+00:00 - 1mo
|2024-02-12T00:00:00.000Z|             12|
|2024-02-13T00:00:00.000Z|             13|
|2024-02-14T00:00:00.000Z|             14|
|2024-02-15T00:00:00.000Z|             15|
Total line number = 4
>>> SELECT s1 FROM root.test.d1 WHERE time >= 2024-03-31T00:00:00+00:00 - 1mo
|2024-02-29T00:00:00.000Z|             29|
|2024-03-01T00:00:00.000Z|            301|
Total line number = 2

Tests

New tests in DateTimeUtilsTest. Each case is parsed by the real tree parser (DataNodeDateTimeUtils.parseDateTimeExpressionToLong) and the real table parser (SqlParser.createExpression) with a registered session, and the resulting timestamp is checked:

  • dateExpressionSubtractNaturalMonthTest (UTC) covers:
    • -1mo / -2mo across different month lengths
    • month-end clamping: 2024-03-31, 2025-03-31, 2024-05-31
    • mixed durations: 1mo1d and 1w2d (tree only, because the table grammar has no compound durations), plus - 1mo - 1d
    • a pre-1970 timestamp
    • the + 1mo - 1mo round trip
    • unchanged + 1mo cases
    • an intermediate timestamp of -1: - 1ms - 1mo, - 1ms1mo and - 1ms + 1mo from 1970-01-01
  • dateExpressionSubtractNaturalMonthWithTimeZoneTest: America/New_York across the DST start, and Asia/Kolkata.
  • dateExpressionNaturalMonthSubMillisecondPrecisionTest runs the same kind of cases at timestamp_precision=us and ns: - 1mo and + 1mo, month-end clamping, the + 1mo - 1mo round trip, a sub-millisecond anchor, and a pre-1970 anchor.

The new tests fail on current master (for example, tree: 2024-03-15T00:00:00+00:00 - 1mo expected:<1707955200000> but was:<1707782400000>) and pass with this change.

Local runs (JDK 25, macOS):

TZ=UTC ./mvnw test -pl iotdb-core/datanode -am -Dtest=DateTimeUtilsTest
Tests run: 16, Failures: 0, Errors: 0, Skipped: 1

TZ=UTC ./mvnw test -pl iotdb-core/datanode -am -Dsurefire.failIfNoSpecifiedTests=false \
  -Dtest='*Parser*Test,*ASTVisitor*Test,*StatementGenerator*Test,*AstBuilder*Test,*DateTime*Test,*TimeRange*Test,*GroupBy*Test,*Fill*Test,*Analyze*Test,*Predicate*Test,*Statement*Test,*TimePredicate*Test,*Filter*Test,*Precision*Test'
Tests run: 523, Failures: 0, Errors: 0, Skipped: 5

./mvnw spotless:check -pl iotdb-core/datanode                              -> BUILD SUCCESS
./mvnw test-compile -pl iotdb-core/datanode -am -DskipTests                -> BUILD SUCCESS
./mvnw test-compile -P with-zh-locale -pl iotdb-core/datanode -am -DskipTests -> BUILD SUCCESS

The skipped test is the existing @Ignored getConvertDurationIncludingMonthUnit. Note that convertDatetimeStrToLongTest1 in the same class fails in a +05:30 local time zone on master as well, independent of this change, so the runs above use TZ=UTC.

Microsecond and nanosecond precision

An earlier revision of this PR left mo arithmetic wrong at timestamp_precision=us and ns, where the precision-scaled timestamp was passed to Calendar.setTimeInMillis. That is fixed here as well, in both directions, after review: 2024-01-16T00:00:00+00:00 - 1mo now gives December 16 at every precision, and + 1mo is corrected along with it.

One unrelated problem remains, outside this change. DateTimeUtils.getInstantWithPrecision adds 999999 in its epochSecond < 0 && nano > 0 branch at us precision, so 1969-12-31T23:59:59.999+00:00 parses as 998999 rather than -1000. It affects any pre-1970 literal with a sub-second part at us precision, independent of durations, so the one test case that would hit it is ns only.


This PR has:

  • been self-reviewed.
  • added unit tests or modified existing tests to cover new code paths, ensuring the threshold for code coverage.
  • been tested in a test IoTDB cluster (standalone, tree and table SQL).
Key changed/added classes (or packages if there are too many classes) in this PR
  • DataNodeDateTimeUtils
  • ASTVisitor
  • AstBuilder
  • DateTimeUtilsTest

@Karman580

Copy link
Copy Markdown
Contributor Author

The CI workflows for this PR are waiting for maintainer approval since it comes from a fork. Could someone approve them when convenient? Locally the new DateTimeUtilsTest cases and the related parser/analyzer tests pass.

@HTHou
HTHou requested a review from JackieTien97 November 11, 2026 02:03
@Karman580
Karman580 force-pushed the fix-natural-month-subtraction branch from fe161ea to 2ef8fcc Compare November 11, 2026 06:09
@Karman580

Copy link
Copy Markdown
Contributor Author

Rebased onto current master with no code changes. On the previous run, the only red checks were the TODO check, which picked up a TODO line in PageIOChannel that only moved on master, and the 1C1D1A AINode job, whose runner was shut down mid-run. Could someone approve the workflows again when convenient?

@Karman580

Copy link
Copy Markdown
Contributor Author

All checks pass on the rebased commit except one Unit-Test job: CompactionTaskManagerTest.testRepeatedSubmitBeforeExecution failed on ubuntu with expected:<0> but was:<1> at line 93, which looks like the compaction task getting picked up before the count is checked. It passed on Windows and on the previous run, and this PR doesn't touch compaction. Could someone re-run that job when convenient?

A date expression such as `2024-03-15 - 1mo` computed the length of the
month after the given time and subtracted it, giving 2024-02-13 instead
of 2024-02-15 (and `2024-03-31 - 1mo` gave 2024-03-01).

Apply the subtracted duration backwards from the original time, so that
`- Nmo` goes back N calendar months the same way `+ Nmo` goes forward.
This affects both the tree and table date-expression parsers. Addition,
other units, and the context-free duration path are unchanged.

Fixes apache#19210
@Karman580
Karman580 force-pushed the fix-natural-month-subtraction branch from 2ef8fcc to 56f8c83 Compare November 12, 2026 07:10
@Karman580

Copy link
Copy Markdown
Contributor Author

Rebased onto current master again to get a fresh run, since I can't re-run the failed job from a fork. The code is unchanged; the only failure last time was the CompactionTaskManagerTest timing issue mentioned above. The workflows need approval again when someone has a moment.

@JackieTien97 JackieTien97 left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Please fix the two date-expression regressions below and add regression tests for them. I reproduced both against the PR head and its base.

convertDurationStrToLong(
currentTime == -1 ? -1 : Math.addExact(currentTime, total),
temp,
negative ? -temp : temp,

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

[P2] Preserve calendar-month subtraction at microsecond and nanosecond precision. In UTC with timestamp_precision=us or ns, 2024-01-16T00:00:00+00:00 - 1mo evaluates to December 16 on the base revision and December 17 on this PR in both SQL models. Calendar.setTimeInMillis interprets the scaled timestamp as milliseconds, so changing the direction changes which unrelated month supplies the duration. Please convert the anchor to milliseconds for calendar arithmetic, preserve its sub-millisecond component, and cover both precisions in the parser tests.

Copy link
Copy Markdown
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Confirmed, fixed in 6ed9afc. Calendar.setTimeInMillis was being handed a timestamp in timestampPrecision units, so at us and ns it anchored on an unrelated date; it now anchors at the millisecond the timestamp falls in, and since the offset is a whole number of milliseconds the sub-millisecond part of the anchor survives untouched.

dateExpressionNaturalMonthSubMillisecondPrecisionTest covers both precisions in both SQL models, including + 1mo, month-end clamping and a pre-1970 anchor. 2024-01-16T00:00:00+00:00 - 1mo gives December 16 now; against the previous head the same case fails with 1702771200000000, which is December 17.

One case there is ns only. At us precision getInstantWithPrecision adds 999999 in its epochSecond < 0 && nano > 0 branch, so 1969-12-31T23:59:59.999+00:00 parses as 998999 rather than -1000. That is independent of this change, so I left it alone here.

@@ -188,7 +204,7 @@ public static long convertDurationStrToLong(
total,
convertDurationStrToLong(
currentTime == -1 ? -1 : Math.addExact(currentTime, total),

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

[P3] Treat -1 as a valid intermediate timestamp. In the tree model at millisecond precision, 1970-01-01T00:00:00+00:00 - 1ms1mo first subtracts 1ms, yielding -1. The mo conversion interprets that value as a missing anchor and uses 30 days. The PR returns 1969-12-01T23:59:59.999Z, while the base revision returns the expected 1969-11-30T23:59:59.999Z. Please represent the presence of an anchor separately from its value so this case uses calendar arithmetic.

Copy link
Copy Markdown
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Confirmed, fixed in 6ed9afc. The presence of the anchor is now tracked separately from its value, and the date-expression parsers go through convertDateExpressionOffset, where the anchor is always real, so 1970-01-01T00:00:00+00:00 - 1ms1mo uses calendar arithmetic and returns 1969-11-30T23:59:59.999Z.

The -1 convention of the other overloads is unchanged, so GROUP BY, CQ, IoTDBDescriptor and constructTimeDuration keep their current behaviour. dateExpressionSubtractNaturalMonthTest now covers - 1ms - 1mo, - 1ms1mo and - 1ms + 1mo; the first fails against the previous head with -2592000001, the flat 30-day fallback.

Two problems in the date-expression duration path, both reachable from
the tree and the table parser.

Calendar.setTimeInMillis was fed a timestamp in timestampPrecision
units, so with timestamp_precision=us or ns the calendar landed on an
unrelated date and the month step measured that date's month instead.
Anchor the calendar at the millisecond the timestamp falls in. The
offset is a whole number of milliseconds, so the sub-millisecond part
of the timestamp is carried over to the result untouched.

A currentTime of -1 meant both "no anchor" and the timestamp
1969-12-31T23:59:59.999, so an intermediate value of -1 (for example
1970-01-01 - 1ms1mo) fell back to a flat 30 days. Represent the
presence of an anchor separately from its value, and give the date
expression parsers an entry point where the anchor is always present.
The -1 convention of the other overloads is unchanged, so GROUP BY,
CQ and constructTimeDuration behave exactly as before.
@Karman580

Copy link
Copy Markdown
Contributor Author

Both review comments are addressed in 6ed9afc, with the details in the two threads. The calendar anchor is now converted to milliseconds before the month step and the sub-millisecond part of the timestamp is preserved, and the presence of an anchor is tracked separately from its value so an intermediate -1 still uses calendar arithmetic.

Locally on JDK 25 with TZ=UTC: DateTimeUtilsTest 16 tests, 0 failures, 1 skipped (the existing @Ignored case), the wider parser and analyzer selection 523 tests, 0 failures, 5 skipped, and spotless:check plus test-compile -P with-zh-locale both succeed. Both new tests fail against the previous head of this branch.

The workflows are sitting at action_required again since this comes from a fork, so they need an approval when someone has a moment.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

[Bug] Subtracting natural months in date expressions (e.g. 2024-03-15 - 1mo) uses the following month's length

2 participants