| Subject: | is_utf8 is not set when CONCAT() is used in SQL query |
It seems that this bug was not reported before.
The problem occurs when columns are combined with
CONCAT() function, then DBD::mysql doesn't set
is_utf8 flag on the output column.
See the script attached.
Many thanks in advance!
Observed output:
===============
UTF-8 is broken if we CONCAT() two fields:
name = 1135 Düsseldorf; is_utf8 =
name = 1160 ÐоÑква; is_utf8 =
UTF-8 is good if we just take `name` field:
name = Düsseldorf; is_utf8 = 1
name = Москва; is_utf8 = 1
Expected output:
===============
UTF-8 isn't broken even if we CONCAT() two fields:
name = 1135 Düsseldorf; is_utf8 = 1
name = 1160 Москва; is_utf8 = 1
UTF-8 is good if we just take `name` field:
name = Düsseldorf; is_utf8 = 1
name = Москва; is_utf8 = 1
Software versions:
=================
MySQL version = 5.1.32-community
$DBI::VERSION = 1.611
$DBD::mysql::VERSION = 4.013
| Subject: | concat_utf8.pl |
#! /usr/bin/perl -w
use utf8;
use strict;
use DBI;
binmode STDOUT, ":utf8";
binmode STDERR, ":utf8";
my $dbh = DBI->connect("DBI:mysql:database=test;", 'user', 'password',
{RaiseError => 1, AutoCommit => 0, mysql_auto_reconnect => 1,
mysql_enable_utf8 => 1,
on_connect_do => [ 'set names utf8' ],
});
$dbh or die $!;
$SIG{__DIE__} = sub {$dbh->disconnect};
=pod Fill some data in a two-column table:
DROP TABLE IF EXISTS `cities`;
CREATE TABLE `cities` (
`year` int(11) DEFAULT NULL,
`name` char(80) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `cities` (`year`, `name`)
VALUES (1135, 'Düsseldorf'), (1160, 'ÐоÑква');
=cut
printf "\nUTF-8 is broken if we CONCAT() two fields:\n";
my $sql = "SELECT CONCAT(`year`, ' ', `name`) AS 'name' FROM `cities`";
my $sth = $dbh->prepare($sql);
$sth->execute();
while (my $hsh = $sth->fetchrow_hashref) {# trouble is here -->
printf "name = %s; is_utf8 = %s\n",
$hsh->{name}, utf8::is_utf8($hsh->{name});
}
print "\nUTF-8 is good if we just take `name` field:\n";
$sql = "SELECT `name` AS 'name' FROM `cities`";
$sth = $dbh->prepare($sql);
$sth->execute();
while (my $hsh = $sth->fetchrow_hashref) {# now it's OK -->
printf "name = %s; is_utf8 = %s\n",
$hsh->{name}, utf8::is_utf8($hsh->{name});
}
print "\nSoftware versions:\n";
$sql = 'SELECT @@version';
$sth = $dbh->prepare($sql);
$sth->execute();
printf "MySQL version = %s\n\$DBI::VERSION = %s\n\$DBD::mysql::VERSION = %s\n",
$sth->fetchrow_array, $DBI::VERSION, $DBD::mysql::VERSION;