MySQL Bugs: #8688: If the last part of a binary insert ends in '20', the '20' is left off

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:	Windows (Win 2000)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

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;

This error also occurs in 4.1.10 using varbinary().

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.

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