CONCAT() unexpectedly truncating result in 5.5.8 when LEFT JOIN and GROUP BY present
I recently noticed one of my queries was unexpectedly truncating the results of a CONCAT() operation. The CONCAT() takes a VARCHAR(4) and TINYINT(1) and should should return an alphanumeric of 4 letters followed by a number from 0-128. Instead it was only returning the first digit of the number e.g. CONCAT('abcd',12) returns 'abcd1'. Using CAST on the TINYINT does NOT resolve the problem. A workaround that does fix it is to use TRIM(CONCAT({VARCHAR(4)},{TINYINT(1)},' '))
The query uses both LEFT JOIN and GROUP BY. If either of those are removed the query functions as expected. The query also functions as expected if the table being joined is completely empty.
It turns out the problem may be specific to MySQL version 5.5.8, as the CONCAT works fine with older versions (5.0.27 & 5.1.56), or it might be something I have done but am unaware of.
Either way I would like to know what causes the unexpected results!
SQL for replicating problem:
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 '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;
Expected results:
12345, 'abcd', 0, 'abcd0'
12346, 'bcde', 5, 'bcde5'
12347, 'cdef', 13, 'cdef13'
12348, 'defg', 42, 'defg42'
12349, 'efgh', 107, 'efgh107'
Results obtained:
12345, 'abcd', 0, 'abcd0'
12346, 'bcde', 5, 'bcde5'
12347, 'cdef', 13, 'cdef1'
12348, 'defg', 42, 'defg4'
12349, 'efgh', 107, 'efgh1'