Double type and Decimal type, which is suitable for monetary value?

Question

I found here in the SO a question with an answer until well accepted by the community What kind of data (double, float or decimal) should I use to represent currency in . NET with C#?

I just never had a problem with double for monetary calculations, I did have float this one already left me standing hair, so from this reply stating that Decimal is correct, or this is just p/ who uses C# and nothing to do with Mysql, I started testing it, and left me a little insecure, for example say that I will always store 2 floating point digits, there are cases where the user will "need" (exception) record the value with 4 digits in the bank that in turn will record only the first two, for this reason I feel safer with double.

Question - there is an example source code that shows me this "error" rounding of Double for me to see to believe, or a reference source book, trusted website that signals this recommendation of Decimal use to record monetary values on Mysql?

Obs 1 - The data types I refer to are from Mysql as I also saw a quote from MSDN https://msdn.microsoft.com/pt-br/library/364x0z75.aspx except that it is C# reference and forgive me for MS supporters I need a more "secure" source when I say trusted site ( because I’m talking about Mysql, not SQL Server, . Net, C# etc.)

Obs 2 - Has a link from mysql itself http://dev.mysql.com/doc/refman/5.0/en/fixed-point-types.html my English is not very good, see if I am mistaken, says:

Maximum number of digits for DECIMAL is 65

Maximum number of digits for DECIMAL Is 65

And in the sequence it says

Before Mysql 5.0.3, the Maximum range of DECIMAL values is the same as for DOUBLE

In other words, the biggest DECIMAL is equal to DOUBLE.

Continuing the text of the site

but the actual range for a Given DECIMAL column can be constrained by the Precision or Scale for a Given column

That part I understand is DECIMAL which is limited by precision or I am wrong ?

I will put here a test, I hope someone shows me this "mistake" in the same way (similarly)

CREATE TABLE `teste` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `numero` double NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `test`.`teste` (`numero`) VALUES ('0.1');
INSERT INTO `test`.`teste` (`numero`) VALUES ('99.9');

SELECT sum(numero) FROM teste;

mysql>     SELECT sum(numero) FROM teste;
+-------------+
| sum(numero) |
+-------------+
|         100 |
+-------------+
1 row in set (0.00 sec)

Answer 1

I doubt you’ve ever had problems. I guess you’ve never noticed the problem. Most cases go wrong for a penny. Of course 1 cent errors can turn into thousands of reais when multiplied.

The reason so many people use wrong is precisely because the error is not usually so obvious and in cases where it becomes a huge problem the person does not know how to validate.

In accounting, a penny of error destroys the work. In other activities it can only give damage, often small, or can give process coming from trading partners or government. I won’t go into detail because I’m not a lawyer.

Has a website trusted where it says and he’s Stack Overflow. He has several references in it.

In that reply of mine I organize several links for other questions/answers on the subject. If you follow all the links There you’ll have all the information you need. You can learn that the problem occurs because of the binary representation of numbers that prevents you from representing all numbers accurately.

One reference in Mysql documentation. Test running on Sqlfiddle.

I put in the Github for future reference.

I imagine that everyone who participates in these responses, even if only through votes, agree that the type DECIMAL should be used. I think that would already be several sources and the variety obtained in a place where what is put is validated by their peers, gives a good reliability.

In the link posted in the comment, a person says that the types DECIMAL and NUMERIC are obsolete. Ask him to quote the source. He probably didn’t understand the question or doesn’t know what he’s talking about. Being a moderator does not mean that she understands the subject of the site. To learn more about these guys has a question here on the website.

Some examples of inaccuracy can be found in wikipedia article.

A simple 0.1 is already problematic. I will show in C# because it is the language that works best among mainstream but the "error" is from the processor, and not from the language, this is true for PHP, JS, SQL, etc. Add 0.1 in one double 100 times, it should be 100, right? Because then it’s 99.9999999986?

using static System.Console;
                    
public class Program {
    public static void Main() {
        double x = 0.0d;
        for (var i = 0; i < 1000; i++) x += 0.1d;
        WriteLine(x);
    }
}

Behold working in the ideone. And in the .NET Fiddle. Also put on the Github for future reference.

$x = 0.0;
for ($i = 0; $i < 1000; $i++) $x += 0.1;
echo $x;

Behold working in the ideone. And in the repl it.. Also put on the Github for future reference.

Some things were added later to the question.

If you think the website Microsoft has some information that is unreliable, it is up to you to prove that there is something wrong. If no one else complained, you must be right. If the problem is because the website is from Microsoft and she’s a whole wrong company, so I won’t be able to convince you of anything because you believe in Saci Pererê too.

The citations from the Mysql documentation have no relation to the problem. But at least it shows that the types mentioned above are not obsolete. The type DECIMAL has no inaccuracy, the number you put on it, is the number that will be used, unlike DOUBLE which uses a number approximate to what you tried to store in it. Of course the DECIMAL will have how many decimal places you determine. It is obvious that half a cent cannot be stored in a DECIMAL with only two decimal places. But it is the decision of the programmer to solve this according to the scenario. The DOUBLE changes the value and this occurs independent of the will of the programmer.

Understand the problem by reading everything I’ve been through and you’ll notice that the statement "That’s the highest DECIMAL is equal to DOUBLE" is completely false. As I said, the quotes do not speak of the "rounding problem" because of binary representation.

The guy DOUBLE can even hide a little better the error of FLOAT but at some point it will appear and it is not so difficult.

May be useful: What is the correct way to use the float, double and decimal types?