List:Falcon Storage Engine« Previous MessageNext Message »
From:Kevin Lewis Date:February 18 2009 6:29pm
Subject:Re: Patch for bug#42208
View as plain text  
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)
Thread
Patch for bug#42208Lars-Erik Bjørk16 Feb
  • RE: Patch for bug#42208Vladislav Vaintroub16 Feb
    • RE: Patch for bug#42208Vladislav Vaintroub16 Feb
      • Re: Patch for bug#42208Jim Starkey16 Feb
        • RE: Patch for bug#42208Vladislav Vaintroub16 Feb
    • Re: Patch for bug#42208Jim Starkey16 Feb
      • RE: Patch for bug#42208Vladislav Vaintroub16 Feb
      • Re: Patch for bug#42208Lars-Erik Bjørk17 Feb
        • Re: Patch for bug#42208Kevin Lewis17 Feb
          • Re: Patch for bug#42208Ann W. Harrison18 Feb
            • Re: Patch for bug#42208Ann W. Harrison18 Feb
              • Re: Patch for bug#42208Ann W. Harrison18 Feb
                • Re: Patch for bug#42208Kevin Lewis18 Feb
                  • Re: Patch for bug#42208Ann W. Harrison18 Feb
                    • Re: Patch for bug#42208Kevin Lewis18 Feb
          • RE: Patch for bug#42208Vladislav Vaintroub18 Feb
            • RE: Patch for bug#42208Vladislav Vaintroub18 Feb
              • Re: Patch for bug#42208Kevin Lewis18 Feb
                • RE: Patch for bug#42208Vladislav Vaintroub18 Feb
                  • RE: Patch for bug#42208Vladislav Vaintroub18 Feb
              • Re: Patch for bug#42208Jim Starkey18 Feb
                • RE: Patch for bug#42208Vladislav Vaintroub18 Feb
                  • Re: Patch for bug#42208Jim Starkey18 Feb
                    • RE: Patch for bug#42208Vladislav Vaintroub18 Feb
                      • RE: Patch for bug#42208Vladislav Vaintroub18 Feb
            • Re: Patch for bug#42208Ann W. Harrison18 Feb
              • RE: Patch for bug#42208Vladislav Vaintroub18 Feb
                • Re: Patch for bug#42208Jim Starkey18 Feb
                  • Re: Patch for bug#42208Ann W. Harrison18 Feb
                    • Re: Patch for bug#42208Ann W. Harrison18 Feb
                • Re: Patch for bug#42208Ann W. Harrison18 Feb
                  • RE: Patch for bug#42208Vladislav Vaintroub18 Feb
                    • Re: Patch for bug#42208Ann W. Harrison18 Feb
                    • Re: Patch for bug#42208Kevin Lewis18 Feb
                      • Re: Patch for bug#42208Ann W. Harrison18 Feb
      • Re: Patch for bug#42208MARK CALLAGHAN17 Feb
  • Re: Patch for bug#42208Jim Starkey16 Feb