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 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------+