插入数据时触发Datetime field overflow错误的原因排查

表结构与数据 #

建表语句：

CREATE TABLE `test_a` (
  `CUSTOMER_RK`         int         DEFAULT NULL,
  `CUSTOMER_STATUS`     varchar(45) DEFAULT NULL,
  `EFFECTIVE_FROM_DTTM` date        DEFAULT NULL,
  `EFFECTIVE_TO_DTTM`   date        DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

表中现有数据：

执行的INSERT语句 #

INSERT INTO test_a (
    CUSTOMER_RK, CUSTOMER_STATUS, EFFECTIVE_FROM_DTTM, EFFECTIVE_TO_DTTM
)
SELECT
    CUSTOMER_RK,
    CUSTOMER_STATUS,
    EFFECTIVE_FROM_DTTM,
    EFFECTIVE_TO_DTTM
FROM
    (
        SELECT
            CUSTOMER_RK,
            CUSTOMER_STATUS,
            DATE_ADD( EFFECTIVE_TO_DTTM, INTERVAL 1 DAY ) AS EFFECTIVE_FROM_DTTM,
            DATE_SUB( LEAD( EFFECTIVE_FROM_DTTM, 1, '9999-12-31' ) OVER ( ORDER BY EFFECTIVE_FROM_DTTM ), INTERVAL 1 DAY ) AS EFFECTIVE_TO_DTTM

        FROM
            test_a AS a1
        WHERE
            CUSTOMER_RK = 1
    ) AS a2
WHERE
    EFFECTIVE_FROM_DTTM < EFFECTIVE_TO_DTTM;

触发的错误信息 #

18:53:59    INSERT INTO test_a (CUSTOMER_RK, CUSTOMER_STATUS, EFFECTIVE_FROM_DTTM, EFFECTIVE_TO_DTTM) SELECT CUSTOMER_RK, CUSTOMER_STATUS, EFFECTIVE_FROM_DTTM, EFFECTIVE_TO_DTTM FROM ( SELECT CUSTOMER_RK, CUSTOMER_STATUS, DATE_ADD(EFFECTIVE_TO_DTTM, INTERVAL 1 DAY) AS EFFECTIVE_FROM_DTTM,  DATE_SUB(LEAD(EFFECTIVE_FROM_DTTM, 1, '9999-12-31') OVER (ORDER BY EFFECTIVE_FROM_DTTM), INTERVAL 1 DAY) AS EFFECTIVE_TO_DTTM FROM test_a AS a1 WHERE CUSTOMER_RK = 1 ) AS a2 WHERE EFFECTIVE_FROM_DTTM < EFFECTIVE_TO_DTTM

Error Code: 1441. Datetime function: datetime field overflow    0.000 sec

补充信息 #

• MySQL版本：8.0.34
• sql_mode：'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
• 测试语句结果：SELECT '9999-12-31' + INTERVAL 1 DAY; 返回 NULL

错误根源在于MySQL DATE类型的范围限制：DATE类型的最大值是9999-12-31，无法生成比这更晚的日期。

结合你的SQL逻辑来看：

1. 子查询中针对CUSTOMER_RK=1的第三条记录（EFFECTIVE_TO_DTTM = '9999-12-31'），执行DATE_ADD(EFFECTIVE_TO_DTTM, INTERVAL 1 DAY)时，试图生成10000-01-01，这超出了DATE类型的最大范围。
2. 虽然外层查询加了WHERE EFFECTIVE_FROM_DTTM < EFFECTIVE_TO_DTTM的过滤条件，但SQL的执行顺序是先完成子查询内的所有字段计算，再进行外层过滤。也就是说，日期溢出错误在过滤步骤之前就已经触发，导致整个语句直接报错。
3. 你的sql_mode包含STRICT_TRANS_TABLES严格模式，这种模式下，日期溢出操作不会返回NULL，而是直接抛出1441错误。这也解释了为什么单独测试SELECT '9999-12-31' + INTERVAL 1 DAY;返回NULL，但在INSERT语句中会报错——因为INSERT操作涉及到表字段的类型校验，严格模式下不允许这种溢出情况。

内容的提问来源于stack exchange，提问作者Oleksandr Zakharchenko