MySQL Bugs: #105983: `SELECT GREATEST(time '20:00:00', 120000) + 0.00` should return 20

Bug #105983	`SELECT GREATEST(time '20:00:00', 120000) + 0.00` should return 20
Submitted:	27 Dec 2021 9:42	Modified:	27 Dec 2021 10:00
Reporter:	Huaiyu Xu	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	8.0.26, 8.0.27, 5.7.36	OS:	MacOS
Assigned to:		CPU Architecture:	Any

Description:
mysql> SELECT GREATEST(time '20:00:00', 120000) + 0.00;

+------------------------------------------+
| GREATEST(time '20:00:00', 120000) + 0.00 |
+------------------------------------------+
|                                200000.00 |
+------------------------------------------+

The return type of `GREATEST(time '20:00:00', 120000)` is VAR_STRING:

mysql> SELECT GREATEST(time '20:00:00', 120000);
Field   1:  `GREATEST(time '20:00:00', 120000)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8mb4_0900_ai_ci (255)
Length:     40
Max_length: 8
Decimals:   0
Flags:      NOT_NULL

+-----------------------------------+
| GREATEST(time '20:00:00', 120000) |
+-----------------------------------+
| 20:00:00                          |
+-----------------------------------+
1 row in set (0.00 sec)

We'll get 20 if we query ` select "20:00:00" + 0.00;`

mysql> select "20:00:00" + 0.00;

+-------------------+
| "20:00:00" + 0.00 |
+-------------------+
|                20 |
+-------------------+
1 row in set, 1 warning (0.00 sec)

How to repeat:
SELECT GREATEST(time '20:00:00', 120000) + 0.00;
select "20:00:00" + 0.00;

Suggested fix:
`SELECT GREATEST(time '20:00:00', 120000) + 0.00` should also return 20

Hello Huaiyu Xu,

Thank you for the report and test case.

regards,
Umesh

I'm not sure it's a bug or not,but the return type of `GREATEST(time '20:00:00', 120000)` actually is time '20:00:00' and "select time "20:00:00" + 0.00;" equal to "SELECT GREATEST(time '20:00:00', 120000) + 0.00;" .