There is a difference between explicit and implicit DATETIME->TIME conversion when using a datetime with zero YYYYMM and a non-zero day:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a TIME);

INSERT INTO t1 VALUES (TIMESTAMP'0000-00-01 10:20:30');

SELECT  * FROM t1;

+----------+

| a        |

+----------+

| 34:20:30 |

+----------+

SELECT CAST(TIMESTAMP'0000-00-01 10:20:30' AS TIME);

+----------------------------------------------+

| CAST(TIMESTAMP'0000-00-01 10:20:30' AS TIME) |

+----------------------------------------------+

| 10:20:30                                     |

+----------------------------------------------+

Notice, implicit conversion adds days to hours, while explicit does not.

The problem also presents in the upstream.

The same problem is repeatable when casting a DATETIME column to TIME.

CREATE OR REPLACE TABLE t1 (a DATETIME);

INSERT INTO t1 VALUES ('0000-00-01 10:20:30');

SELECT CAST(a AS TIME) FROM t1;

+-----------------+

| CAST(a AS TIME) |

+-----------------+

| 10:20:30        |

+-----------------+

Note, if I now ALTER the DATETIME column to TIME, it does mix days to hours:

ALTER TABLE t1 MODIFY a TIME;

SELECT * FROM t1;

+----------+

| a        |

+----------+

| 34:20:30 |

+----------+

This is inconsistent. Conversion should work the same way in all cases:

• Explicit CAST
• Implicit conversion on INSERT and SET
• Explicit conversion on ALTER.

In 10.3 (as of 2018-02-07) implicit conversion from DATETIME to TIME at INSERT time and explicit conversion at ALTER time work as follows:

• if the YYYYMM part is zero, then the data represents a "INTERVAL DAY TO SECOND"-alike value, then mix days to hours
• If the YYYYMM part is not zero, then the data represents a real DATETIME value, then truncate YYYYMMDD and preserve the hhmmss part only.

There are two options:
1. Make CAST work like INSERT/ALTER (mix days to hours if YYYYMM is zero)
2. Make INSERT/ALTER work like CAST (truncate the YYYYMMDD part)

Sergei is included towards #2.
Bar would also prefer #2, but is afraid that some users can have upgrade problems.