Bug #119432 Inconsistent time conversion behaviors between text and integer

• Submitted: 20 Nov 8:47
• Modified: 21 Nov 3: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 8:47] John Jove

Description:
I got the following four cases to test time conversion. When the converted value is accepted by the time data type, both text and integer column successfully return the expected results, like case 1and case 2.

However, when an overflow value is stored, the text column returns a truncated value, while the integer column returns NULL, as shown in case 3 and case 4.

I may expect that the consistent results should be returned, either both the truncated value or NULL value.

How to repeat:
-- case 1
DROP TABLE t1;
CREATE TABLE t1 (c1 CHAR(20));
INSERT INTO t1 VALUES (8385959);
SELECT CAST(c1 AS TIME) FROM t1; -- {838:59:59}

-- case 2
DROP TABLE t1;
CREATE TABLE t1 (c1 BIGINT);
INSERT INTO t1 VALUES (8385959);
SELECT CAST(c1 AS TIME) FROM t1; --  {838:59:59}

-- case 3
DROP TABLE t1;
CREATE TABLE t1 (c1 CHAR(20));
INSERT INTO t1 VALUES (9941110);
SELECT CAST(c1 AS TIME) FROM t1; -- {838:59:59}

-- case 4
DROP TABLE t1;
CREATE TABLE t1 (c1 BIGINT);
INSERT INTO t1 VALUES (9941110);
SELECT CAST(c1 AS TIME) FROM t1; --  {NULL}

[20 Nov 9:14] John Jove

The version is 8.4.6

[21 Nov 3:30] John Jove

I try the case 4 in MariaDB 11.8.3, which returns 838:59:59 as expected.

-- case 4
DROP TABLE t1;
CREATE TABLE t1 (c1 BIGINT);
INSERT INTO t1 VALUES (9941110);
SELECT CAST(c1 AS TIME) FROM t1; --  {838:59:59}