bug: DATE arithmetic at the 9999-12-31 boundary diverges three ways for identical durations: + INTERVAL 1 DAY silently wraps to 0001-01-01, + INTERVAL 24 HOUR goes backward 2 hours, only + INTERVAL 1 MONTH/YEAR correctly raises Invalid date #20416

Search before asking

• I had searched in the issues and found no similar issues.

Version

8.0.90-v1.2.936-nightly-d4f3935e7c (rust nightly, image built 2026-08-30 22:30) — the latest nightly as of 2026-08-31. Reproduced 2/2 runs.

The DAY-family silent wrap is also present on 8.0.90-v1.2.925-patch-9-6dc65e9df8 and v1.2.881; the HOUR/MINUTE/SECOND backward-arithmetic shape was verified as still live on this build.

What's Wrong?

DATE '9999-12-31' plus one day overflows the DATE range. All of 1 DAY, 24 HOUR, 1440 MINUTE, 86400 SECOND denote the same duration, and every one of these forms must raise Invalid date — exactly like + INTERVAL 1 MONTH / + INTERVAL 1 YEAR already do at the same boundary. Instead the DAY family splits into three different wrong behaviors:

Because of the divergence, a predicate comparing the result flip-flops between TRUE and FALSE depending on which equivalent interval literal the query happens to use (see the reproduction pair: 1 row vs 0 rows for semantically identical predicates) — query results become an artifact of spelling.

How to Reproduce?

DROP DATABASE IF EXISTS repro_date_overflow;
CREATE DATABASE repro_date_overflow;
USE repro_date_overflow;
CREATE TABLE t1(c DATE);
INSERT INTO t1 VALUES ('9999-12-31');

-- Base query (predicate written with the DAY interval):
SELECT t1.c FROM t1 WHERE ((t1.c + INTERVAL 1 DAY) = DATE '0001-01-01');
-- 9999-12-31
-- 1 row    <-- WRONG twice over: (1) the addition should raise Invalid date
--             (cf. + INTERVAL 1 MONTH below), instead it silently wraps;
--             (2) because of the wrap, this equality predicate HOLDS

-- Rewritten query (semantically identical predicate, equivalent interval literal):
SELECT ref0 FROM (
    SELECT t1.c AS ref0,
           ((t1.c + INTERVAL 24 HOUR) = DATE '0001-01-01') AS ref1
    FROM t1
) s WHERE ref1;
-- (empty result set)    <-- the HOUR form computes a THIRD wrong value
--                          (9999-12-30 22:00:00 — going BACKWARD 2 hours),
--                          so the same predicate FAILS here — 1 vs 0 disagreement

Semantics: 1 DAY, 24 HOUR, 1440 MINUTE, and 86400 SECOND are the same duration;
DATE '9999-12-31' plus that duration overflows the DATE range and every form must
raise Invalid date — exactly what + INTERVAL 1 MONTH and + INTERVAL 1 YEAR
already do. Instead the DAY form wraps to 0001-01-01 and the HOUR/MINUTE/SECOND
forms compute 9999-12-30 22:00:00.

All five forms verified individually (live, this build):

SELECT t1.c + INTERVAL 1 DAY     FROM t1;  -- 0001-01-01            (silent wrap)
SELECT t1.c - INTERVAL -1 DAY    FROM t1;  -- 0001-01-01            (silent wrap)
SELECT t1.c + INTERVAL 24 HOUR   FROM t1;  -- 9999-12-30 22:00:00   (backward 2 hours!)
SELECT t1.c + INTERVAL 86400 SECOND FROM t1; -- 9999-12-30 22:00:00 (same)
SELECT t1.c + INTERVAL 1 MONTH   FROM t1;  -- ERROR 1105 BadArguments: Invalid date:
                                           -- parameter 'year' with value 10000 is not in
                                           -- the required range of -9999..=9999   (correct)

• Cluster information: single node, local datafuselabs/databend Docker container (MySQL protocol). No special configuration.

Additional analysis:

• The defect is a scalar-kernel divergence independent of predicate placement: the
  same literal computes the same (wrong) value on the WHERE filter path and on the
  materialized projection path, and a row-level equivalence oracle pair
  (COUNT(*) WHERE p vs SUM(CASE WHEN p)) also agrees on either literal. The
  observable disagreement above comes purely from rewriting the predicate with the
  equivalent interval literal.
• Boundary-specific: values away from 9999-12-31 behave correctly; MONTH/YEAR units
  already raise the correct error at the same boundary — only the DAY-family units
  (DAY/HOUR/MINUTE/SECOND) mishandle the overflow, each in its own way.
• Related to the fixed LAST_DAY(DATE '9999-12-31') panic (v1.2.935-nightly) but a
  distinct defect: silent wrong results, not a panic.

Are you willing to submit PR?

• Yes I am willing to submit a PR!