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)
