MySQL Bugs: #60685: CRC32(CONCAT_WS()) Returns wrong values

Bug #60685	CRC32(CONCAT_WS()) Returns wrong values
Submitted:	29 Mar 2011 9:43	Modified:	3 Jun 2011 8:52
Reporter:	Daniël van Eeden	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.5.8	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
The CRC32() function on a value from CONCAT_WS() returns different values that Perl and previous versions.

host1 = 5.0.77
host2 = 5.8.8

# ./test2.pl 
====== Server: host1 ======
NAME1:      Australië
NAME1:      1738757367 (CRC32 Perl)
NAME1:      1738757367 (CRC32 MySQL)

NAME2:      Test
NAME2:      2018365746 (CRC32 Perl)
NAME2:      2018365746 (CRC32 MySQL)

CONCAT1:    Australië#Test
CONCAT1:    1454614022 (CRC32 Perl)
CONCAT1:    1454614022 (CRC32 MySQL)

CONCAT2:    Australië#Test
CONCAT2:    1454614022 (CRC32 Perl)
CONCAT2:    1454614022 (CRC32 MySQL)

====== Server: host2 ======
NAME1:      Australië
NAME1:      1738757367 (CRC32 Perl)
NAME1:      1738757367 (CRC32 MySQL)

NAME2:      Test
NAME2:      2018365746 (CRC32 Perl)
NAME2:      2018365746 (CRC32 MySQL)

CONCAT1:    Australië#Test
CONCAT1:    1454614022 (CRC32 Perl)
CONCAT1:    1454614022 (CRC32 MySQL)

CONCAT2:    Australië#Test
CONCAT2:    1454614022 (CRC32 Perl)
CONCAT2:    3883857570 (CRC32 MySQL)

How to repeat:
#!/usr/bin/perl
use DBI;
use strict;
use warnings;
use String::CRC32;

my @servers = ("host1","host2");
foreach my $server (@servers) {

  my $dbh = DBI->connect('DBI:mysql:host=' . $server . ';mysql', 'user', 'pass'
	           ) || die "Could not connect to database: $DBI::errstr";

  $dbh->do('CREATE TEMPORARY TABLE testc (`FIELD1` VARCHAR(255),`FIELD2` VARCHAR(255))');
  $dbh->do("INSERT INTO testc VALUES('Australië','Test')"); 
  my $sth = $dbh->prepare("SELECT 
				'Australië' AS NAME1,
				CRC32('Australië') AS NAME1_CRC,
				'Test' AS NAME2,
				CRC32('Test') AS NAME2_CRC,
				CONCAT_WS('#','Australië','Test') AS CONCAT1,
				CRC32(CONCAT_WS('#','Australië','Test')) AS CONCAT1_CRC,
				CONCAT_WS('#',FIELD1,FIELD2) AS CONCAT2,
				CRC32(CONCAT_WS('#',FIELD1,FIELD2)) AS CONCAT2_CRC
				FROM testc");

  $sth->execute();
    while (my $ref = $sth->fetchrow_hashref()) {
      print "====== Server: $server ======\n";
      print "NAME1:      $ref->{'NAME1'}\n";
      print "NAME1:      " . crc32($ref->{'NAME1'}) . " (CRC32 Perl)\n";
      print "NAME1:      $ref->{'NAME1_CRC'} (CRC32 MySQL)\n\n";
      print "NAME2:      $ref->{'NAME2'}\n";
      print "NAME2:      " . crc32($ref->{'NAME2'}) . " (CRC32 Perl)\n";
      print "NAME2:      $ref->{'NAME2_CRC'} (CRC32 MySQL)\n\n";
      print "CONCAT1:    $ref->{'CONCAT1'}\n";
      print "CONCAT1:    " . crc32($ref->{'CONCAT1'}) . " (CRC32 Perl)\n";
      print "CONCAT1:    $ref->{'CONCAT1_CRC'} (CRC32 MySQL)\n\n";
      print "CONCAT2:    $ref->{'CONCAT2'}\n";
      print "CONCAT2:    " . crc32($ref->{'CONCAT2'}) . " (CRC32 Perl)\n";
      print "CONCAT2:    $ref->{'CONCAT2_CRC'} (CRC32 MySQL)\n\n";
    }
  $sth->finish();
 
  $dbh->disconnect();
} 

Suggested fix:
Unknown

Changed Severity to S1

Tested with perl-DBD-MySQL-3.0007-2.el5 and Perl 5.8.8 on RHEL 5.3 and 5.6 both with an en_US.UTF-8 locale.

Thank you for the report.

What returns same test if run from command line? Which MySQL encoding do you?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

This is probably due to a different server charset.

I will change the status to closed as this probably isn't a bug.

Changed status to Not a Bug.