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: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 4.1, 5.0 | OS: | |
Assigned to: | Alexander Barkov | CPU Architecture: | Any |
[18 Mar 2005 13:32]
Alexander Barkov
[18 Mar 2005 13:44]
MySQL Verification Team
Bar, This isn't duplicate of http://bugs.mysql.com/bug.php?id=9255 ?
[18 Mar 2005 13:55]
Alexander Barkov
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?
[18 Mar 2005 14:01]
MySQL Verification Team
Bar, Thanks. I will change the Heikki's report as duplicate of this since you are extending its description.
[23 Mar 2005 12:16]
Alexander Barkov
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
[25 Mar 2005 13:56]
Alexander Barkov
This is going to be discussed in Prague. Unmarking "showstopper" flag.
[10 Oct 2005 21:10]
Peter Gulutzan
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
[17 Oct 2005 4:24]
Alexander Barkov
Fixed in 5.0.15
[20 Oct 2005 16:11]
Paul DuBois
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
[20 Oct 2005 18:44]
Peter Gulutzan
The two failing tests are now in separate bugs, bug#14188 and bug#14189.
[31 Oct 2005 17:32]
Paul DuBois
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