MySQL Bugs: #9145: CONCAT() returns unexpected results joining a fields and fixed strings

Bug #9145	CONCAT() returns unexpected results joining a fields and fixed strings
Submitted:	12 Mar 2005 17:12	Modified:	7 Jul 2005 11:10
Reporter:	Gary Ott	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.10a-nt	OS:	Windows (WinXP SP1 (sorry, not my choice))
Assigned to:	Petr Chardin	CPU Architecture:	Any

Description:
I get the same results using MyODBC 3.51 across a LAN and in the command line client on localhost.

I am attempting to join a customers title, forename and surname together with a space inbetween.

CONCAT() returns unexpected results when joining a combination of fields and fixed strings. The bug is not present when using CONCAT(Title, Customers, Surname).

I should get:

Mr I Taylor
 Allan Wilson
 A Jones
 Thomas Tennant
Mrs Z Sawyer
etc..
..
.

The results I get are below.

mysql> Select CONCAT(Title, " ", Forename, " ", Surname) from customers;

+--------------------------------------------------------------------------+
| CONCAT(Customers.Title, " ", Customers.Forename, " ", Customers.Surname) |
+--------------------------------------------------------------------------+
| Mr I Taylor                                                              |
|  Allan Wilson                                                            |
|  Allan A Jones                                                           |
|  Allan A JonesThomas Tennant                                             |
| Mrs Allan A JonesZ Sawyer                                                |
etc...
..
.

How to repeat:

CREATE TABLE `customers` (
  `CustomerID` int(11) NOT NULL auto_increment,
  `Alert` tinyint(1) default '0',
  `DefaultAddressID` int(11) default '0',
  `Title` varchar(4) default '',
  `Forename` varchar(40) default '',
  `Surname` varchar(40) default '',
  `Memo` varchar(255) default NULL,
  `EmailAddr` varchar(50) default NULL,
  `PhoneNumber` varchar(40) default NULL,
  PRIMARY KEY  (`CustomerID`),
  KEY `CustomerID` (`CustomerID`),
  KEY `DefaultAddressID` (`DefaultAddressID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `customers` (`CustomerID`,`Alert`,`DefaultAddressID`,`Title`,`Forename`,`Surname`,`Memo`,`EmailAddr`,`PhoneNumber`) VALUES 
 (259,0,156,'Mr','I','Taylor',NULL,'a@a.a','01234 123456'),
 (260,0,157,'','Allan','Wilson',NULL,'a@a.a','01234 123456'),
 (261,0,158,'','A','Jones',NULL,'a@a.a','01234 123456'),
 (262,0,159,'','Thomas','Tennant',NULL,'a@a.a','01234 123456'),
 (263,0,160,'Mrs','Z','Sawyer',NULL,'a@a.a','01234 123456'),
 (264,0,161,'','J M','Terry-short',NULL,'a@a.a','01234 123456'),
 (265,0,162,'','H','Ferreira',NULL,'a@a.a','01234 123456'),
 (266,0,163,'','Brett','Croucher',NULL,'a@a.a','01234 123456'),
 (267,0,164,'','David','Coates',NULL,'a@a.a','01234 123456'),
 (268,0,165,'Mr','P J','Dutton',NULL,'a@a.a','01234 123456'),
 (269,0,166,'','Ferat','Kivirci',NULL,'a@a.a','01234 123456'),
 (270,0,167,'','H','Ferreira',NULL,'a@a.a','01234 123456');

Select CONCAT(Customers.Title, " ", Customers.Forename, " ", Customers.Surname) from customers;

Suggested fix:
Sorry, no clue.

Verified using 4.1.11 from bk

mysql> Select CONCAT(customers.Title, " ", customers.Forename, " ", customers.Surname) from customers;
+--------------------------------------------------------------------------+
| CONCAT(customers.Title, " ", customers.Forename, " ", customers.Surname) |
+--------------------------------------------------------------------------+
| Mr I Taylor                                                              |
|  Allan Wilson                                                            |
|  Allan A Jones                                                           |
|  Allan A JonesThomas Tennant                                             |
| Mrs Allan A JonesZ Sawyer                                                |
|  Allan A JonesJ M Terry-short                                            |
|  Allan A JonesH Ferreira                                                 |
|  Allan A JonesH FerreiraBrett Croucher                                   |
|  Allan A JonesH FerreiraBrett David Coates                               |
| Mr Allan A JonesH FerreiraBrett P J Dutton                               |
|  Allan A JonesH FerreiraBrett Ferat Kivirci                              |
|  Allan A JonesH FerreiraBrett H Ferreira                                 |
+--------------------------------------------------------------------------+
12 rows in set (0.01 sec)

mysql> select CONCAT(Title, Forename, Surname) from customers;          
+----------------------------------+
| CONCAT(Title, Forename, Surname) |
+----------------------------------+
| MrITaylor                        |
| AllanWilson                      |
| AJones                           |
| ThomasTennant                    |
| MrsZSawyer                       |
| J MTerry-short                   |
| HFerreira                        |
| BrettCroucher                    |
| DavidCoates                      |
| MrP JDutton                      |
| FeratKivirci                     |
| HFerreira                        |
+----------------------------------+
12 rows in set (0.00 sec)

Attempted to verify the bug with 1) 4.1.12a from mysql.com (windows binary) 2) 4.1.13 from bk (suse linux) 3) 4.1.13 from bk (WinXP). No success.

looks like related to #7874