Bug #16584 IF( 1, A, B ) * expr != IF( <true>, A, B ) * expr. --> Heavy rounding errors.
Submitted: 17 Jan 2006 21:32 Modified: 28 Feb 2008 18:28
Reporter: Tapferes Schneiderlein Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.18, 4.1.11, 5.0.18, 5.0.19-BK OS:Linux (Linux)
Assigned to: Georgi Kodinov CPU Architecture:Any

[17 Jan 2006 21:32] Tapferes Schneiderlein
Description:
MySQL produces different results when using an IF clause and arithmetical expressions.

IF( 1, A, B ) * expression
-> correct result

IF( <true>, A, B ) * expression
-> heavily incorrect rounded result

How to repeat:
CREATE
TEMPORARY # you can omit this line
TABLE `projects` (
  `id` INT,
  `f_id_customer` INT
);

INSERT INTO `projects` VALUES (1, 100);

CREATE
TEMPORARY # you can omit this line
TABLE `link_customer_staff` (
  `f_id_customer` INT,
  `f_id_staff` INT
);

INSERT INTO `link_customer_staff` VALUES (100, 10);

CREATE
TEMPORARY # you can omit this line
TABLE `payments` (
  `f_id_project` INT,
  `amount` INT
);

SELECT

IF( 1, 1/1.16, 0 )
    * ( 2000 + IFNULL( SUM( pay.amount ), 0 ) / 100 )
    AS "correct",

IF( l.f_id_staff = 10, 1/1.16, 0 )
    * ( 2000 + IFNULL( SUM( pay.amount ), 0 ) / 100 )
    AS "wrong",

VERSION()

FROM projects p

LEFT JOIN link_customer_staff l
    USING ( f_id_customer )

LEFT JOIN payments pay
    ON pay.f_id_project = p.id

GROUP BY p.id;

# +-----------+-----------+--------------------+
# | correct   | wrong     | VERSION()          |
# +-----------+-----------+--------------------+
# | 1724.1379 | 1724.2000 |4.0.18-standard-log |
# +-----------+-----------+--------------------+

# +-----------+-----------+---------------------------+
# | correct   | wrong     | VERSION()                 |
# +-----------+-----------+---------------------------+
# | 1724.1379 | 1724.2000 | 4.1.11-Debian_4sarge2-log |
# +-----------+-----------+---------------------------+

# +-----------+-----------+----------------+
# | correct   | wrong     | VERSION()      |
# +-----------+-----------+----------------+
# | 1724.1379 | 1724.2000 | 5.0.18-max-log |
# +-----------+-----------+----------------+
[21 Jan 2006 19:01] Valeriy Kravchuk
Thank you for a bug report. Verified just as described on 5.0.19-BK (ChangeSet@1.1997, 2006-01-20 17:21:39+03:00) on Linux:

