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

[27 Jan 2007 4:36] Mark Callaghan
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
[27 Jan 2007 5:06] Paul Dubois
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.)
[28 Jan 2007 10:03] Valeriy Kravchuk
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.
[20 Apr 2007 23:47] Timothy Smith
The documentation for Mathematical Functions has been updated to more correctly describe the FLOOR/CEILING result types.

Thank you for the bug report!