MySQL Bugs: #49639: NULL union all with unsigned column should return unsigned type

Bug #49639	NULL union all with unsigned column should return unsigned type
Submitted:	12 Dec 2009 10:57	Modified:	12 Dec 2009 13:18
Reporter:	Prafulla Tekawade	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0+, 5.1+	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
NULL union all with unsigned column should return unsigned values

In following example,
In t5, null column should be unsigned because it is being unionized
with unisigned column.

mysql> create table t1(i int unsigned, j int);
Query OK, 0 rows affected (0.02 sec)

mysql> create table t3 as select i from t1 union all select null from t1;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t3;
+-------+------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                   |
+-------+------------------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
  `i` int(11) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+-------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table t5 as select null from t1 union all select i from t1;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t5;
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table                                                                             |
+-------+------------------------------------------------------------------------------------------+
| t5    | CREATE TABLE `t5` (
  `NULL` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+-------+------------------------------------------------------------------------------------------+

How to repeat:
Covered in description.

Suggested fix:
Unionized column should cover all child-columns and decide its attribute based on that.
Mysql seems to be taking just a first branch column and giving its attribute to
unionized column.

--Addition to suggested fix--
and it should ignore literalnulls while doing that.

Verified just as described:

77-52-7-73:5.0 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.89-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  create table t1(i int unsigned, j int);
Query OK, 0 rows affected (0.00 sec)

mysql>  create table t3 as select i from t1 union all select null from t1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `i` int(11) unsigned default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>  create table t5 as select null from t1 union all select i from t1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t5\G
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `NULL` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Our manual, http://dev.mysql.com/doc/refman/5.0/en/union.html, clearly says:

"If the data types of corresponding SELECT columns do not match, the types and lengths of the columns in the UNION result take into account the values retrieved by all of the SELECT statements."

While we see that this is NOT the case and order of SELECTs in UNION matters. So, this is a bug.

Verified with recent 5.0.89 and 5.1.43 from bzr.

NOT FIXED in 2016 !!!
(please...)

Note this bug only "reveal" when the value is bigger than the signed max value. For exemple for a TINYINT UNSIGNED, when the value is greater or equal than 255

Actually, the only workaround is to do :

SELECT CAST(NULL AS UNSIGNED) UNION ALL SELECT myUnsignedColumn FROM table