MySQL Bugs: #99567: Wrong result is returned with UNION syntax

Bug #99567	Wrong result is returned with UNION syntax
Submitted:	14 May 2020 7:58	Modified:	7 Apr 2021 13:27
Reporter:	Hope Lee (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	8.0.20	OS:	CentOS
Assigned to:		CPU Architecture:	Any

Description:
The server returns the wrong result when executing a simple query using UNION syntax.

How to repeat:
select 1,19 union all select 2, 2.9;

Expected result:
+---+-----+
| 1 | 19  |
+---+-----+
| 1 | 19 |
| 2 | 2.9 |
+---+-----+
2 rows in set (0.00 sec)

Actual result:
+---+-----+
| 1 | 19  |
+---+-----+
| 1 | 9.9 |
| 2 | 2.9 |
+---+-----+
2 rows in set (0.00 sec)

Thank you for the bug report. Version 5.6/5.7 not affected.

It looks like an issue in mysql8.0. I traced the query and guessed the problem is that the precision/scale is inappropriate when transforming and storing the ulonglong to decimal if there are an integer and a decimal in a select .. union .. statement.

For example, 

mysql> select 19  union  select 1.1;
+-----+
| 19  |
+-----+
| 9.9 |
| 1.1 |
+-----+

The number 1.1 is a decimal,  precision is 2, scale is 1. 
The number 19 is an integer. 

Firstly, it will be transformed into a decimal by using ull2dec(), the precision is 9(by default) and scale is 0 after transformed.  
Then, the decimal 10 will be transformed into the binary format to store by using decimal2bin(). However, the executor applies the precision/scale of the number 1.1 to decimal2bin(), the result is 9.9 due to the precision is 2, and scale is 1.

more tests.
mysql> select 3, 1234 union select 2, 1.111;
+---+---------+
| 3 | 1234    |
+---+---------+
| 3 | 999.999 |
| 2 |   1.111 |
+---+---------+

mysql> select 3 as c1, 1234 as c2 union all select 2, 1.111 union select 3, 1.11111;
+----+-----------+
| c1 | c2        |
+----+-----------+
|  3 | 999.99999 |
|  2 |   1.11100 |
|  3 |   1.11111 |
+----+-----------+

If all of the input numbers are the decimal or negtive integer, the results are correct.
mysql> select 1,10.1  union  select 2, 3.75;
+---+-------+
| 1 | 10.1  |
+---+-------+
| 1 | 10.10 |
| 2 |  3.75 |
+---+-------+

select 3, -1234 union select 2, 1.111;
+---+-----------+
| 3 | -1234     |
+---+-----------+
| 3 | -1234.000 |
| 2 |     1.111 |
+---+-----------+

Posted by developer:
 
Fixed in 8.0.25.

The precision for a UNION between integer and decimal values could be
calculated incorrectly, leading to truncation of the integer in the
result.

Posted by developer:
 
Fixed in 8.0.26, not 8.0.25.