Bug #30886 Union returns wrong column type size
Submitted: 7 Sep 2007 8:16 Modified: 8 Feb 2018 22:08
Reporter: Ajasja Ljubetič Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.0.45, 4.1, 5.0, 5.1, 5.2 BK OS:Any (Linux, Windows XP SP2)
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: column type, UNION
Triage: Triaged: D4 (Minor) / R3 (Medium) / E4 (High)

[7 Sep 2007 8:16] Ajasja Ljubetič
Description:
When selecting a union of two tables, the result column type’s size does not mach the underlying tables.

For example the union of two tinyint(1) fields will return tinyint(4) instead of tinyint(1). 
The union of two int(10) unsigned will return int(11) unsigned.

Tested with 5.0.41 and 5.0.45 on Win XP SP2.

How to repeat:
DROP TABLE IF EXISTS `t1`;
CREATE TABLE  `t1` (
  `ID` int(10) unsigned NOT NULL auto_increment primary key,
  `Boolean` tinyint(1) NOT NULL default '0'
)ENGINE=InnoDB;

DROP TABLE IF EXISTS `t2`;
CREATE TABLE  `t2` (
  `ID` int(10) unsigned NOT NULL auto_increment primary key,
  `Boolean` tinyint(1) NOT NULL default '0'
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `t3`;
CREATE TABLE t3
SELECT * FROM t1
UNION
SELECT * FROM t2;

SHOW CREATE TABLE t3;

###################
RETURNS:
CREATE TABLE `t3` (
  `ID` int(11) unsigned NOT NULL default '0',
  `Boolean` tinyint(4) NOT NULL default '0'
) ENGINE=InnoDB 

Suggested fix:
None.
[7 Sep 2007 9:18] Sveta Smirnova
Thank you for the report.

Verified as described.
[8 Feb 2018 22:08] Roy Lyseng
Posted by developer:
 
We consider this not a bug currently, since we don't see good reasons to maintain display sizes across a UNION.
The maximum display size should be just fine. If not, it is possible to create the target table with specific display size.