CODEWITHSUNDEEP
mysqlfunctionroundingdivision
Aleks G
Aleks G

Reputation: 57316

MySQL function - strange division results?

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?

Upvotes: 5

Views: 1350

Answers (3)

DhruvPathak
DhruvPathak

Reputation: 43245

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)

Upvotes: 1

juergen d
juergen d

Reputation: 204784

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

Upvotes: 5

Jason Heo
Jason Heo

Reputation: 10246

need to specify fraction:

mysql> SELECT 1/2 ;
+--------+
| 1/2    |
+--------+
| 0.5000 |
+--------+

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,2));
+-------------------------------+
| CAST(1 / 50 AS DECIMAL(10,2)) |
+-------------------------------+
|                          0.02 |
+-------------------------------+

Upvotes: 0

Related Questions