Bug #48370 Absolutely wrong calculations with GROUP BY and decimal fields when using IF

• Submitted: 28 Oct 2009 10:32
• Modified: 12 Mar 2010 17:01
• Reporter: Zigmund Bulinsh
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 5.1.40
• OS: Windows
• Assigned to: Davi Arnaut
• CPU Architecture: Any
• Tags: GROUP BY, IF, regression

[28 Oct 2009 10:32] Zigmund Bulinsh

Description:
v_total column is calculated wrong!
And this is really critical..verry critical bug because of this I rolled back to 5.1.38 (because 5.1.39 has a bug which I also reported about slave_deattach_thread..).

How to repeat:
Load attached dump to mysql database.
Then run this query:

mysql> select payment_id, price, discount
    -> from sub_tasks
    -> order by payment_id
    -> limit 10;
+------------+----------+----------+
| payment_id | price    | discount |
+------------+----------+----------+
|       3001 |  12.6000 |   0.0000 |
|       3001 |  14.5800 |   0.0000 |
|       3003 |   9.2400 |   0.0000 |
|       3004 | 164.1800 |   0.0000 |
|       3005 |  23.6600 |   0.0000 |
|       3005 | 223.6500 |   0.0000 |
|       3006 |  16.2900 |   0.0000 |
|       3006 |   1.7100 |   0.0000 |
|       3006 |  17.4600 |   0.0000 |
|       3007 |   8.3300 |   0.0000 |
+------------+----------+----------+
10 rows in set (0.00 sec)

mysql> select
    ->   PAY.id,
    ->   MIN((1 + IF(PAY.status, PAY.vat, SUP.vat) / 100)) v_min,
    ->   MAX((1 + IF(PAY.status, PAY.vat, SUP.vat) / 100)) v_max,
    ->   #net is rounded at the beginning
    ->   SUM(
    ->     ROUND(
    ->       ROUND(SUB.price, 2) * BCUR.rate / CUR.rate
    ->     , 2)
    ->   ) v_net,
    ->
    ->   SUM(
    ->     ROUND(
    ->       (ROUND(SUB.price, 2) - ROUND(ROUND(SUB.price, 2) * (SUB.discount / 100), 2)) * BCUR.rate / CUR.rate
    ->     , 2)
    ->   ) v_net_with_discount,
    ->
    ->   ROUND(SUM(
    ->     ROUND(
    ->       (ROUND(SUB.price, 2) - ROUND(ROUND(SUB.price, 2) * (SUB.discount / 100), 2)) * BCUR.rate / CUR.rate
    ->     , 2) * (1 + IF(PAY.status, PAY.vat, SUP.vat) / 100)
    ->   ), 2) v_total
    -> from
    ->   payments PAY
    ->     join (
    ->       suppliers SUP,
    ->       currencies CUR)
    ->     on (
    ->       PAY.supplier_id = SUP.id and
    ->       PAY.currency_id = CUR.id)
    ->     left join (
    ->       sub_tasks SUB,
    ->       currencies BCUR)
    ->     on (
    ->       SUB.payment_id = PAY.id and
    ->       SUB.currency_id = BCUR.id)
    -> where
    ->   PAY.id <> 0 and
    ->   PAY.vat = 0 and
    ->   SUP.vat = 0
    -> group by
    ->   PAY.id,
    ->   SUP.id
    -> limit 10;
+------+------------+------------+---------+---------------------+---------+
| id   | v_min      | v_max      | v_net   | v_net_with_discount | v_total |
+------+------------+------------+---------+---------------------+---------+
| 3001 | 1.00000000 | 1.00000000 |   27.18 |               27.18 |   27.20 |
| 3003 | 1.00000000 | 1.00000000 |    9.24 |                9.24 |    9.20 |
| 3004 | 1.00000000 | 1.00000000 |  164.18 |              164.18 |  164.20 |
| 3005 | 1.00000000 | 1.00000000 |  247.31 |              247.31 |  247.40 |
| 3006 | 1.00000000 | 1.00000000 |   35.46 |               35.46 |   35.50 |
| 3007 | 1.00000000 | 1.00000000 |   73.59 |               73.59 |   73.50 |
| 3008 | 1.00000000 | 1.00000000 |   70.38 |               70.38 |   70.40 |
| 3009 | 1.00000000 | 1.00000000 |   32.04 |               32.04 |   32.00 |
| 3011 | 1.00000000 | 1.00000000 |   63.66 |               63.66 |   63.70 |
| 3012 | 1.00000000 | 1.00000000 | 1052.01 |             1052.01 | 1052.00 |
+------+------------+------------+---------+---------------------+---------+
10 rows in set (0.08 sec)

[28 Oct 2009 10:34] Zigmund Bulinsh

The VAT and DISCOUNT columns are 0.00 in suppliers and payments tables.