mysql> CREATE
    -> TEMPORARY # you can omit this line
    -> TABLE `projects` (
    ->   `id` INT,
    ->   `f_id_customer` INT
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `projects` VALUES (1, 100);
Query OK, 1 row affected (0.01 sec)

mysql> CREATE
    -> TEMPORARY # you can omit this line
    -> TABLE `link_customer_staff` (
    ->   `f_id_customer` INT,
    ->   `f_id_staff` INT
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `link_customer_staff` VALUES (100, 10);
Query OK, 1 row affected (0.00 sec)

mysql> CREATE
    -> TEMPORARY # you can omit this line
    -> TABLE `payments` (
    ->   `f_id_project` INT,
    ->   `amount` INT
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    -> IF( 1, 1/1.16, 0 )
    ->     * ( 2000 + IFNULL( SUM( pay.amount ), 0 ) / 100 )
    ->     AS "correct",
    ->
    -> IF( l.f_id_staff = 10, 1/1.16, 0 )
    ->     * ( 2000 + IFNULL( SUM( pay.amount ), 0 ) / 100 )
    ->     AS "wrong",
    ->
    -> VERSION()
    ->
    -> FROM projects p
    ->
    -> LEFT JOIN link_customer_staff l
    ->     USING ( f_id_customer )
    ->
    -> LEFT JOIN payments pay
    ->     ON pay.f_id_project = p.id
    ->
    -> GROUP BY p.id;
+---------------+---------------+-----------+
| correct       | wrong         | VERSION() |
+---------------+---------------+-----------+
| 1724.13793000 | 1724.20000000 | 5.0.19    |
+---------------+---------------+-----------+
1 row in set (0.06 sec)

mysql> SELECT IF( 1, 1/1.16, 0 )     * ( 2000 + IFNULL( SUM( pay.amount ), 0)
/ 100 )     AS "correct", IF( l.f_id_staff = 10, 1/1.16, 0 )     * ( 2000 + IFN
ULL( SUM( pay.amount ), 0 ) / 100 )     AS "wrong", VERSION() FROM projects p LEFT JOIN link_customer_staff l     USING ( f_id_customer ) LEFT JOIN payments pay     ON pay.f_id_project = p.id;
+---------------+---------------+-----------+
| correct       | wrong         | VERSION() |
+---------------+---------------+-----------+
| 1724.13793000 | 1724.20000000 | 5.0.19    |
+---------------+---------------+-----------+
1 row in set (0.00 sec)
[29 Mar 2006 0:55] Ian Greenhoe
A shorter test case is as follows:

############
drop table if exists test1;
drop table if exists test2;

CREATE TABLE `test1` ( `col_1a` INT, `col_1b` INT );
CREATE TABLE `test2` ( `col_2a` INT, `col_2b` INT );

INSERT INTO `test1` VALUES (1, 100);

SELECT

IF (1,         1/9, 0) * (1 + IFNULL(SUM(t2.col_2b), 0) / 2) AS "correct",
IF (t1.col_1a, 1/9, 0) * (1 + IFNULL(SUM(t2.col_2b), 0) / 2) AS "wrong"

FROM test1 t1
LEFT JOIN test2 t2 ON t2.col_2a = t1.col_1a;
#############

+------------+------------+
| correct    | wrong      |
+------------+------------+
| 0.11111111 | 0.11110000 |
+------------+------------+
1 row in set (2.39 sec)

Note that if the third option to the if statement has sufficent precision, it seems to cause this to not fail.  Also, if the ifnull refers to a literal NULL, then you get the same (truncated) answer.
[21 Dec 2007 16:28] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40345

ChangeSet@1.2594, 2007-12-21 18:28:11+02:00, gkodinov@macbook.gmz +5 -0
  Bug #16584: IF( 1, A, B ) * expr != IF( <true>, A, B ) * expr. 
    --> Heavy rounding errors.
  When calculating aggregated functions the optimizer is storing
  intermediate function calculation results. It used a string 
  buffer to hold these results. This caused the problem, because
  when converting intermediate results to strings their designated
  precision was limited.
  Fixed by removing Item_copy_string and replacing it with 
  Item_cache_* hierarchy that has ints, floating point etc buffers
  so the precision of the intermediate results is not lost.
[28 Feb 2008 18:28] Georgi Kodinov
MySQL's manual (chapter 11.5.1) says that a result of a division has div_precision_argument(default 4) more digits after the decimal point compared to its arguments.
So in the case you're describing what you consider wrong is actually correct according to the manual.
Unlike the normal arithmetics, where the intermediate calculations are done in the largest possible precision and the result is then rounded to fit the place it's stored the arithmetics in MySQL have a finite (and defined in the manual) precision for the intermediate results.
In this case this finite precision was not enforced in a particular case and hence you're getting what looks like a increased precision.
This discrepancy will be fixed by the fix for bug #11604 which will enforce the correct (according to MySQL's manual) rounding of the intermediate results and will limit the precision to match the other cases.