MySQL Bugs: #94607: CAST( x as UNSIGNED) overflows with no warning/error

Bug #94607	CAST( x as UNSIGNED) overflows with no warning/error
Submitted:	8 Mar 2019 13:15	Modified:	8 Mar 2019 14:24
Reporter:	Catalin Besleaga	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S4 (Feature request)
Version:		OS:	Ubuntu
Assigned to:		CPU Architecture:	Any

Description:
CAST (.. as SIGNED/UNSIGNED);

select CAST(3.14e19 AS UNSIGNED);
+---------------------------+
| CAST(3.14e19 AS UNSIGNED) |
+---------------------------+
|       9223372036854775807 |
+---------------------------+
1 row in set (0,00 sec)

instead of returning error:
ERROR 22003: BIGINT value is out of range in 'cast(3.14e19 as UNSIGNED)'

How to repeat:
select CAST(3.14e19 AS UNSIGNED);

Suggested fix:
call check_integer_overflow before returning the value

Hi,

Thank you for your bug report.

Actually, that value of yours is still within the range of the unsigned 64-bit integer.

However, I tried values far above the range of 64-bit integers and did not get any warning.

Actually, this is a feature request. That is because there should not be an error, as CAST() can be used in very complex queries. It would go against one of the axioms of MySQL server, which is to try to do what user asks, if possible at all.

But, such a conversion should return a warning. I did not get any warning either.

Hence, this is a verified feature request.

> Actually, that value of yours is still within the range of the unsigned 64-bit integer.

Shouldn't 3.14 * 10^19 result still start with a 3 then, not a 9?

mysql> select CAST(3.14e17 AS UNSIGNED);
+---------------------------+
| CAST(3.14e17 AS UNSIGNED) |
+---------------------------+
|        314000000000000000 |
+---------------------------+

mysql> select CAST(3.14e18 AS UNSIGNED);
+---------------------------+
| CAST(3.14e18 AS UNSIGNED) |
+---------------------------+
|       3140000000000000000 |
+---------------------------+
1 row in set (0.000 sec)

> select CAST(3.14e19 AS UNSIGNED);
+---------------------------+
| CAST(3.14e19 AS UNSIGNED) |
+---------------------------+
|       9223372036854775807 |
+---------------------------+
1 row in set (0.003 sec)

See also bug #50567.