Bug #114058 The return value of the CAST(... AS UNSIGNED) expression is out of range.

• Submitted: 20 Feb 2024 7:28
• Modified: 20 Feb 2024 11:06
• Reporter: Bob Wong
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.35
• OS: Any
• CPU Architecture: Any

[20 Feb 2024 7:28] Bob Wong

Description:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.35    |
+-----------+
1 row in set (0.00 sec)

mysql>
mysql> CREATE TABLE t0 (c0 TINYINT);
Query OK, 0 rows affected (0.21 sec)

mysql> INSERT INTO t0(c0) VALUES (-30);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT CAST(t0.c0 AS UNSIGNED) FROM t0;
+-------------------------+
| CAST(t0.c0 AS UNSIGNED) |
+-------------------------+
|    18446744073709551586 |
+-------------------------+
1 row in set (0.00 sec)

mysql>

The type of t0.c0 is TINYINT, and the corresponding type of CAST(t0.c0 AS UNSIGNED) is TINYINT UNSIGNED, with a value range from 0 to 255.
The corresponding type for 18446744073709551586 is BIGINT UNSIGNED.

How to repeat:
mysql> CREATE TABLE t0 (c0 TINYINT);
Query OK, 0 rows affected (0.21 sec)

mysql> INSERT INTO t0(c0) VALUES (-30);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT CAST(t0.c0 AS UNSIGNED) FROM t0;
+-------------------------+
| CAST(t0.c0 AS UNSIGNED) |
+-------------------------+
|    18446744073709551586 |
+-------------------------+
1 row in set (0.00 sec)

mysql>

[20 Feb 2024 11:06] MySQL Verification Team

Hi Mr. Wong,

Thank you very much for your bug report.

However, this is not a bug.

Unsigned numbers do not have negative values. Also, since you did not precise the width of the integer column, it has defaulted to the longest integer column type available.

This type of conversion is prohibited by SQL standard, which means that (by the standard) you would get no result, but the error message.  Hence,  it is up to each RDBMS implementation to define its own conversion rules. MySQL has chosen the best available one and it will not be changed.

Not a bug.