MySQL function - strange division results?

Question

I'm seeing some strange behavior in MySQL related to user-created functions. I'll simplify this as much as possible.

mysql> SELECT 1 / 50

+--------+
| 1/50   |
+--------+
| 0.0200 |
+--------+
1 row in set (0.00 sec)

So far so good. Now I create a function to do this division and call the function:

mysql> delimiter $$
mysql> create function myd(var decimal) returns decimal language sql deterministic
    -> begin
    ->     declare res decimal;
    ->     set res = var / 50;
    ->     
    ->     return res;
    -> end
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select myd(1);
+--------+
| myd(1) |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

Very strange. Ok, let's try a few other values:

mysql> select myd(10), myd(20), myd(50), myd(70), myd(100);
+---------+---------+---------+---------+----------+
| myd(10) | myd(20) | myd(50) | myd(70) | myd(100) |
+---------+---------+---------+---------+----------+
|       0 |       0 |       1 |       1 |        2 |
+---------+---------+---------+---------+----------+
1 row in set (0.00 sec)

I did a bit more testing - but it's pretty clear from the results that the result of the function is being rounded to an integer value, despite the function being declared as returns decimal. I tried replacing type decimal with float but that didn't change a bit.

So why is this rounding occurring and, more importantly, how do I prevent it?

Answer 1

If you don't specify a precision for a decimal it will be DECIMAL(M,0) by default which is zero digits after the comma.

Example: Define it as

decimal(10,3) 

to have 3 digits after the comma.

Source

Answer 2

Decimal declared without precision means 0 precision.

mysql> select CAST(1/50 as Decimal);
+-----------------------+
| CAST(1/50 as Decimal) |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select CAST(1/50 as Decimal(10,4));
+-----------------------------+
| CAST(1/50 as Decimal(10,4)) |
+-----------------------------+
|                      0.0200 |
+-----------------------------+
1 row in set (0.00 sec)

If you change it to float or decimal(10,4) , it will work.

mysql> delimiter $$
mysql> create function myd6(var float) returns float language sql deterministic begin declare res float; set res = var / 50; return res; end$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select myd6(1);
+----------------------+
| myd6(1)              |
+----------------------+
| 0.019999999552965164 |
+----------------------+
1 row in set (0.00 sec)