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

• dbdemon: 20 somehow becomes 9.9?! That doesn't seem right.
• András Váczi: MySQL 8.0 on DBFiddle shows the same nonsense: dbfiddle.uk/…
• Evan Carroll: Even more confusing... SELECT 20 UNION SELECT null UNION SELECT 40 UNION SELECT 4.3; works fine
• Qsigma: Pls post the output that you need. Also, how much control do you have over the code that is generated: for example can you change the type of 20 to 20.0 , or the type of 2.2 to 2?
• ngcbassman: I added more context now, one possible solution in my case is to force the value to 20.0 in the code, I tested and fix the problem, but it looks like a bug because only happens in the specific scenario where the null is involved and in that order.

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)

• Qsigma: The CAST to DOUBLE is a syntax error in MySQL. A decimal works instead: SELECT CAST(20 AS DECIMAL) AS x UNION SELECT NULL UNION SELECT 2.2;
• dbdemon: I took the liberty of reporting this as a bug in the MariaDB Jira: jira.mariadb.org/browse/MDEV-15999
• dbdemon: The issue appears to have been fixed in MariaDB 10.3. (I've just tested with 10.3.6, and 10.3.1 is also supposed to work.)
• Andriy M: Curiously there's no problem if you specify the 20 as 020. The behaviour is the same in MariaDB 10.2 and in MySQL 8.0. Looks very much like the length of the literal affects the type of the combined column. In any event, this is definitely a bug in my book.
• Mick O'Hea: I'm seeing the issue in MySQL 8.0 even without the null (although that works OK in MariaDB 10.2). There's also differences in the column size if you include a leading zero or a calculation

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.

• ypercubeᵀᴹ: SELECT cast(20 as signed) UNION SELECT null UNION SELECT 2.2 ; produces the same (9.9) wrong result. But if we use "unisgned" there it all goes well. Go figure ...
• Andriy M: SELECT -20 UNION SELECT null UNION SELECT 2.2 ; works correctly too, as does SELECT 20. UNION SELECT null UNION SELECT 2.2 ;
• IMSoP: The key insight here is that MySQL has selected a data type that can hold 2.2 but is too narrow to hold 20. You can see this by changing the last select clause to CAST(2.2 AS DECIMAL(10,2)), which gives 20.0 as the first row (implicitly running CAST(20 AS DECIMAL(10,2))).
• Mick O'Hea: The weird thing is it's not just basing the data type on the 2.2 . If you try select 20000 union select null union select 2.22 you get 9999.99, in a decimal(6,2) . It's always one digit too short to hold the value
• Salman Arshad: @Mick yeah. If you throw in the NULL value in the middle it calculates the precision 1 short.