Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL type FLOAT should not be used for economical data #12575

Closed
olleharstedt opened this issue Feb 21, 2018 · 10 comments · Fixed by #15994
Closed

SQL type FLOAT should not be used for economical data #12575

olleharstedt opened this issue Feb 21, 2018 · 10 comments · Fixed by #15994
Assignees
Labels
Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc.
Milestone

Comments

@olleharstedt
Copy link

In table log_conversion, revenue has type FLOAT. It should be DECIMAL(11, 2) or similar.

@fdellwing
Copy link
Contributor

Why not?

Links to any studies? Performance tests? Other known problems?

@olleharstedt
Copy link
Author

Precision. 0.1 + 0.2 != 0.3 in float.

@olleharstedt
Copy link
Author

And today I tried to get all conversions with revenue = 0.29, which was not possible in SQL using = 0.29.

@olleharstedt
Copy link
Author

olleharstedt commented Feb 21, 2018

Here's a stackoverflow thread about it: https://stackoverflow.com/questions/13030368/best-data-type-to-store-money-values-in-mysql

Since money needs an exact representation don't use data types that are only approximate like float.

@robocoder
Copy link
Contributor

Looks like 4 decimals places (to accommodate Chile) would suffice. https://en.wikipedia.org/wiki/ISO_4217

@sgiehl
Copy link
Member

sgiehl commented Mar 30, 2018

We can't address this issue prior Matomo 4.0, as we try to avoid any schema changes to log tables in minor releases...

@sgiehl sgiehl added this to the 4.0.0 milestone Mar 30, 2018
@tsteur
Copy link
Member

tsteur commented Feb 20, 2020

Note: We'll only change it for new installs and create an FAQ for existing installs. For more details see https://github.com/matomo-org/matomo/pull/15408/files#r367739574

@sgiehl
Copy link
Member

sgiehl commented Mar 2, 2020

@tsteur can we close this issue, now as #15408 is merged, or shall we keep it open as reminder for anything?

@tsteur
Copy link
Member

tsteur commented Mar 2, 2020

@sgiehl there are still some floats in other tables like log_conversion which we may want to change. Could also check there if DECIMAL(X,Y) works nicely for new installs meaning that it won't automatically convert a 5 to a 5.00 when inserting such a record and then selecting these values aren't automatically converted to 5.00 (but I think it does). We would then use double instead I guess. I suppose float might be too small for some currencies.

@tsteur tsteur added the Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc. label May 27, 2020
@tsteur tsteur self-assigned this May 27, 2020
@olleharstedt
Copy link
Author

double still uses floating-point arithmetic.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc.
Projects
None yet
Development

Successfully merging a pull request may close this issue.

5 participants