Bug #8688 If the last part of a binary insert ends in '20', the '20' is left off
Submitted: 22 Feb 2005 15:05 Modified: 23 Mar 2005 12:20
Reporter: Jacob Cagley Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.23 OS:Microsoft Windows (Win 2000)
Assigned to: Sergei Glukhov CPU Architecture:Any

[22 Feb 2005 15:05] Jacob Cagley
Description:
When inserting a binary record as I have listed below, the last hex value '20' is dropped when the data is returned. If you run the sql in the 'How to repeat' section, you will see what I mean.

How to repeat:

create table Test ( t1 char(2) binary );
insert into Test ( t1 ) values ( Cast(0x4020 as binary) );
insert into Test ( t1 ) values ( Cast(0x4002 as binary) );
insert into Test ( t1 ) values ( Cast(0x2002 as binary) );
insert into Test ( t1 ) values ( Cast(0x1020 as binary) );
select t1, Hex(t1) from test;
[23 Feb 2005 18:13] Jacob Cagley
This error also occurs in 4.1.10 using varbinary().
[23 Feb 2005 20:30] Jacob Cagley
if you run this along with the other sql and change the char(2) to char(5) or something larger, you will see that all 0x20 are removed from the ending.....  as long as 0x20 is the last byte.

insert into Test ( t1 ) values ( Cast(0x552020 as binary) );

So on this sql statement, only 0x55 will show up instead of the 0x552020

If I try to insert 0x20202020, nothing will show up.
[23 Mar 2005 12:20] Alexander Barkov
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

This is expected behaviour for 4.0.x:  4.0 doesn't have a special
BINARY(n) type. BINARY(n) is an alias for CHAR(n) with case
sensitive sorting order and works according to:

   http://dev.mysql.com/doc/mysql/en/char.html

In 4.1 there is a separate datatype BINARY(N) which has
the problem your described. It will be fixed in 4.1 together
with other trailing spaces related problems descibed in:

http://bugs.mysql.com/bug.php?id=9278