Skip Menu |
 

This queue is for tickets about the DBD-mysql CPAN distribution.

Report information
The Basics
Id: 25389
Status: resolved
Priority: 0/
Queue: DBD-mysql

People
Owner: CAPTTOFU [...] cpan.org
Requestors: jdiepen [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in:
  • 4.001
  • 4.002
  • 4.003
Fixed in: (no value)



Subject: selecting on utf8_bin unique columns with case-differing values returns too few results.
Download (untitled) / with headers
text/plain 1.2k
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 text
mysql> 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
Download utf8_bin.pl
text/x-perl 1.1k
#!/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); } }
Download (untitled) / with headers
text/plain 267b
Even with the patch you sent, I still only get 1 row back, yet in the sql log of the database, I see: select name from testutf8unique where name = 'AA' OR name = 'Aa' OR name = 'aa' OR name = 'aA' Which when run in the client gives 4 rows. Still looking into this.
From: JDIEPEN [...] cpan.org
Download (untitled) / with headers
text/plain 704b
On Tue Mar 20 10:00:40 2007, CAPTTOFU wrote: Show quoted text
> Even with the patch you sent,
I didn't sent a patch for this bug - just a test script, since I couldn't figure out what was going on either :-) There may be some confusion with bug #24738 - which I reported at the same time, with a patch. As far as I know this bug isn't related to that one. Show quoted text
> I still only get 1 row back, yet in the > sql log of the database, I see: > > select name from testutf8unique where name = 'AA' OR name = 'Aa' OR > name = 'aa' OR name = 'aA' > > Which when run in the client gives 4 rows. Still looking into this.
I'm wondering if the bug isn't in MySQL itself, somehow. Couldn't find it in the mysql bug database, anyway.
Download (untitled) / with headers
text/plain 747b
Hi, On Sun Mar 25 09:07:20 2007, JDIEPEN wrote: Show quoted text
> > I still only get 1 row back, yet in the > > sql log of the database, I see: > > > > select name from testutf8unique where name = 'AA' OR name = 'Aa' OR > > name = 'aa' OR name = 'aA' > > > > Which when run in the client gives 4 rows. Still looking into this.
> > I'm wondering if the bug isn't in MySQL itself, somehow. Couldn't find > it in the mysql bug database, anyway.
I added a test case for this bug based on the code provided, and with current MySQL it is passing. So the issue seems resolved. If it would be still an issue (on a specific mysql for instance) please let me know. https://github.com/perl5-dbi/DBD-mysql/commit/aabdf28834ecb3b49dbeb20fcb8e11d20d5023f3 -- Michiel
I do see test failures from this test here.  Running 'make test' for DBD-mysql-4.027 on a recent Ubuntu (12.04) trying to run the tests against a pretty old server; 3.23.58:
#   Failed test 'select name from `dbd-mysql-utf8-unique` where name = 'AA' OR name = 'Aa' OR name = 'aa' OR name = 'aA''
#   at t/rt25389-bin-case.t line 55.
#          got: '1'
#     expected: '4'
# Looks like you failed 1 test of 8.
t/rt25389-bin-case.t ................. 
Dubious, test returned 1 (wstat 256, 0x100)
Failed 1/8 subtests 
 
On Tue May 27 16:51:04 2014, GAAS wrote:
Show quoted text
> against a pretty old server; 3.23.58:

I was wrong about the server version.  The server used during the test is actually 5.0.51b


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.