MySQL Bugs: #35818: union not checking for unsigned vs signed, returns 0's

Bug #35818	union not checking for unsigned vs signed, returns 0's
Submitted:	3 Apr 2008 22:11	Modified:	4 Apr 2008 5:48
Reporter:	Ariel S	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	Ver 14.12 Distrib 5.0.32, 5.0.56	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	signed, UNION, UNSIGNED

Description:
Doing a union of an unsigned column and a signed one does not produce a signed result. This changes all negative values to 0 - this can be pretty serious.

How to repeat:
create table test (c int unsigned);
insert into test values (1);
(select c from test) union (select -c from test);

This outputs:

+------+
| c    |
+------+
|    1 |
|    0 |
+------+

(select -c from test) union (select c from test);

Works:

+------+
| -c   |
+------+
|   -1 |
|    1 |
+------+

create table test2 (c int signed);
insert into test2 values (-1);

Even though test2 is signed:

(select c from test) union (select -c from test2);

+------+
| c    |
+------+
|    1 |
|    0 |
+------+

The last query should be:

(select c from test) union (select c from test2);

Not:

(select c from test) union (select -c from test2);

Thank you for a problem report. According to the manual, http://dev.mysql.com/doc/refman/5.0/en/union.html:

"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."

The above, "taking into account types of the columns ... retrieved by all of the SELECT statements", should be applied to UNSIGNED flag also.

Still verifiable in 5.6.10