[debian-mysql] Bug#498818: mysql-server: Problem with comparison of BINARY primary key columns

[A Meaden]

Package: mysql-server
Version: 5.0.32-7etch5
Severity: normal

Using BINARY data type for (at least) primary key columns is subject to
data comparison failures given certain data. This was noticed when doing
SELECT queries where the primary key is of BINARY type, and that column
is in the WHERE cause. Below is a test I did to replicate the problem.

Creating a test table, with BINARY primary key:

mysql> desc test2;
+-----------+------------+------+-----+---------+-------+
| Field     | Type       | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| binaryKey | binary(16) | NO   | PRI |         |       | 
| integer   | int(11)    | NO   |     |         |       | 
+-----------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Program to insert data:

#!/usr/bin/perl

use warnings;
use strict;
use DBI();

my $string1 = "be473185fcc2a9ccf03fb9fa71f98120";
my $bin1 = pack("H*", $string1);
my $string2 = "ebc7a1ee235075c7406fadbb932a30ce";
my $bin2 = pack("H*", $string2);

my $dbh = DBI->connect("DBI:mysql:database=test;host=localhost", "test", "test123");
my $sth = $dbh->prepare("INSERT INTO `test2`(`binaryKey`, `integer`) VALUES(?, ?)");
$sth->execute($bin1, 1); 
$sth->execute($bin2, 2); 

$sth->finish();
$dbh->disconnect();

Testing:
Comparison fails on the first row of data. Of many hundreds of binary
strings this is the only one I have found to fail...

mysql> select * from test2;
+------------------+---------+
| binaryKey        | integer |
+------------------+---------+
| ¾G1ü©Ìð?¹úqù  |       1 | 
| ëǡî#PuÇ@o­»*0Î |       2 | 
+------------------+---------+
2 rows in set (0.00 sec)

mysql> select * from test2 where binaryKey = (select binaryKey from test2 where `integer` = 1);
Empty set (0.00 sec)

# Clearly a value should equal itself! The problem is not restricted to
# the "mysql" command-line program (I get the same behaviour using
# placeholders in perl).

mysql> select * from test2 where binaryKey = (select binaryKey from test2 where `integer` = 2);
+------------------+---------+
| binaryKey        | integer |
+------------------+---------+
| ëǡî#PuÇ@o­»*0Î |       2 | 
+------------------+---------+
1 row in set (0.00 sec)



-- System Information:
Debian Release: 4.0
  APT prefers stable
  APT policy: (500, 'stable')
Architecture: amd64 (x86_64)
Shell:  /bin/sh linked to /bin/bash
Kernel: Linux 2.6.25.6
Locale: LANG=en_GB.UTF-8, LC_CTYPE=en_GB.UTF-8 (charmap=UTF-8)

Versions of packages mysql-server depends on:
ii  mysql-server-5.0           5.0.32-7etch5 mysql database server binaries

mysql-server recommends no packages.

-- no debconf information