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
Triage: Triaged: D2 (Serious)

[3 Apr 2008 22:11] Ariel S
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 |
+------+
[3 Apr 2008 22:13] Ariel S
The last query should be:

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

Not:

(select c from test) union (select -c from test2);
[4 Apr 2008 5:48] Valeriy Kravchuk
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.
[4 Apr 2013 8:34] Hartmut Holzgraefe
Still verifiable in 5.6.10