MySQL Bugs: #25887: Documentation for return types of ceiling and floor is wrong

Bug #25887	Documentation for return types of ceiling and floor is wrong
Submitted:	27 Jan 2007 4:36	Modified:	20 Apr 2007 23:47
Reporter:	Mark Callaghan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.0.36-BK	OS:	Linux (Linux)
Assigned to:	Alexey Kopytov	CPU Architecture:	Any
Tags:	bfsm_2007_02_01, ceiling, documentation, floor

Description:
The documentation states that floor() and ceiling() return type bigint. In some cases, the return type is Double.

http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html

How to repeat:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1106
Server version: 5.0.30-standard-debug 
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> create table d (d double);
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into d values (1.1);
Query OK, 1 row affected (0.00 sec)
 
mysql> create table floor_d as select floor(d) from d;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
mysql> create table ceiling_d as select ceiling(d) from d;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table floor_d;
+---------+--------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                           |
+---------+--------------------------------------------------------------------------------------------------------+
| floor_d | CREATE TABLE `floor_d` (
  `floor(d)` double(17,0) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> show create table ceiling_d;
+-----------+------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                               |
+-----------+------------------------------------------------------------------------------------------------------------+
| ceiling_d | CREATE TABLE `ceiling_d` (
  `ceiling(d)` double(17,0) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

Suggested fix:
Update the docs

The behavior is version specific. The return type is bigint (as
documented) in MySQL 4.0 and 4.1.

Shorter test script:

drop table if exists d, t;
create table d (d double);
insert into d values (1.1);
create table t as select floor(d), ceiling(d) from d;
show create table t\G

Result in 4.0 or 4.1:

*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `floor(d)` bigint(17) default NULL,
  `ceiling(d)` bigint(17) default NULL
) TYPE=MyISAM

Result in 5.0 or 5.1:

*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `floor(d)` double(17,0) default NULL,
  `ceiling(d)` double(17,0) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

The result changes at version 5.0.3.  In 5.0.2,
the return type is bigint; in 5.0.3, it's double.
This might have something to do with the introduction
of precision math in 5.0.3.

It's unclear whether the change in return type is
deliberate. If so, it needs to be documented. Otherwise,
it is a bug that needs to be fixed. (I vote for the latter
since the functions are supposed to return integer
values.)

Verified just as described with latest 5.0.36-BK on Linux. I think, it is a (serious enough) bug, and not just a documentation request. There is no good reason to use DOUBLE data type for something that is INTEGER by definition.

The documentation for Mathematical Functions has been updated to more correctly describe the FLOOR/CEILING result types.

Thank you for the bug report!