Bug #108128 BIN function does not handle out of range correctly

• Submitted: 12 Aug 2022 8:49
• Modified: 16 Aug 2022 12:05
• Reporter: John Jove
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• OS: Any (20.04LTS)
• CPU Architecture: Any

[12 Aug 2022 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 2022 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 2022 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 2022 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.