MySQL Bugs: #95148: union all signed and unsigned int display wrong result.

Bug #95148	union all signed and unsigned int display wrong result.
Submitted:	26 Apr 2019 9:29	Modified:	16 Mar 2020 22:04
Reporter:	Donghoon Lee	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.6.44, 5.7.26, 8.0.16	OS:	Linux (CentOS 6)
Assigned to:		CPU Architecture:	x86 (E5-2670)

Description:
first SELECT statement is signed int and second SELECT is unsigned int in UNION ALL, result of second SELECT unsigned int value is truncated or incorrectly displayed.

How to repeat:
1. Prepare

dba@localhost [donghoon_lee]> create table test1 (id int signed) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

dba@localhost [donghoon_lee]> create table test2 (id int unsigned) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

dba@localhost [donghoon_lee]> insert into test1 (id) values (2000000000);
Query OK, 1 row affected (0.00 sec)

dba@localhost [donghoon_lee]> insert into test2 (id) values (4000000000);
Query OK, 1 row affected (0.01 sec)

2. correct cases: SELECT unsigned int first,

dba@localhost [donghoon_lee]> select id from test2 union all select id from test1;
+------------+
| id         |
+------------+
| 4000000000 |
| 2000000000 |
+------------+
2 rows in set (0.00 sec)

dba@localhost [donghoon_lee]> select max(id) from test2 union all select max(id) from test1;
+------------+
| max(id)    |
+------------+
| 4000000000 |
| 2000000000 |
+------------+
2 rows in set (0.00 sec)

3. incorrect cases: SELECT signed int first,

dba@localhost [donghoon_lee]> select id from test1 union all select id from test2;
+------------+
| id         |
+------------+
| 2000000000 |
| -294967296 |
+------------+
2 rows in set (0.00 sec)

dba@localhost [donghoon_lee]> select max(id) from test1 union all select max(id) from test2;
+------------+
| max(id)    |
+------------+
| 2000000000 |
| 2147483647 |
+------------+
2 rows in set (0.00 sec)

Hello Donghoon Lee,

Thank you for the report.

regards,
Umesh

The bug report has been registered and the problem has been verified in 8.0, 5.7, and 5.6, but there is no response for almost a year.
Please give me a feedback.

Documented fix as follows in the MySQL 8.0.21 changelog:

    After refactoring the type propagation code for LEAST, GREATEST,
    UNION and other functions, an adjustment of the result type for
    data types like ENUM also replaced the calculated integer data
    type a type that could not accommodate both signed and unsigned
    values.

    Regression of BUG#83895.

Closed.

Posted by developer:
 
This bugfix is based on a rather large refactoring in 8.0, so it is not recommended to be backported to earlier versions.

Possible workarounds:

1. Select the unsigned integer first, and the signed integer in following query blocks (according to bug report)

2. Cast the unsigned integer to signed integer, or vice versa.
   Casting the unsigned to signed works as long as values are less than 2^63.
   Casting the signed to unsigned works as long as there are no negative values.