Bug #44363 IDIV not rounding off float/decimals correctly ?

• Submitted: 20 Apr 2009 9:56
• Modified: 21 Apr 2009 11:52
• Reporter: Prafulla Tekawade
• Status: Verified
• Category: MySQL Server: General
• Severity: S2 (Serious)
• Version: 5.1.15, 5.1.35-bzr
• OS: Any
• CPU Architecture: Any

[20 Apr 2009 9:56] Prafulla Tekawade

Description:
idiv in the given testcase does not seem to round off 12.5 correctly.
It should have rounded 12.5 to 13 as shown by round and other cast methods.
If there is any different logic used to round children of div, it seems inconsistent to me.
Please throw some light on it.

drop table t1;
create table t1(f1 float, d1 double, i1 int);
insert into t1 values(12.5, 12.5, 1);
insert into t1 values(12.4, 12.6, 1);
select f1 div i1 from t1;
select d1 div i1 from t1;

mysql> select f1 div i1 from t1;
+-----------+
| f1 div i1 |
+-----------+
|        12 | 
|        12 | 
+-----------+
2 rows in set (0.00 sec)

mysql> select d1 div i1 from t1;
+-----------+
| d1 div i1 |
+-----------+
|        12 | 
|        13 | 
+-----------+
2 rows in set (0.00 sec)

mysql> select cast(12.5 as signed);
+----------------------+
| cast(12.5 as signed) |
+----------------------+
|                   13 | 
+----------------------+
1 row in set (0.00 sec)

mysql> select cast(12.5 as unsigned);
+------------------------+
| cast(12.5 as unsigned) |
+------------------------+
|                     13 | 
+------------------------+
1 row in set (0.00 sec)

mysql> select round(12.5);
+-------------+
| round(12.5) |
+-------------+
|          13 | 
+-------------+
1 row in set (0.00 sec)

How to repeat:
drop table t1;
create table t1(f1 float, d1 double, i1 int);
insert into t1 values(12.5, 12.5, 1);
insert into t1 values(12.4, 12.6, 1);
select f1 div i1 from t1;
select d1 div i1 from t1;

[21 Apr 2009 11:52] Valeriy Kravchuk

Yes, I think this is inconsistent:

openxs@suse:/home2/openxs/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.35-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t1;
Query OK, 0 rows affected (0.10 sec)

mysql> create table t1(f1 float, d1 double, i1 int);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t1 values(12.5, 12.5, 1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(12.4, 12.6, 1);
Query OK, 1 row affected (0.00 sec)

mysql> select f1 div i1 from t1;
+-----------+
| f1 div i1 |
+-----------+
|        12 |
|        12 |
+-----------+
2 rows in set (0.00 sec)

mysql> select d1 div i1 from t1;
+-----------+
| d1 div i1 |
+-----------+
|        12 |
|        13 |
+-----------+
2 rows in set (0.00 sec)

mysql> select 12.6 div 1;
+------------+
| 12.6 div 1 |
+------------+
|         13 |
+------------+
1 row in set (0.00 sec)

mysql> select 12.5 div 1;
+------------+
| 12.5 div 1 |
+------------+
|         13 |
+------------+
1 row in set (0.00 sec)