Datetime field overflow error while inserting

Question

I have a table test_a

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;

Filled Like so:

CUSTOMER_RK	CUSTOMER_STATUS	EFFECTIVE_FROM_DTTM	EFFECTIVE_TO_DTTM
1	A	2004-01-20	2015-02-23
1	B	2016-02-24	2016-05-17
1	A	2017-05-18	9999-12-31
2	B	1998-09-01	2018-09-04
2	A	2018-09-05	2018-09-06
2	B	2019-09-07	2019-09-07
2	A	2019-09-08	9999-12-31

Tried to insert result of this select query in my table with this code:

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;

But got this error:

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

Why am I getting this error?

Upd.:

MySQL version: 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'

Result of SELECT '9999-12-31' + INTERVAL 1 DAY; was NULL

Answer 1

I cannot figure out what MySQL version and sql_mode combination will lead to this. Please add the following to your question as I am intrigued to know:

1. MySQL version (SELECT VERSION();)
2. sql_mode (SELECT @@sql_mode;)
3. Result of SELECT '9999-12-31' + INTERVAL 1 DAY;

UPDATE: Thank you for adding the answers to the above to your question. I don't know what I was doing/thinking when I looked at this last night, but it was easy to reproduce.

You can avoid it by NULLing the problematic value.

Change

DATE_ADD( EFFECTIVE_TO_DTTM, INTERVAL 1 DAY ) AS EFFECTIVE_FROM_DTTM,

to

DATE_ADD( NULLIF(EFFECTIVE_TO_DTTM, '9999-12-31'), INTERVAL 1 DAY ) AS EFFECTIVE_FROM_DTTM,

IMHO, a better option is to store a more appropriate value when the EFFECTIVE_TO_DTTM is unknown, such as NULL.

Here's a db<>fiddle.