Bug #27986 Long Decimals from sub-queries used in updates give wrong results

• Submitted: 20 Apr 2007 15:20
• Modified: 7 Apr 2008 8:36
• Reporter: Giles McArdell
• Status: Can't repeat
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.0.27, 5.1
• OS: Linux
• Assigned to: Alexey Botchkov
• CPU Architecture: Any
• Tags: decimal, subquery

[20 Apr 2007 15:20] Giles McArdell

Description:
In the example provided the results of the equations are all very close to 1, and normally get rounded to 1 when placed into a column with fewer decimal places than the result.
In some cases this is what happens, in others a the number 0.000000001000 is placed in the column.

Note that this only happens when the SUM aggregate is used.

How to repeat:
CREATE TABLE IF NOT EXISTS MathTest (a DECIMAL(19,12),b DECIMAL(19,12),c DECIMAL(19,12), d int) ENGINE = MEMORY;

INSERT MathTest 
SELECT 1.0 a, 99.999999999982 b, 0 c, 1 d UNION SELECT 1.0,100.000000000021,0, 2;

SELECT SUM(a * (b / 100)) c FROM MathTest GROUP BY d;

UPDATE MathTest , (SELECT SUM(a * (b / 100)) SUMc, d FROM MathTest GROUP BY d) SNOW
SET MathTest.c = SNOW.SUMc WHERE MathTest.d = SNOW.d;

SELECT * FROM MathTest;

DROP TABLE IF EXISTS MathTest;

Suggested fix:
I found using a CAST(.. AS DECIMAL) around the SUM() seems to fix the problem IE:
UPDATE MathTest , (SELECT CAST(SUM(a * (b / 100)) AS DECIMAL) SUMc, d FROM MathTest GROUP BY d) SNOW
SET MathTest.c = SNOW.SUMc WHERE MathTest.d = SNOW.d;

[20 Apr 2007 18:15] Sveta Smirnova

Thank you for the report.

Verified as described. Versions 5.0 and 5.1 are affected.