Problem with union casting integer to ceiling(decimal)
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)
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 eachSELECT
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 theUNION
result take into account the values retrieved by all of theSELECT
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.