Bug #80762 union converts unsigned int to signed
Submitted: 16 Mar 2016 11:01 Modified: 17 Mar 2016 8:08
Reporter: Jan Kahoun Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.6.24-72.2-log, 5.5.48, 5.6.29, 5.7.11 OS:Ubuntu (14.04.4)
Assigned to: CPU Architecture:Any

[16 Mar 2016 11:01] Jan Kahoun
Description:
In a UNION SELECT, unsigned int columns can be converted into signed values. It depends on SELECTs order in UNION.

How to repeat:
DROP TABLE IF EXISTS `TestTable`;

CREATE TABLE `TestTable` (                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
	`typeId` int unsigned NOT NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
) ENGINE=InnoDB;

INSERT INTO `TestTable` SET `typeId` = 2148292247;

# returns wrong signed value -2146675049
SELECT * 
FROM 
(
	(
		SELECT NULL AS `typeId`
		  FROM `TestTable`
	 )
	 
	 UNION
	 
	 (
		SELECT `typeId`
	    FROM `TestTable`
	 )
) AS tmp;

# returns right unsigned value 2148292247
SELECT * 
FROM 
(
	 (
		SELECT `typeId`
	    FROM `TestTable`
	 )
	 
	 UNION
	 
	 (
		SELECT NULL AS `typeId`
		  FROM `TestTable`
	 )
) AS tmp;
[17 Mar 2016 8:08] Umesh Shastry
Hello Jan Kahoun,

Thank you for the report and test case.

Thanks,
Umesh