MySQL Bugs: #81642: union of decimal(10,1) and decimal(10,9) is not expected decimal(18,9)

Bug #81642	union of decimal(10,1) and decimal(10,9) is not expected decimal(18,9)
Submitted:	30 May 2016 11:26	Modified:	30 May 2016 16:02
Reporter:	Su Dylan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.7.8	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Output:
===
mysql> create table t1 (d double(10,1));
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2 (d double(10,9));
insert into t1 values ('100000000.0');
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values ('100000000.0');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values ('1.23456780');
om t2 Query OK, 1 row affected (0.00 sec)

mysql> select d from t2 union select * from t1;
+-----------+
| d         |
+-----------+
| 1.2345678 |
| 100000000 |
+-----------+
2 rows in set (0.00 sec)

mysql> select cast(1.23456789 as decimal(10,9)) union select cast(100000000 as decimal(10,1));
+-----------------------------------+
| cast(1.23456789 as decimal(10,9)) |
+-----------------------------------+
|                       1.234567890 |
|               100000000.000000000 |
+-----------------------------------+
2 rows in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.7.8-rc-log |
+--------------+
1 row in set (0.00 sec)

Problem:
===
Expected result for "select d from t2 union select * from t1" is decimal(18,9).

How to repeat:
drop table if exists t1,t2;
create table t1 (d double(10,1));
create table t2 (d double(10,9));
insert into t1 values ('100000000.0');
insert into t2 values ('1.23456780');
select d from t2 union select * from t1;
select cast(1.23456789 as decimal(10,9)) union select cast(100000000 as decimal(10,1));

Suggested fix:
"select d from t2 union select * from t1" returns result in decimal(18,9).

Hello Mr (or Ms) Dylan,

What text in our user manual gave you the impression that resulting type of the CASTED floating number should be decimal (18,9).  Casted IEEE floating number types will behave (in this case) as decimals, as much as possible. However, you should not forget that you are dealing with a floating point type and not decimal type, regardless of the casting.

Have you tried the same operation with decimal types ???

Hi Sinisa,

Thanks for your feedback.

I made it really hard to understand. Sorry for that. Let me clarify it.

For "select d from t2 union select * from t1", I mean double(18,9), not decimal(18,9).

I may have some misunderstanding. Then what should the result type for "select d from t2 union select * from t1" be? 

Thanks:)

Hi,

The output for that UNION should be:

 "select d from t2 union select * from t1"
+-----------+
| d         |
+-----------+
| 1.2345678 |
| 100000000 |
+-----------+

If you have read our manual, you would have read that in the declaration of REAL or DOUBLE (M,N), total number of digits and number of digits in the fractional part, are only INFORMATIVE and are not using in formatting the output.

Hence, not a bug.