[MCOL-249] wrong decimal sum when using FLOAT type Created: 2016-07-05  Updated: 2016-08-16  Resolved: 2016-08-16

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: 1.0.2
Fix Version/s: 1.0.0

Type: Bug Priority: Major
Reporter: Xrizoo Assignee: Andrew Hutchings (Inactive)
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

OpenSUSE 42.1 (act as a server)


 Description   

To reproduce the error, build this table as example:

{{CREATE TABLE `test` (
`FACTURA` CHAR(20) NULL DEFAULT NULL,
`FECHA` DATE NULL DEFAULT NULL,
`IMPORTE` FLOAT NULL DEFAULT NULL
);}}

It only need a 1 record, but you must use any. Use this for example:

{{INSERT INTO `test` (`FACTURA`, `FECHA`, `IMPORTE`) VALUES
('BE6141', '2016-06-14', 877.8);
}}

Finally, run this query:

{{select factura, CONCAT_WS(',',FECHA) as FECHA, SUM(importe) AS IMPORTE
from test
group by factura;
}}

The result is:
------------------------------------
| factura | FECHA | IMPORTE |
------------------------------------
| BE6141 | 2016-06-14 | 877.7999877929688 |
------------------------------------
1 row in set (0.00 sec)

As you can see = 877.7999877929688 <> 877.8

 Comments   
Comment by Xrizoo [ 2016-07-05 ]

Finally, i found that the problem was other. The query returns the same wrong error without the function concat_ws().
I've changed the FLOAT to DOUBLE and everything works find. So, the issue is the FLOAT type

Comment by Andrew Hutchings (Inactive) [ 2016-08-16 ]

This isn't just a ColumnStore issue, it happens in all engines and is expected behaviour.

The FLOAT and DOUBLE data types use approximate storage for the values, the same as float/double in most programming languages. There is a display precision by default when running a basic query but using 'SUM()' passes the float through a function and therefore will not use the display precision.

It is recommended if you wish to use accurate floating point storage to use the DECIMAL data type.

Comment by Justin Swanhart (Inactive) [ 2016-08-16 ]

More info can be found here:
https://dev.mysql.com/doc/refman/5.5/en/floating-point-types.html
and
https://dev.mysql.com/doc/internals/en/floating-point-types.html

Generated at Thu Feb 08 02:19:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.