Real problem is in the usage of the IF statement (which really there always is 1.00). If we exclude the IF statement from the query, then all is ok.

[28 Oct 2009 10:38] Zigmund Bulinsh

This query worked fine for two years. Until this release 5.1.40.
I thought that the is some ceil() or floor() values for 1 digit after the comma. But it happens in random - once value is bigger, then in another row it is smaller..

[28 Oct 2009 10:38] Zigmund Bulinsh

How this bug could happen. All the calculations of amounts, prices, accounting are affected. I think many solutions in the world will have such problems.

[28 Oct 2009 12:23] MySQL Verification Team

Thank you for the bug report. Could you please print here the expected output of that query. Thanks in advance.

[28 Oct 2009 13:41] Zigmund Bulinsh

+------+------------+------------+---------+---------------------+---------+
| id   | v_min      | v_max      | v_net   | v_net_with_discount | v_total |
+------+------------+------------+---------+---------------------+---------+
| 3001 | 1.00000000 | 1.00000000 |   27.18 |               27.18 |   27.18 |
| 3003 | 1.00000000 | 1.00000000 |    9.24 |                9.24 |    9.24 |
| 3004 | 1.00000000 | 1.00000000 |  164.18 |              164.18 |  164.18 |
| 3005 | 1.00000000 | 1.00000000 |  247.31 |              247.31 |  247.31 |
| 3006 | 1.00000000 | 1.00000000 |   35.46 |               35.46 |   35.46 |
| 3007 | 1.00000000 | 1.00000000 |   73.59 |               73.59 |   73.59 |
| 3008 | 1.00000000 | 1.00000000 |   70.38 |               70.38 |   70.38 |
| 3009 | 1.00000000 | 1.00000000 |   32.04 |               32.04 |   32.04 |
| 3011 | 1.00000000 | 1.00000000 |   63.66 |               63.66 |   63.66 |
| 3012 | 1.00000000 | 1.00000000 | 1052.01 |             1052.01 | 1052.01 |
+------+------------+------------+---------+---------------------+---------+
10 rows in set (0.08 sec)

[28 Oct 2009 14:03] Zigmund Bulinsh

Hint:
(1 + IF(PAY.status, PAY.vat, SUP.vat) / 100) always is 1.0000 (must be).

[28 Oct 2009 14:10] MySQL Verification Team

Thank you for the feedback.

    -> limit 10;
+------+------------+------------+---------+---------------------+---------+
| id   | v_min      | v_max      | v_net   | v_net_with_discount | v_total |
+------+------------+------------+---------+---------------------+---------+
| 3001 | 1.00000000 | 1.00000000 |   27.18 |               27.18 |   27.20 |
| 3003 | 1.00000000 | 1.00000000 |    9.24 |                9.24 |    9.20 |
| 3004 | 1.00000000 | 1.00000000 |  164.18 |              164.18 |  164.20 |
| 3005 | 1.00000000 | 1.00000000 |  247.31 |              247.31 |  247.40 |
| 3006 | 1.00000000 | 1.00000000 |   35.46 |               35.46 |   35.50 |
| 3007 | 1.00000000 | 1.00000000 |   73.59 |               73.59 |   73.50 |
| 3008 | 1.00000000 | 1.00000000 |   70.38 |               70.38 |   70.40 |
| 3009 | 1.00000000 | 1.00000000 |   32.04 |               32.04 |   32.00 |
| 3011 | 1.00000000 | 1.00000000 |   63.66 |               63.66 |   63.70 |
| 3012 | 1.00000000 | 1.00000000 | 1052.01 |             1052.01 | 1052.00 |
+------+------------+------------+---------+---------------------+---------+
10 rows in set (0.51 sec)

mysql 5.1 >show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.1.41-Win X64-log  |
| version_comment         | Source distribution |
| version_compile_machine | unknown             |
| version_compile_os      | Win64               |
+-------------------------+---------------------+
5 rows in set (0.37 sec)
 
   -> limit 10;
+------+------------+------------+---------+---------------------+---------+
| id   | v_min      | v_max      | v_net   | v_net_with_discount | v_total |
+------+------------+------------+---------+---------------------+---------+
| 3001 | 1.00000000 | 1.00000000 |   27.18 |               27.18 |   27.18 |
| 3003 | 1.00000000 | 1.00000000 |    9.24 |                9.24 |    9.24 |
| 3004 | 1.00000000 | 1.00000000 |  164.18 |              164.18 |  164.18 |
| 3005 | 1.00000000 | 1.00000000 |  247.31 |              247.31 |  247.31 |
| 3006 | 1.00000000 | 1.00000000 |   35.46 |               35.46 |   35.46 |
| 3007 | 1.00000000 | 1.00000000 |   73.59 |               73.59 |   73.59 |
| 3008 | 1.00000000 | 1.00000000 |   70.38 |               70.38 |   70.38 |
| 3009 | 1.00000000 | 1.00000000 |   32.04 |               32.04 |   32.04 |
| 3011 | 1.00000000 | 1.00000000 |   63.66 |               63.66 |   63.66 |
| 3012 | 1.00000000 | 1.00000000 | 1052.01 |             1052.01 | 1052.01 |
+------+------------+------------+---------+---------------------+---------+
10 rows in set (0.16 sec)

