CONCAT() result unexpectedly truncated when LEFT JOIN or GROUP BY is used in query

A MySQL query containing a CONCAT() is truncating the result unexpectedly and returning only 5 of the anticipated 6 characters ('abcd2' instead of abcd21'). A trimmed down version of the actual query follows:

SELECT c.cid, c.club, c.crewno, CONCAT(c.club,c.crewno) crewcode
 FROM `crews` c
 LEFT JOIN `results` r ON r.rno=c.cid
 GROUP BY c.cid;

The above query returns:

54321, 'abcd', 21, 'abcd2'
65432, 'abcd', 1, 'abcd1'

However, if the LEFT JOIN is removed and/or if the GROUP BY is removed then the CONCAT() works as expected and returns:

54321, 'abcd', 21, 'abcd21'
65432, 'abcd', 1, 'abcd1'

I have no idea what the problem is...

Additional information: the field c.club has type VARCHAR(4) and the field c.crewno has type TINYINT(1) UNSIGNED. The outcome is unaffected by whether or not the results table contains rows to join.

A temporary workaround is in place using TRIM(CONCAT(c.club,c.crewno,' ')), which returns the expected values:

54321, 'abcd', 21, 'abcd21'
65432, 'abcd', 1, 'abcd1'

However, rather than live with an ugly workaround, I'd prefer to learn what the underlying problem is and fix it properly!

Edit 1: if a three digit crewno is used then only the first digit is returned and to get all three using my workaround I need to add a double space TRIM(CONCAT(c.club,c.crewno,' ')).

Edit 2: SQL for setting up tables to demonstrate the problem follows. This is not production SQL but the minimum set of fields required to replicate the issue. (Note: when the results table is completely empty the CONCAT() works as expected but as soon as it has data the CONCAT returns the unexpected results)

CREATE TABLE IF NOT EXISTS `crewsmin` (
 `cid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `club` varchar(4) NOT NULL DEFAULT '',
 `crewno` tinyint(1) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`cid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 PACK_KEYS=1;
INSERT INTO `crewsmin` (`cid`, `club`, `crewno`) VALUES
 (12345, 'abcd', 0),
 (12346, 'bcde', 5),
 (12347, 'cdef', 13),
 (12348, 'defg', 42),
 (12349, 'efgh', 107);

CREATE TABLE `resultsmin` (
 `rid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `cid` mediumint(8) unsigned NOT NULL DEFAULT 开发者_如何学Python'0',
 `result` tinyint(3) NOT NULL DEFAULT '0',
 PRIMARY KEY (`rid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 PACK_KEYS=1;
INSERT INTO `resultsmin` (`rid`, `cid`, `result`) VALUES
 (1, 12345, 3),
 (2, 12345, 1);

SELECT c.cid, c.club, c.crewno, CONCAT(c.club,c.crewno) crew
 FROM crewsmin c
 LEFT JOIN resultsmin r ON r.cid=c.cid
 GROUP BY c.cid;

---

It seems that MySQL doesn't always do what you would expect when you use CONCAT with numeric values. You should use CAST on the numeric values:

CONCAT(c.club,CAST(c.crewno AS CHAR))