MySQL Bugs: #30886: Union returns wrong column type size

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

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.

Thank you for the report.

Verified as described.

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.