MySQL Bugs: #9278: Trailing spaces and BINARY

Bug #9278	Trailing spaces and BINARY
Submitted:	18 Mar 2005 13:32	Modified:	31 Oct 2005 17:32
Reporter:	Alexander Barkov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1, 5.0	OS:
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
BINARY(N) and VARBINARY(N) should not ignore traling spaces.

How to repeat:
create table with binary or varbinary columns.
Insert string 'a' with and without trailing spaces.
Check
SELECT DISTINCT ..
SELECT .. GROUP BY ..
SELECT .. ORDER BY ..
SELECT .. WHERE a='a'
SELECT .. WHERE a='a '

etc.

Bar,

This isn't duplicate of http://bugs.mysql.com/bug.php?id=9255 ?

Miguel, 

Right. This is the same. However, Heikki is talking about 
comparison only, while the problem is wider and affects
DISTINCT, GROUP, ORDER wrong behavior as well.

Sorry, I didn't check when posting. Perhaps mine can be closed as "duplcated",
but Heikki's report should be extended to mention DISTICT/GROUP/ORDER too.

Would you like me to do these changes?

Bar,

Thanks. I will change the Heikki's report as duplicate of this
since you are extending its description.

Another part of this problem is trailing spaces truncation in a BINARY column:

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.

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;

See http://bugs.mysql.com/bug.php?id=8688 for more details

This is going to be discussed in Prague.
Unmarking "showstopper" flag.

The rules for padding and ignoring 0x00 / 0x20
are not being followed, or are being followed
for VARBINARY only, or are being followed for
MyISAM only. After tests with 5.0.15-rc-debug,
and consultation with the responsible parties,
I have changed the bug priority and added more
test conditions. Fix should be for 5.0 only.

Rule: "zero padding on insert". For any change of
a BINARY(n) target, e.g. INSERT or UPDATE or "SET
variable=value", if length(target) > length(source),
pad target with trailing 0x00 ('\0') bytes. It
doesn't happen.

/* Test: "zero padding on insert" */

mysql> create table th0 (s1 binary(2)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into th0 values (0x41),(0x4100);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select hex(s1) from th0;
+---------+
| hex(s1) |
+---------+
| 41      |
| 4100    |
+---------+
2 rows in set (0.00 sec)

mysql> delimiter //
mysql> create procedure ph0 (p1 binary(2)) begin declare v1 binary(2)
default 0x41; select hex(p1),hex(v1); end//
Query OK, 0 rows affected (0.03 sec)

mysql> call ph0(0x41)//
+---------+---------+
| hex(p1) | hex(v1) |
+---------+---------+
| 41      | 41      |
+---------+---------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> create table th1 (s1 binary(2), s2 varbinary(2));
Query OK, 0 rows affected (0.00 sec)

Rule: "no zero cut on val()". do not strip
trailing zeros when evaluating any expression.
But trail spaces are stripped on input, and
ignored during comparisons. This bug is for
BINARY, not VARBINARY.

/* Test: "no zero cut on val()" */

mysql> delete from th1;
Query OK, 1 row affected (0.00 sec)

mysql> insert into th1 values (0x4120,0x4120);
Query OK, 1 row affected (0.00 sec)

mysql> select length(concat('*',s1,'*',s2,'*')) from th1;
+-----------------------------------+
| length(concat('*',s1,'*',s2,'*')) |
+-----------------------------------+
|                                 6 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select * from th1 where s1 = 0x41;
+------+------+
| s1   | s2   |
+------+------+
| A    | A    |
+------+------+
1 row in set (0.00 sec)

Rule: "don't ignore end zero". When comparing,
you have to take end 0x00 into account, instead
of ignoring it. But when sorting, MySQL does
zero padding.

/* Test: "don't ignore end zero" */

mysql> create table th2 (s1 varbinary(2));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into th2 values (0x41,'b'),(0x4100,'a');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select hex(s1),s2 from th2 order by s1,s2;
+---------+------+
| hex(s1) | s2   |
+---------+------+
| 4100    | a    |
| 41      | b    |
+---------+------+
2 rows in set (0.00 sec)

Rule: "don't ignore end space". When comparing,
you have to take end 0x20 into account, instead
of ignoring it. This is the case with MyISAM and
VARBINARY. This is not the case with InnoDB and
VARBINARY.

/* Test: "don't ignore end space" */

mysql> create table th8 (s1 varbinary(2),primary key (s1)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into th8 values (0x4120),(0x41);
ERROR 1062 (23000): Duplicate entry 'A' for key 1

Fixed in 5.0.15

Two of the tests listed in the bug report still fail.

Failure 1:

Rule: "zero padding on insert". For any change of
a BINARY(n) target, e.g. INSERT or UPDATE or "SET
variable=value", if length(target) > length(source),
pad target with trailing 0x00 ('\0') bytes. It
doesn't happen.

/* Test: "zero padding on insert" */

mysql> delimiter //
mysql> create procedure ph0 (p1 binary(2)) begin declare v1 binary(2)
default 0x41; select hex(p1),hex(v1); end//
Query OK, 0 rows affected (0.03 sec)

mysql> call ph0(0x41)//
+---------+---------+
| hex(p1) | hex(v1) |
+---------+---------+
| 41      | 41      |
+---------+---------+
1 row in set (0.01 sec)

Failure 2:

/* Test: "don't ignore end space" */

mysql> create table th8 (s1 varbinary(2),primary key (s1)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into th8 values (0x4120),(0x41);
ERROR 1062 (23000): Duplicate entry 'A' for key 1

The two failing tests are now in separate bugs, bug#14188 and bug#14189.

The changes for trailing zeros/spaces occurred in 5.0.15,
and will be documented here:

http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html