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

[23 Dec 2005 12:31] Paolo Alessandroni
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
[23 Dec 2005 12:55] Valeriy Kravchuk
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.
[6 Jun 2006 21:11] Bugs System
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
[14 Jun 2006 11:22] Alexander Barkov
The patch is ok to push.
[19 Jun 2006 0:15] Evgeny Potemkin
Fixed in 4.1.21, 5.0.23, 5.1.12
[19 Jun 2006 0:20] Evgeny Potemkin
Sorry, fixed in 5.0 and 5.1 only
[20 Jun 2006 10:51] Evgeny Potemkin
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.
[23 Jun 2006 5:01] Paul Dubois
Noted in 5.0.23, 5.1.12 changelogs.