Description:
It appears that when the total number of digits in a decimal
result exceeds 64, odd things start to happen. The UNION
of a large decimal with another results in an incorrect value.
How to repeat:
mysql> create table t1 (col1 decimal(43));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values (1000000000000000000000000000000000000000000);
Query OK, 1 row affected (0.00 sec)
-- NB: This value is exactly 43 digits long.
mysql> select distinct col1 * 1.000000000000000000000 from t1;
+-------------------------------------------------------------------+
| col1 * 1.000000000000000000000 |
+-------------------------------------------------------------------+
| 1000000000000000000000000000000000000000000.000000000000000000000 |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)
-- This is the correct result. It has a scale of 21, as does the
second argument in the math expression. So the total length
of the result is 64 digits.
mysql> select distinct col1 * 1.000000000000000000000 from t1 union select col1 from t1;
+-------------------------------------------------------------------+
| col1 * 1.000000000000000000000 |
+-------------------------------------------------------------------+
| 1000000000000000000000000000000000000000000.000000000000000000000 |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)
-- When the result is 64 digits long, the UNION returns the correct result.
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t1 (col1 decimal(44));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values (10000000000000000000000000000000000000000000);
Query OK, 1 row affected (0.00 sec)
NB: This value is exactly 44 digits long.
mysql> select distinct col1 * 1.000000000000000000000 from t1;
+--------------------------------------------------------------------+
| col1 * 1.000000000000000000000 |
+--------------------------------------------------------------------+
| 10000000000000000000000000000000000000000000.000000000000000000000 |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)
-- This is the correct result. It has a scale of 21, as does the
second argument in the math expression. So the total length
of the result is 65 digits.
mysql> select distinct col1 * 1.000000000000000000000 from t1 union select col1 from t1;
+-------------------------------------------------------------------+
| col1 * 1.000000000000000000000 |
+-------------------------------------------------------------------+
| 9999999999999999999999999999999999999999999.999999999999999999999 |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)
-- This is the same query that worked when the result was 64 digits
long. As soon as the result is 65 digits long, UNION begins to return
an incorrect result.
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)
