MySQL Forums
Forum List  »  Quality Assurance

CONCAT() unexpectedly truncating result in 5.5.8 when LEFT JOIN and GROUP BY present
Posted by: Andrew Carr-Smith
Date: September 26, 2011 02:10AM

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'

Options: ReplyQuote


Subject
Views
Written By
Posted
CONCAT() unexpectedly truncating result in 5.5.8 when LEFT JOIN and GROUP BY present
4103
September 26, 2011 02:10AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.