Floating Point Types comparisons

Question

I have inserted diff values of pi (see below):

3.14 
3.1415 
3.14159
3.14159265359 

I do not see the different in how the different floating point types handle the same values.

Code:

mysql> select * from test_types;
+---------+---------+---------+----------+
| flo     | dub     | deci    | noomeric |
+---------+---------+---------+----------+
| 3.14000 | 3.14000 | 3.14000 |  3.14000 |
| 3.14150 | 3.14150 | 3.14150 |  3.14150 |
| 3.14159 | 3.14159 | 3.14159 |  3.14159 |
| 3.14150 | 3.14150 | 3.14150 |  3.14150 |
| 3.14159 | 3.14159 | 3.14159 |  3.14159 |
+---------+---------+---------+----------+
5 rows in set (0.00 sec)

mysql> describe test_types;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| flo      | float(10,5)   | YES  |     | NULL    |       |
| noomeric | decimal(10,5) | YES  |     | NULL    |       |
| deci     | decimal(10,5) | YES  |     | NULL    |       |
| dub      | double(10,5)  | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

I can see here that when creating the table the field with numeric type used DECIMAL (see describe command table).

Does anybody know an example showing differences between FLOAT, DECIMAL and DOUBLE please?

Answer 1

FLOAT and DOUBLE are meant for very small values or very large values.

Essentially they are the same thing (except differ in storage size FLOAT 4 bytes against DOUBLE 8 bytes, see Data Type Storage Requirements)

The main thing about them is that they are approximate (see quoted from Oracle website):

Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies.

DECIMAL allows for an exact representation but the reason your DECIMAL column did not work for PI very well is because you allowed for only 5 decimal places but then you fed it 11 decimal places.

The best way to store the value of PI accurate to 11 decimal places is something like DECIMAL(12,11).

For an actual example for values being treated differently when stored as DECIMAL as opposed to same value being stored and used as a FLOAT see below:

CREATE TABLE decimal_vs_float_test
( dec DECIMAL(12,11)
, fl FLOAT
);

INSERT INTO decimal_vs_float_test VALUES
  ( 3.947947949 , 3.947947949 )
 ,( 3.777777777 , 3.777777777 )
 ,( 3.555555555 , 3.555555555 )
 ,( 3.333333333 , 3.333333333 )
 ,( 3.111111111 , 3.111111111 ) 
 ;

SELECT * FROM decimal_vs_float_test WHERE fl = dec

Now you can see the values for a DECIMAL or a FLOAT treated differently.

Hope that helps.

Additionally FLOAT and DOUBLE are floating binary point types whereas DECIMAL is a floating decimal point type.

See this answer for more exact details on what that means, the difference between how the types are encoded and when is best to use what type (its meant for C# but its still interesting).