mysql> show variables like "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| protocol_version        | 10                           |
| version                 | 5.1.38-community             |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | unknown                      |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+

    -> limit 10;
+------+------------+------------+---------+---------------------+---------+
| id   | v_min      | v_max      | v_net   | v_net_with_discount | v_total |
+------+------------+------------+---------+---------------------+---------+
| 3001 | 1.00000000 | 1.00000000 |   27.18 |               27.18 |   27.18 |
| 3003 | 1.00000000 | 1.00000000 |    9.24 |                9.24 |    9.24 |
| 3004 | 1.00000000 | 1.00000000 |  164.18 |              164.18 |  164.18 |
| 3005 | 1.00000000 | 1.00000000 |  247.31 |              247.31 |  247.31 |
| 3006 | 1.00000000 | 1.00000000 |   35.46 |               35.46 |   35.46 |
| 3007 | 1.00000000 | 1.00000000 |   73.59 |               73.59 |   73.59 |
| 3008 | 1.00000000 | 1.00000000 |   70.38 |               70.38 |   70.38 |
| 3009 | 1.00000000 | 1.00000000 |   32.04 |               32.04 |   32.04 |
| 3011 | 1.00000000 | 1.00000000 |   63.66 |               63.66 |   63.66 |
| 3012 | 1.00000000 | 1.00000000 | 1052.01 |             1052.01 | 1052.01 |
+------+------------+------------+---------+---------------------+---------+
10 rows in set (0.53 sec)

mysql 5.0 > show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.0.88-Win X64      |
| version_comment         | Source distribution |
| version_compile_machine | unknown             |
| version_compile_os      | Win64               |
+-------------------------+---------------------+
5 rows in set (0.02 sec)

mysql 5.0 >

[2 Nov 2009 10:00] Alexey Kopytov

The regression was caused by the patch for bug #45261.

[2 Nov 2009 10:29] Davi Arnaut

The problem seems to be due to the SUM function artificially increasing the precision of the field (specially the integer part), which is truncated when a temporary table is created to hold the results (GROUP BY). For the time being, let's revert the patch for Bug#45261 as it needs to be extended even further and perhaps targeted for a later version.

[2 Nov 2009 11:22] 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/88920

3172 Davi Arnaut	2009-11-02
      Bug#48370: Absolutely wrong calculations with GROUP BY and decimal fields when using IF
      Bug#45261: Crash, stored procedure + decimal
      
      Revert fix for Bug#45261 due to unforeseen bugs.

[3 Nov 2009 10:00] Davi Arnaut

Queued to 5.1-bugteam

[4 Nov 2009 9:24] Bugs System

Pushed into 5.1.41 (revid:[e-mail]-20091104092152-qz96bzlf2o1japwc) (version source revid:[e-mail]-20091103162305-08l4gkeuif2ozsoj) (merge vers: 5.1.41) (pib:13)

[11 Nov 2009 6:48] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20091110093407-rw5g8dys2baqkt67) (version source revid:[e-mail]-20091109080109-7dxapd5y5pxlu08w) (merge vers: 6.0.14-alpha) (pib:13)

[11 Nov 2009 6:56] Bugs System

Pushed into 5.5.0-beta (revid:[e-mail]-20091109115615-nuohp02h8mdrz8m2) (version source revid:[e-mail]-20091105121316-hgdduu5vqdpbawf8) (merge vers: 5.5.0-beta) (pib:13)

[13 Nov 2009 11:14] 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/90329

3199 Georgi Kodinov	2009-11-13
      Bug #45261 : Crash, stored procedure + decimal
      Bug #48370  Absolutely wrong calculations with GROUP BY and
        decimal fields when using IF
      
      Added the test cases in the above two bugs for regression
      testing.

[13 Nov 2009 16:20] 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/90377

3199 Georgi Kodinov	2009-11-13
      Bug #45261 : Crash, stored procedure + decimal
      Bug #48370  Absolutely wrong calculations with GROUP BY and
        decimal fields when using IF
      
      Added the test cases in the above two bugs for regression
      testing.
      Added additional tests that demonstrate a incomplete fix.
      Added a new factory method for Field_new_decimal to 
      create a field from an (decimal returning) Item.
      In the new method made sure that all the precision and 
      length variables are capped in a proper way. 
      This is required because Item's can have larger precision
      than the decimal fields and thus need to be capped when
      creating a field based on an Item type.

