Bug #80762 union converts unsigned int to signed

• Submitted: 16 Mar 2016 11:01
• Modified: 17 Mar 2016 8:08
• Reporter: Jan Kahoun
• Status: Verified
• 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)
• 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] MySQL Verification Team

Hello Jan Kahoun,

Thank you for the report and test case.

Thanks,
Umesh