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:
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

[1 Apr 13:35] Jingzhou Fu
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)
[2 Apr 10:38] MySQL Verification Team
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.