Bug #37143 Integer division in case statement can be rounded to whole numbers
Submitted: 2 Jun 2008 19:49 Modified: 2 Jun 2008 20:24
Reporter: Tom Dexter Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.54 OS:Linux
Assigned to: CPU Architecture:Any

[2 Jun 2008 19:49] Tom Dexter
Description:
It's possible that this may be related to bug 24907, and I apologize if it is, but it was such an odd result that I decided to file a bug report.

I've been able to reproduce a simplified version of a problem I encountered where division of integers gets truncated to whole numbers, but only when 1) the division is within a CASE statement, and 2) there is a join involved that causes the optimizer to use "Using temporary; Using filesort".

Note that, if it makes a difference, my div_precision_increment is at the default of 4.

How to repeat:
The following will set two simple tables that reproduce this:
--------------------------------
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;

CREATE TABLE t1 (id INTEGER, name VARCHAR(255), bytes INTEGER, INDEX id_name(id, name));

INSERT INTO t1 VALUES (1, 'test', 123), (2, 'test2', NULL);

CREATE TABLE t2 (child_id INTEGER, parent_id INTEGER, PRIMARY KEY (parent_id, child_id));

INSERT INTO t2 VALUES (1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 5),
(6, 6),
(7, 7),
(8, 8),
(9, 9);
--------------------------------

With the above, the following SQL:

SELECT CASE WHEN bytes IS NULL THEN NULL ELSE 14098432/24098432 END FROM t1
JOIN t2 ON t2.child_id=t1.id ORDER BY name;

...will result in 1 rather than 0.5850 in the NOT NULL case.  If either the JOIN or the ORDER BY are removed (causing the optimizer to not use "Using temporary; Using filesort") or if the CASE statement is replaces with just the 14098432/24098432 expression, it results in 0.5850.

Here's what the optimizer is doing on my server:

--------------
EXPLAIN SELECT CASE WHEN bytes IS NULL THEN NULL ELSE 14098432/24098432 END FROM t1 JOIN t2 ON t2.child_id=t1.id ORDER BY name
--------------

+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                           |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | t1    | ALL   | id_name       | NULL    | NULL    | NULL |    2 | Using temporary; Using filesort | 
|  1 | SIMPLE      | t2    | index | NULL          | PRIMARY | 8       | NULL |    9 | Using where; Using index        | 
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------+
[2 Jun 2008 20:23] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior with verison 5.0.62, although bug is repeatable with version 5.0.60. Please upgrade.