CodeBlog.ch

The fact that computers aren’t perfect at calculation is something most developers know. 1/3 can’t be saved as a floating point number, it would be infinitely long, 0.3333… If you aren’t aware of that, check out this site, it explains the problem quite nicely http://floating-point-gui.de/.

Most people who work with SQL rarely come across this problem, but there’s one pitfall which in my opinion is even more dangerous, especially since SQL handles this problem in most cases quite well.

Let’s start by creating a dummy table and insert a row with two numbers:

DROP TABLE IF EXISTS mathtest;
CREATE TABLE mathtest (num_dec DECIMAL(20, 10), num_float FLOAT);
INSERT INTO mathtest VALUES (2018.446510036496, 2018.446510036496);

Now that we have some data to work with, let’s query our table and add two columns with a static value.

SELECT
	round(num_float, 9) table_float,
	round(num_dec, 9) table_decimal,
	round(2018.446510036496, 9) static_float,
	round(CAST(2018.446510036496 AS DECIMAL(20,10)), 9) static_decimal
FROM mathtest;

What will the result be of this? The first table_float is pretty obvious, it’s very imprecise, but what about the others? Let’s have a look:

What does this tell us? It’s simple, when you query data from a table, it will use the precision of the column type. But when you do some arithmetic in an SQL query, it will use floats by default and thus be imprecise. If we cast it to a decimal, we can get a precision of 64 digits.

Not a big deal, but make sure you’re aware how you do your calculations in SQL!