MySQL Bugs: #114526: Inconsistent Results when Using ROUND(), GREATEST(), and CAST()

Bug #114526	Inconsistent Results when Using ROUND(), GREATEST(), and CAST()
Submitted:	1 Apr 13:35	Modified:	2 Apr 10:38
Reporter:	Jingzhou Fu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.3.0	OS:	Linux (Ubuntu 20.04, docker)
Assigned to:		CPU Architecture:	x86
Tags:	Logic bug

Description:
There are inconsistent results when using ROUND(), GREATEST(), and CAST() in a SELECT statement in a subquery compared to the main query.

The two equivalent queries output different results. However, since the query is related to truncated values, I don't know which result is correct. Is it a bug, or just a feature?

How to repeat:
mysql> SELECT ROUND ( GREATEST ( '1', CAST( '2020-12-12' AS DATE )));
+--------------------------------------------------------+
| ROUND ( GREATEST ( '1', CAST( '2020-12-12' AS DATE ))) |
+--------------------------------------------------------+
|                                               20201212 |
+--------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT ROUND (x) FROM (SELECT GREATEST ( '1', CAST( '2020-12-12' AS DATE )) AS x) AS y;
+-----------+
| ROUND (x) |
+-----------+
|      2020 |
+-----------+
1 row in set, 2 warnings (0.00 sec)

Hi Mr. Fu,

Thank you so much for your bug report.

However, this is not a bug, but a feature ......

Here, you are actually using two different types in both expressions. One type is a string and the other type is a DATE. According to the SQL Standard, a database server should return a hard error ....... and no result, what so ever.

MySQL attempts to return something back to the client side, which makes non-compliant with the SQL standard, but is more friendly.

In all such situation a third data type is chosen and both values are converted to that resolving type. In MySQL case, it is an integer. Other RDBMS products may choose a different resolution type, but this is totally out of standard and therefore a choice of the RDBMS architecture.

Not a bug.