MySQL Bugs: #15962: union truncate column composed by concat()

Bug #15962	union truncate column composed by concat()
Submitted:	23 Dec 2005 12:31	Modified:	23 Jun 2006 5:01
Reporter:	Paolo Alessandroni	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.19-BK, 5.0.17	OS:	Linux (Linux, WinXP)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
A union with column composed by concat() are truncated.

The single select return correct value.

Grazie.

How to repeat:
Using this data:

CREATE TABLE `_orecop` (
  `SOCIETA` varchar(10) NOT NULL default '',
  `ID_ANNO` int(10) NOT NULL default '0',
  `ID_MESE` int(10) NOT NULL default '0',
  `MATRICOLA` varchar(10) NOT NULL default '',
  `OGG_DEST` varchar(255) default NULL,
  `TOT_ORE` int(10) default NULL,
  `ID_WBS` varchar(255) default NULL,
  `TIPO_DEST` varchar(255) default NULL,
  `ID_CDC` varchar(255) default ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `_orecop` (`SOCIETA`,`ID_ANNO`,`ID_MESE`,`MATRICOLA`,`OGG_DEST`,`TOT_ORE`,`ID_WBS`,`TIPO_DEST`,`ID_CDC`) VALUES 
 ('DIST',2005,11,'034023','000002070391',2825,'6040M','ODM','AD050'),
 ('DIST',2005,11,'034023','000002070893',7600,'2088M.36','ODM','AD050'),
 ('DIST',2005,11,'034023','000002071297',1550,'6090M','ODM','AD050'),
 ('DIST',2005,11,'034023','5230K',775,'5230K','WBS','AD050');

The query:
SELECT concat(rpad(SOCIETA,4," "),
              lpad(MATRICOLA,6,"0"),
              '7',
              rpad(ID_ANNO*100+ID_MESE,6," "),
              rpad("ORE_COP",9," "),
              rpad(trim(LEADING "0" FROM OGG_DEST),24," "),
              lpad(sum(TOT_ORE),10,"0")
             ) Orecop
  FROM datisap._orecop o
  WHERE SOCIETA != "" and
        MATRICOLA != ""
  GROUP BY SOCIETA, MATRICOLA,ID_ANNO,ID_MESE, OGG_DEST

return :
DIST0340237200511ORE_COP  2070391                 0000002825
DIST0340237200511ORE_COP  2070893                 0000007600
DIST0340237200511ORE_COP  2071297                 0000001550
DIST0340237200511ORE_COP  5230K                   0000000775

The union:
SELECT concat(rpad(SOCIETA,4," "),
              lpad(MATRICOLA,6,"0"),
              '7',
              rpad(ID_ANNO*100+ID_MESE,6," "),
              rpad("ORE_COP",9," "),
              rpad(trim(LEADING "0" FROM OGG_DEST),24," "),
              lpad(sum(TOT_ORE),10,"0")
             ) Orecop
  FROM datisap._orecop o
  WHERE SOCIETA != "" and
        MATRICOLA != ""
  GROUP BY SOCIETA, MATRICOLA,ID_ANNO,ID_MESE, OGG_DEST
union
SELECT concat(rpad(SOCIETA,4," "),
              lpad(MATRICOLA,6,"0"),
              '5',
              rpad(ID_ANNO*100+ID_MESE,6," "),
              rpad("ORE_COP",9," "),
              rpad("3-ATTRIB",24," "),
              lpad(sum(TOT_ORE),10,"0")
             ) Orecop
    FROM datisap._orecop o
    WHERE SOCIETA != "" and
          MATRICOLA != ""
    GROUP BY SOCIETA, MATRICOLA,ID_ANNO,ID_MESE
order by Orecop;

return:
DIST0340235200511ORE_COP  3-ATTRIB               
DIST0340237200511ORE_COP  2070391                
DIST0340237200511ORE_COP  2070893                
DIST0340237200511ORE_COP  2071297                
DIST0340237200511ORE_COP  5230K

Thank you for a bug report. Verified just as described on 5.0.19-BK (ChangeSet@1.1999, 2005-12-21 18:50:06+01:00) on Linux. UNION ALL gives the same (wrong) result.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7335

The patch is ok to push.

Fixed in 4.1.21, 5.0.23, 5.1.12

Sorry, fixed in 5.0 and 5.1 only

To calculate its max_length the CONCAT() function is simply sums max_lengths
of its arguments but when the collation of an argument differs from the 
collation of the CONCAT() max_length will be wrong. This may lead to a data
truncation when a tmp table is used, in UNIONS for example.

Noted in 5.0.23, 5.1.12 changelogs.