Repository navigation
SQL dates: invalid dates such as 2021-02-30 are silently moved to a valid day, and a DATE equals a DATETIME of the same day #10206
Description
Date and time handling in ArcadeDB SQL differs from DuckDB (and from SQL) in ways that change data silently:
- Invalid dates are moved to a valid one instead of rejected:
'2021-02-30'.asDate('yyyy-MM-dd')and.asDate()give2021-02-28,'2021-04-31'gives2021-04-30, andWHERE d1 = '2021-02-30'matches the record dated2021-02-28;'2021-02-03 24:00:00'.asDateTime('yyyy-MM-dd HH:mm:ss')gives the next day. DuckDB answers NULL (or refuses the literal). This looks like the lenient (SMART) resolver ofjava.time; a strict one would refuse these. - A DATE compares equal to a DATETIME of the same day: with
d1 = 2021-02-03andt1 = 2021-02-03 12:00,d1 = t1andd1 >= t1are true andd1 < t1false; SQL compares the date as midnight. - Smaller ones:
d1.format('HH')(any time pattern) on a DATE throwsUnsupportedTemporalTypeExceptioninstead of formatting midnight;d1 - t3with a DATETIME_NANOS operand throwslong overflowfor a 321-year gap that DATETIME_MICROS handles; and'0000-01-01'.asDate()parses while.asDate('yyyy-MM-dd')returns null.
Identical on main (a759246daa) and 26.10.2 (e5040d0339), JDK 25 (-XX:+UseCompactObjectHeaders) and JDK 21, timezone UTC; with an index on the date properties the answers are the same. Rows: id 1 (d1 2021-02-03, t1/t2 2021-02-03T12:00, s '2021-02-03'), id 2 (d1 2021-02-28, s '2021-02-30'), id 3 (s '2021-04-31'), id 4 (s '2021-02-03 24:00:00'), id 5 (s '0000-01-01'), id 6 (d1 2021-02-03, t2 1700-03-01T00:00, t3 1700-03-01T00:00:00.000000001).
| statement | ArcadeDB | DuckDB |
|---|---|---|
SELECT id FROM E WHERE d1 >= t1 |
[1] | [] |
SELECT id FROM E WHERE d1 = t1 |
[1] | [] |
SELECT id FROM E WHERE d1 < t1 |
[] | [1] |
SELECT id FROM E WHERE d1 >= t2 |
[1, 6] | [6] |
SELECT s.asDate('yyyy-MM-dd') AS v FROM E WHERE id = 2 |
[2021-02-28] | [null] |
SELECT s.asDate('yyyy-MM-dd') AS v FROM E WHERE id = 3 |
[2021-04-30] | [null] |
SELECT s.asDate() AS v FROM E WHERE id = 2 |
[2021-02-28] | [null] |
SELECT id FROM E WHERE d1 = '2021-02-30' |
[2] | error: ConversionException: Conversion Error: date field value out of range: |
SELECT s.asDateTime('yyyy-MM-dd HH:mm:ss') AS v FROM E WHERE id = 4 |
[2021-02-04T00:00] | [null] |
SELECT s.asDate() AS v FROM E WHERE id = 5 |
[0000-01-01] | [0001-01-01 (BC)] |
SELECT s.asDate('yyyy-MM-dd') AS v FROM E WHERE id = 5 |
[null] | [0001-01-01 (BC)] |
SELECT d1.format('HH') AS v FROM E WHERE id = 1 |
error UnsupportedTemporalTypeException: Unsupported field: HourOfDay | [00] |
SELECT d1.format('yyyy-MM-dd HH:mm:ss') AS v FROM E WHERE id = 1 |
error UnsupportedTemporalTypeException: Unsupported field: HourOfDay | [2021-02-03 00:00:00] |
SELECT d1.format('yyyy-MM-dd') AS v FROM E WHERE id = 1 |
[2021-02-03] | [2021-02-03] |
SELECT (d1 - t3) AS v FROM E WHERE id = 6 |
error ArithmeticErrorException: long overflow | [10127548799999999999] |
SELECT (d1 - t2) AS v FROM E WHERE id = 6 |
[PT2813208H] | [10127548800000000000] |
Found by a differential of SQL date handling against DuckDB (4,000 generated queries over DATE, DATETIME_SECOND, DATETIME, DATETIME_MICROS and DATETIME_NANOS; filtering, ordering, grouping, asLong and the other formats agreed). DuckDB's 0001-01-01 (BC) for year 0000 is its own notation, not a difference.
Activity
fix(#10247,#10210,#10206): a failed schema change rebuilds the bucket…
test(#10206): exercise the zoned <= bound on a DATE
fix(#10247,#10245,#10210,#10206): schema changes scale with the types…
lvca commentedon Oct 18, 2026
Fixed in #10259 (26.11.1).
2021-02-30are now refused instead of being silently moved to another day.=,<,<=,>,>=andBETWEEN, and index lookups on DATE properties follow the same rule.❤️ Thanks for helping make ArcadeDB better! ArcadeDB is free and open source, sustained by its community. If it's useful to you or your company, please consider becoming a sponsor to keep development going.
fix(#10247,#10245,#10210,#10206): schema changes scale with the types…