Bug #119433 Inconsistent date conversion behaviors between text and integer

• Submitted: 20 Nov 9:30
• Reporter: John Jove
• Status: Open
• Category: MySQL Server: Data Types
• Severity: S1 (Critical)
• Version: 8.4.6
• OS: Any
• CPU Architecture: Any

[20 Nov 9:30] John Jove

Description:
I got the following four cases to test date conversion. When the stored values are different, the conversion behaviors are different.

When the stored value is 8010247, we can convert the text column to date, while the integer column does not, as shown in case 1 and case 2.

However, when the stored value is different like 7621214, we cannot convert the text column to date, while the integer column does, as shown in case 3 and case 4.

These above two pairs of cases show different conversion behaviors.

How to repeat:
-- case 1
DROP TABLE t1;
CREATE TABLE t1 (c1 CHAR(20));
INSERT INTO t1 VALUES (8010247);
SELECT CAST(c1 AS DATE) FROM t1; -- {1980-10-24}

-- case 2
DROP TABLE t1;
CREATE TABLE t1 (c1 INT);
INSERT INTO t1 VALUES (8010247);
SELECT CAST(c1 AS DATE) FROM t1; -- {NULL}

-- case 3
DROP TABLE t1;
CREATE TABLE t1 (c1 CHAR(20));
INSERT INTO t1 VALUES (7621214);
SELECT CAST(c1 AS DATE) FROM t1; -- {NULL}

-- case 4
DROP TABLE t1;
CREATE TABLE t1 (c1 INT);
INSERT INTO t1 VALUES (7621214);
SELECT CAST(c1 AS DATE) FROM t1; -- {0762-12-14}