Bug #42731 Binary column padded with \0, not trimmed on retrieve

• Submitted: 10 Feb 2009 14:56
• Modified: 10 Feb 2009 17:43
• Reporter: Olaf van der Spek (Basic Quality Contributor)
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.1.31
• OS: Windows
• CPU Architecture: Any
• Tags: qc

[10 Feb 2009 14:56] Olaf van der Spek

Description:
The docs say binary(M) is similar to char(M).

> A fixed-length string that is always right-padded with spaces to the specified length when stored. 

1. It doesn't say binary(M) is padded with 0x00.

> Trailing spaces are removed when CHAR values are retrieved unless the PAD_CHAR_TO_FULL_LENGTH  SQL mode is enabled. 

2. It doesn't appear to do this. That SQL mode isn't enabled.

http://dev.mysql.com/doc/refman/5.1/en/string-type-overview.html

How to repeat:
mysql> create table t (f binary(4));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t values ('A');
Query OK, 1 row affected (0.00 sec)

mysql> select concat(f, '!'), f = 'A' from t;
+----------------+---------+
| concat(f, '!') | f = 'A' |
+----------------+---------+
| A   !          |       0 |
+----------------+---------+
1 row in set (0.00 sec)

[10 Feb 2009 17:17] Sergei Golubchik

See here: http://dev.mysql.com/doc/refman/5.1/en/binary-varbinary.html
"
Values are right-padded with 0x00 on insert, and no trailing bytes are removed on select.
"

[10 Feb 2009 17:43] Olaf van der Spek

Yeah, thanks, my bad.
Binary values shouldn't be trimmed anyway.