| Subject: | selecting on utf8_bin unique columns with case-differing values returns too few results. |
Say we have a table with a uniqe utf8_bin column:
Show quoted text
mysql> show full columns from testutf8unique;
+-------+------------+-----------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra |
Privileges | Comment |
+-------+------------+-----------+------+-----+---------+-------+---------------------------------+---------+
| name | varchar(8) | utf8_bin | YES | UNI | NULL | |
select,insert,update,references | |
+-------+------------+-----------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00 sec)
and values:
Show quoted textmysql> select * from testutf8unique;
+------+
| name |
+------+
| AA |
| Aa |
| aA |
| aa |
+------+
then this works in the mysql client tool:
select * from testutf8unique where name = "AA" or name = "Aa" or name =
"aA" or name = "aa";
+------+
| name |
+------+
| AA |
| Aa |
| aA |
| aa |
+------+
4 rows in set (0.00 sec)
But using DBD::mysql it only returns one result: the "AA" column.
The problem does not manifest itself for a latin1_bin column or when the
column isn't unique.
See attached test script.
Cheers,
Joost Diepenmaat
Zeekat Softwareontwikkeling.
| Subject: | utf8_bin.pl |
#!/usr/bin/perl -w
use DBI;
use strict;
use Test::More tests => 8;
my $dbh = DBI->connect("DBI:mysql:database=test",'','',{"mysql_enable_utf8"=>1}) or die $DBI::errstr;
my ($sth,$i);
my @test = qw(AA Aa aa aA);
for my $charset(qw(latin1 utf8)){
for my $unique("","unique"){
#for my $charset(qw(utf8)){
# for my $unique("unique"){
my $table = "test$charset$unique";
$dbh->do("drop table if exists $table ");
# print "#" x 80,"\nCHARSET: $charset; TABLE: $table; \U$unique\n";
my $create = "create table $table (name varchar(8) character set $charset collate $ {charset}_bin $unique)";
# print $create,"\n";
$dbh->do($create) or die $DBI::errstr;
for(@test){
$dbh->do("insert into $table values ('$_')");
}
my $q1 = "select name from $table";
$sth = $dbh->prepare($q1);
$sth->execute;
$i = 0;
while(my @row = $sth->fetchrow){
$i++;
}
is($i, scalar @test,$q1);
$sth->finish;
my $q2 = "select name from $table where ".join(" OR ",map{"name = '$_'"}@test);
$sth = $dbh->prepare($q2);
$sth->execute;
$i = 0;
while(my @row = $sth->fetchrow){
$i++;
}
is($i, scalar @test,$q2);
}
}