Mysql – Problem with union casting integer to ceiling(decimal)

datatypesdecimalMySQLmysql-5.6union

I have this scenario, it looks like MySQL is taking the largest decimal value and tries to cast the other values to that.

The problem is that this query is generated by an external library, so I don't have control over this code, at this level at least.
Do you have some idea how to fix this?

SELECT 20 AS x
  UNION SELECT null
  UNION SELECT 2.2;
+------+
| x    |
+------+
|  9.9 | -- why from 20 to 9.9
| NULL |
|  2.2 |
+------+

Expected Result

+------+
| x    |
+------+
|   20 | -- or 20.0, doesn't matter really in my case
| NULL |
|  2.2 |
+------+

Adding more context, I'm using Entity Framework 6 with an extension library http://entityframework-extensions.net/ to save changes in batches, specifically the method context.BulkSaveChanges();, this library creates queries using "select union".

Best Answer

Looks like a bug to me and I can confirm this puzzling behaviour in:

10.2.14-MariaDB

If possible you can cast the integer value to a double:

SELECT cast(20 as double) UNION SELECT null UNION SELECT 2.2;

or make sure you have the double value first:

SELECT 2.2 UNION SELECT null UNION SELECT 22;

Further observations after reading the comments in @Evan Carroll's answer

select 20 union select null union select 2;
+------+
| 20   |
+------+
|   20 |
| NULL |
|    2 |
+------+

Ok, using int values does not seem to produce the error.

select 20 union select null union select 9.0;
+------+
| 20   |
+------+
| 9.9  |
| NULL |
| 9.0  |
+------+

ERROR: Seems like output is decimal(2,1)

create table tmp as select * from (select 20 as x 
                                   union 
                                   select null 
                                   union 
                                   select 9.0) as t

describe tmp;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| x     | decimal(2,1) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

The error is not isolated to the command line interface, it exists for python2-mysql-1.3.12-1.fc27.x86_64 as well:

>>> import MySQLdb
>>> db = MySQLdb.connect(host="localhost", user="*****", passwd="*****", db="test") 
>>> cur = db.cursor()
>>> cur.execute("SELECT 20 union select null union select 2.2")
3L
>>> for row in cur.fetchall() :
...     print row
... 
(Decimal('9.9'),)
(None,)
(Decimal('2.2'),)

Oddly enough the error disappears if null is moved first or last:

select null union select 20 union select 9.0;
select 20 union select 9.0 union select null;

+------+
| NULL |
+------+
| NULL |
| 20.0 |
| 9.0  |
+------+

If null is placed first, resulting type is decimal(20,1). If null is placed last resulting type is decimal(3,1)

The error also disappears if another leg is added to the union:

select 20 union select 6 union select null union select 9.0;
+------+
| 20   |
+------+
| 20.0 |
| 6.0  |
| NULL |
| 9.0  |
+------+

resulting type decimal(20,1)

adding another null in the middle preserves the error:

select 20 union select null union select null union select 9.0;
+------+
| 20   |
+------+
| 9.9  |
| NULL |
| 9.0  |
+------+

But adding a null at the beginning fixes it:

select null union select 20 union select null union select null union select 9.0;
+------+
| NULL |
+------+
| NULL |
| 20.0 |
| 9.0  |
+------+

As expected casting first value to decimal(3,1) works.

Finally, explicitly casting to decimal(2,1) produces the same error but with a warning:

select cast(20 as decimal(2,1));
+--------------------------+
| cast(20 as decimal(2,1)) |
+--------------------------+
| 9.9                      |
+--------------------------+
1 row in set, 1 warning (0.00 sec)
Related Question