Bug #111354 varbinary result incorrect

• Submitted: 12 Jun 2023 2:37
• Modified: 12 Jun 2023 13:00
• Reporter: haizhen xue
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[12 Jun 2023 2:37] haizhen xue

Description:
create table test (varbinary_col varbinary(255) DEFAULT NULL);
insert into test values(0);
insert into test values(_binary 'dddddddddd1');
mysql> select * from test where varbinary_col in (0);
+------------------------------+
| varbinary_col                |
+------------------------------+
| 0x30                         |
| 0x6464646464646464646431     |
+------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'dddddddddd1' |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

How to repeat:
create table test (varbinary_col varbinary(255) DEFAULT NULL);
insert into test values(0);
insert into test values(_binary 'dddddddddd1');
mysql> select * from test where varbinary_col in (0);
+------------------------------+
| varbinary_col                |
+------------------------------+
| 0x30                         |
| 0x6464646464646464646431     |
+------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'dddddddddd1' |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
mysql> select * from test where varbinary_col in (0);
expect result is 0x30.

[12 Jun 2023 13:00] MySQL Verification Team

Hi Mr. xue,

Thank you for your bug report.

However, this is not a bug.

MySQL database system is not based on the strict type checking. That is how it was designed from the beginning. We can not change that behaviour, since millions of installations are depending on that behaviour.

When you insert wrong data type into certain column, then MySQL tries to find a third data type that would be something like common denominator. 

Hence , that is why you get the results that you get. 

This is all explained fully throughout our Reference Manual, which you can find on dev.mysql.com.

Not a bug.