[17 Nov 2009 23:29] Paul DuBois

Noted in 5.1.41, 5.5.0, 6.0.14 changelogs.

SUM() artificially increased the precision of a DECIMAL argument,
which was truncated when a temporary table was created to hold the
results.

[20 Nov 2009 10:11] 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/91049

3199 Georgi Kodinov	2009-11-20
      Bug #45261 : Crash, stored procedure + decimal
      Bug #48370  Absolutely wrong calculations with GROUP BY and
        decimal fields when using IF
      
      Added the test cases in the above two bugs for regression
      testing.
      Added additional tests that demonstrate a incomplete fix.
      Added a new factory method for Field_new_decimal to 
      create a field from an (decimal returning) Item.
      In the new method made sure that all the precision and 
      length variables are capped in a proper way. 
      This is required because Item's can have larger precision
      than the decimal fields and thus need to be capped when
      creating a field based on an Item type.
      Fixed the wrong typecast to Item_decimal.

[24 Nov 2009 14:42] 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/91425

3219 Georgi Kodinov	2009-11-20
      Bug #45261 : Crash, stored procedure + decimal
      Bug #48370  Absolutely wrong calculations with GROUP BY and
        decimal fields when using IF
      
      Added the test cases in the above two bugs for regression
      testing.
      Added additional tests that demonstrate a incomplete fix.
      Added a new factory method for Field_new_decimal to 
      create a field from an (decimal returning) Item.
      In the new method made sure that all the precision and 
      length variables are capped in a proper way. 
      This is required because Item's can have larger precision
      than the decimal fields and thus need to be capped when
      creating a field based on an Item type.
      Fixed the wrong typecast to Item_decimal.

[2 Dec 2009 8:03] Bugs System

Pushed into 5.1.42 (revid:[e-mail]-20091202080033-mndu4sxwx19lz2zs) (version source revid:[e-mail]-20091125130912-d7hrln14ef7y5d7i) (merge vers: 5.1.42) (pib:13)

[7 Dec 2009 16:47] Paul DuBois

Noted in 5.1.40sp1 changelog.

[7 Dec 2009 16:48] Paul DuBois

Already fixed in 5.1.x.

[8 Dec 2009 9:29] Bugs System

Pushed into 5.1.43 (revid:[e-mail]-20091208092611-pbno5awyb0v38hs7) (version source revid:[e-mail]-20091208092611-pbno5awyb0v38hs7) (merge vers: 5.1.43) (pib:13)

[16 Dec 2009 8:38] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20091216083311-xorsasf5kopjxshf) (version source revid:[e-mail]-20091215065750-5m04ogppd5l0pol5) (merge vers: 6.0.14-alpha) (pib:14)

[16 Dec 2009 8:45] Bugs System

Pushed into 5.5.0-beta (revid:[e-mail]-20091216082430-s0gtzibcgkv4pqul) (version source revid:[e-mail]-20091211070127-kl8uvlrv9cr11kva) (merge vers: 5.5.0-beta) (pib:14)

[16 Dec 2009 8:51] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20091216083231-rp8ecpnvkkbhtb27) (version source revid:[e-mail]-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)

[18 Dec 2009 10:29] Bugs System

Pushed into 5.1.41-ndb-7.1.0 (revid:[e-mail]-20091218102229-64tk47xonu3dv6r6) (version source revid:[e-mail]-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

[18 Dec 2009 10:45] Bugs System

Pushed into 5.1.41-ndb-6.2.19 (revid:[e-mail]-20091218100224-vtzr0fahhsuhjsmt) (version source revid:[e-mail]-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

[18 Dec 2009 11:00] Bugs System

Pushed into 5.1.41-ndb-6.3.31 (revid:[e-mail]-20091218100616-75d9tek96o6ob6k0) (version source revid:[e-mail]-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

[18 Dec 2009 11:14] Bugs System

Pushed into 5.1.41-ndb-7.0.11 (revid:[e-mail]-20091218101303-ga32mrnr15jsa606) (version source revid:[e-mail]-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)

[12 Mar 2010 14:10] Bugs System

Pushed into 5.1.44-ndb-7.0.14 (revid:[e-mail]-20100312135944-t0z8s1da2orvl66x) (version source revid:[e-mail]-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)

[12 Mar 2010 14:26] Bugs System

Pushed into 5.1.44-ndb-6.2.19 (revid:[e-mail]-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:[e-mail]-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)

[12 Mar 2010 14:40] Bugs System

Pushed into 5.1.44-ndb-6.3.33 (revid:[e-mail]-20100312135724-xcw8vw2lu3mijrhn) (version source revid:[e-mail]-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)