Bug #108128 BIN function does not handle out of range correctly
Submitted: 12 Aug 8:49 Modified: 16 Aug 12:05
Reporter: John Jove Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version: OS:Any (20.04LTS)
Assigned to: CPU Architecture:Any

[12 Aug 8:49] John Jove
Description:
I try the following statements in MySQL 8.0.28, where the function BIN causes different behaviors.
In the first and second statement, the SELECT statement executes without warning.
In the third statement, the SELECT statement executes and throws a data truncated warning 'Truncated incorrect DECIMAL value: '100000000000000000000''.
All these statements return the same result, while the second statement should generate a warning to indicate the out of range error.

How to repeat:
SELECT 1 | BIN(100000000); -- 9223372036854775807
SELECT 1 | BIN(1000000000000); -- 9223372036854775807
SELECT 1 | BIN(100000000000000000000); -- 9223372036854775807

Suggested fix:
I think the BIN function only permits a range of value, for example, DECIMAL range. When an out of range value is feed in the BIN function, a warning should be exposed, even an error, to prevent unexpected behaviors.
[12 Aug 11:49] MySQL Verification Team
Hi Mr. Jove,

Thank you for your bug report.

However, you have not exactly provided sufficient info .....

Please, elucidate in details on what should each of those three statements return. Which results, what errors and which warnings .......

Waiting on your feedback.
[12 Aug 12:34] John Jove
mysql> SELECT 1 | BIN(100000000);
+---------------------+
| 1 | BIN(100000000)  |
+---------------------+
| 9223372036854775807 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT BIN(100000000);
+-----------------------------+
| BIN(100000000)              |
+-----------------------------+
| 101111101011110000100000000 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT 1 | 101111101011110000100000000;
+---------------------------------+
| 1 | 101111101011110000100000000 |
+---------------------------------+
|             9223372036854775807 |
+---------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------+
| Level   | Code | Message                                                          |
+---------+------+------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: '101111101011110000100000000' |
+---------+------+------------------------------------------------------------------+
1 row in set (0.00 sec)

I think SELECT 1 | BIN(100000000); should return above warning, but it does not.

mysql> SELECT 1 | BIN(1000000000000);
+------------------------+
| 1 | BIN(1000000000000) |
+------------------------+
|    9223372036854775807 |
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT BIN(1000000000000);
+------------------------------------------+
| BIN(1000000000000)                       |
+------------------------------------------+
| 1110100011010100101001010001000000000000 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT 1 | 1110100011010100101001010001000000000000;
+----------------------------------------------+
| 1 | 1110100011010100101001010001000000000000 |
+----------------------------------------------+
|                          9223372036854775807 |
+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------------+
| Level   | Code | Message                                                                       |
+---------+------+-------------------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: '1110100011010100101001010001000000000000' |
+---------+------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

I think SELECT 1 | BIN(1000000000000); should return above warning, but it does not.

mysql> SELECT 1 | BIN(100000000000000000000);
+--------------------------------+
| 1 | BIN(100000000000000000000) |
+--------------------------------+
|            9223372036854775807 |
+--------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: '100000000000000000000' |
+---------+------+------------------------------------------------------------+
1 row in set (0.00 sec)

I think  SELECT 1 | BIN(100000000000000000000); should return the warning message, which can be a truncated value of BIN(100000000000000000000). But current message is ok to indicate the computation exception.
[16 Aug 12:05] MySQL Verification Team
Hi Mr. Jove,

Thank you for your feedback.

However, this is not a bug. It is all documented in our Reference Manual.

Function BIN() expects a signed BIGINT number and it returns a string containing the same number, but in the binary format.

All the examples that you have sent us do the job correctly. Overflows are not checked, because they can not be checked, since there is no larger integer then 64-bit one .....

Not a bug.