Bug #105355 time <cmp> string returns wrong result
Submitted: 28 Oct 2021 8:33 Modified: 28 Oct 2021 9:12
Reporter: Huaiyu Xu Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.36, 8.0.27 OS:MacOS
Assigned to: CPU Architecture:x86

[28 Oct 2021 8:33] Huaiyu Xu 
Description:
result of  `select cast("12:12:12.000" as time(3)) = "12:12:12"` is unexpected.

How to repeat:
mysql> select cast("12:12:12.000" as time(3)) = "12:12:12";
Field   1:  `cast("12:12:12.000" as time(3)) = "12:12:12"`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      BINARY NUM

+----------------------------------------------+
| cast("12:12:12.000" as time(3)) = "12:12:12" |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+
1 row in set (0.01 sec)

mysql> select cast("12:12:12.000" as datetime(3)) = "12:12:12";
Field   1:  `cast("12:12:12.000" as datetime(3)) = "12:12:12"`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      BINARY NUM

+--------------------------------------------------+
| cast("12:12:12.000" as datetime(3)) = "12:12:12" |
+--------------------------------------------------+
|                                                1 |
+--------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
`select cast("12:12:12.000" as time(3)) = "12:12:12"` should also return 1.
[28 Oct 2021 9:12] MySQL Verification Team 
Hello Huaiyu Xu,

Thank you for the report and test case.

regards,
Umesh