Problem with union casting integer to ceiling(decimal)

Question

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".

Answer 1

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)

Answer 2

Bug MDEV-15999

Bug MDEV-15999 filed by dbdemon reported this. It's since been fixed in 10.3.1.

Weird MySQL/MariaDB nature

From the docs,

The column names from the first SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each SELECT statement should have the same data type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.)

If the data types of corresponding SELECT columns do not match, the types and lengths of the columns in the UNION result take into account the values retrieved by all of the SELECT statements.

In this case, they reconcile decimal and integer by promoting the integer to a decimal that can't contain it. I know that's horrid, but equally horrid is this silently behaving like that.

SELECT CAST(20 AS decimal(2,1));
+--------------------------+
| CAST(20 AS decimal(2,1)) |
+--------------------------+
|                      9.9 |
+--------------------------+

Which seems to pave the way for this problem.