From: Kevin Lewis Date: February 18 2009 6:29pm Subject: Re: Patch for bug#42208 List-Archive: http://lists.mysql.com/falcon/552 Message-Id: <499C539E.5090507@sun.com> MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset=windows-1252 Content-Transfer-Encoding: 7BIT Ann W. Harrison wrote: > So, in a word, the standard writers chose to punt on whether a > varbinary 0x00 ix the same as 0x0000 when it's a varbinary, but > say that they aren't equal if they're blobs. > > So the next question is "who is the implementer here?" Well, here is the MySQL (read MyISAM) implemetation; 1) BINARY fixed length fields are always padded out to the fixed length. Thus for a BINARY(5) column; 0x00, 0x0000, 0x000000 and 0x00000000 are all equal because they are all stored and sorted as 0x0000000000. 2) VARBINARY fields must take into account the actual length. Thus for a VARBINARY column; 0x00, 0x0000, 0x000000 and 0x00000000 are all different and sorted by length. We do pretty good at that except for multi-segmented fields. Check out the example below. The last SELECT gets the wrong (according to MySQL) order because it uses LIMIT with a multi-segmented field and sees the VARBINARY values as the same. Lars-Erik, this might make a good test script. mysql> drop table t1; Query OK, 0 rows affected (0.01 sec) mysql> create table t1 (f1 BINARY(5), f2 VARBINARY(5), f3 char(10), -> key(f1), key(f2), key(f1,f2,f3)) engine=falcon; Query OK, 0 rows affected (0.06 sec) mysql> insert into t1 values ('B', 'B', 'B'); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values ('A', 'A', 'A'); Query OK, 1 row affected (0.01 sec) mysql> insert into t1 values (0x0000000000, 0x0000000000, 'five'); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (0x00000000, 0x00000000, 'four'); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (0x000000, 0x000000, 'three'); Query OK, 1 row affected (0.02 sec) mysql> insert into t1 values (0x0000, 0x0000, 'two'); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (0x00, 0x00, 'one'); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values ('', '', 'none'); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (null, null, 'null'); Query OK, 1 row affected (0.00 sec) mysql> select f1, hex(f1), f2, hex(f2), f3 from t1; +-------+------------+-------+------------+-------+ | f1 | hex(f1) | f2 | hex(f2) | f3 | +-------+------------+-------+------------+-------+ | B | 4200000000 | B | 42 | B | | A | 4100000000 | A | 41 | A | | | 0000000000 | | 0000000000 | five | | | 0000000000 | | 00000000 | four | | | 0000000000 | | 000000 | three | | | 0000000000 | | 0000 | two | | | 0000000000 | | 00 | one | | | 0000000000 | | | none | | NULL | NULL | NULL | NULL | null | +-------+------------+-------+------------+-------+ 9 rows in set (0.00 sec) mysql> select f1, hex(f1), f2, hex(f2), f3 from t1 order by f1; +-------+------------+-------+------------+-------+ | f1 | hex(f1) | f2 | hex(f2) | f3 | +-------+------------+-------+------------+-------+ | NULL | NULL | NULL | NULL | null | | | 0000000000 | | 0000000000 | five | | | 0000000000 | | 00000000 | four | | | 0000000000 | | 000000 | three | | | 0000000000 | | 0000 | two | | | 0000000000 | | 00 | one | | | 0000000000 | | | none | | A | 4100000000 | A | 41 | A | | B | 4200000000 | B | 42 | B | +-------+------------+-------+------------+-------+ 9 rows in set (0.00 sec) mysql> select f1, hex(f1), f2, hex(f2), f3 from t1 order by f2; +-------+------------+-------+------------+-------+ | f1 | hex(f1) | f2 | hex(f2) | f3 | +-------+------------+-------+------------+-------+ | NULL | NULL | NULL | NULL | null | | | 0000000000 | | | none | | | 0000000000 | | 00 | one | | | 0000000000 | | 0000 | two | | | 0000000000 | | 000000 | three | | | 0000000000 | | 00000000 | four | | | 0000000000 | | 0000000000 | five | | A | 4100000000 | A | 41 | A | | B | 4200000000 | B | 42 | B | +-------+------------+-------+------------+-------+ 9 rows in set (0.00 sec) mysql> select f1, hex(f1), f2, hex(f2), f3 from t1 order by f1,f2,f3 ; +-------+------------+-------+------------+-------+ | f1 | hex(f1) | f2 | hex(f2) | f3 | +-------+------------+-------+------------+-------+ | NULL | NULL | NULL | NULL | null | | | 0000000000 | | | none | | | 0000000000 | | 00 | one | | | 0000000000 | | 0000 | two | | | 0000000000 | | 000000 | three | | | 0000000000 | | 00000000 | four | | | 0000000000 | | 0000000000 | five | | A | 4100000000 | A | 41 | A | | B | 4200000000 | B | 42 | B | +-------+------------+-------+------------+-------+ 9 rows in set (0.00 sec) mysql> select f1, hex(f1), f2, hex(f2), f3 from t1 order by f1,f2,f3 limit 7 ; +-------+------------+-------+------------+-------+ | f1 | hex(f1) | f2 | hex(f2) | f3 | +-------+------------+-------+------------+-------+ | NULL | NULL | NULL | NULL | null | | | 0000000000 | | | none | | | 0000000000 | | 0000000000 | five | | | 0000000000 | | 00000000 | four | | | 0000000000 | | 00 | one | | | 0000000000 | | 000000 | three | | | 0000000000 | | 0000 | two | +-------+------------+-------+------------+-------+ 7 rows in set (5.59 sec)