Databases
StackOverflow2013
Postsdbo
POCONCAT() result unexpectedly truncated when LEFT JOIN or GROUP BY is used in query
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCONCAT() result unexpectedly truncated when LEFT JOIN or GROUP BY is used in query
    text
    copied!<p>A MySQL query containing a <code>CONCAT()</code> 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:</p> <pre><code>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; </code></pre> <p>The above query returns:</p> <pre><code>54321, 'abcd', 21, 'abcd2' 65432, 'abcd', 1, 'abcd1' </code></pre> <p>However, if the <code>LEFT JOIN</code> is removed and/or if the <code>GROUP BY</code> is removed then the <code>CONCAT()</code> works as expected and returns:</p> <pre><code>54321, 'abcd', 21, 'abcd21' 65432, 'abcd', 1, 'abcd1' </code></pre> <p>I have no idea what the problem is...</p> <p>Additional information: the field <code>c.club</code> has type <code>VARCHAR(4)</code> and the field <code>c.crewno</code> has type <code>TINYINT(1) UNSIGNED</code>. The outcome is unaffected by whether or not the results table contains rows to join.</p> <p>A temporary workaround is in place using <code>TRIM(CONCAT(c.club,c.crewno,' '))</code>, which returns the expected values:</p> <pre><code>54321, 'abcd', 21, 'abcd21' 65432, 'abcd', 1, 'abcd1' </code></pre> <p>However, rather than live with an ugly workaround, I'd prefer to learn what the underlying problem is and fix it properly!</p> <p>Edit 1: if a three digit <code>crewno</code> is used then only the first digit is returned and to get all three using my workaround I need to add a double space <code>TRIM(CONCAT(c.club,c.crewno,' '))</code>.</p> <p>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 <code>CONCAT()</code> works as expected but as soon as it has data the <code>CONCAT</code> returns the unexpected results)</p> <pre><code>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; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload