FLOAT datatype behaviour
Posted by: J Verbree
Date: January 16, 2019 01:29PM

I have a few questions regarding the behaviour of FLOAT values. In the documentation about the float datatype is said that FLOAT support an optional format, like:

FLOAT(M,D)

- M - precision, the total number of digits.
- D - Scale, the number of digits that can be used of M for number after the decimal symbol "."

Consider the following:

- mysql> CREATE TABLE testing (value FLOAT);
- mysql> INSERT INTO testing (value) VALUES (9999);
- mysql> SELECT * FROM testing;
- Result:

+-------+
| value |
+-------+
| 9999 |
+-------+

The result is as expected, I defined no precision or scale or so.
Now lets see what happens I would introduce a float with a precision (max number of digits)
I use a precision of 4 (the number can use up to 4 digits) and a scale of
(2 two numbers of P will be used as digits after the decimal)

- mysql> ALTER TABLE testing MODIFY value FLOAT(4,2);
- mysql> SELECT * FROM testing;
- Result:

+---------+
| value |
+---------+
| 9999.00 |
+---------+

This result seems a bit strange to me, yes has the specified scale of 2 decimals digits, but the precision is out or range,
The number is longer than the total of 4 digits. I would have expected a value like 99.00 or 99.99 but not 9999.00, Its seems to
me that the specified precision is not a hard limit, simply because the value 9999.00 is out of range. how does this work?

Let me try to insert a value that does not meet the specified requirements. for example: 100.00

- mysql> INSERT INTO testing (value) VALUES (100.00);
- Result:

ERROR 1264 (22003): Out of range value for column 'value' at row 1

This confirms the value is limited by the precision and scale parameters I specified earlier. So why is it possible for the column
to hold value like 9999.00 when the definition of the column is FLOAT(4,2) (after changing the column definition)


Let see what happens when only specify precision and not a scale:

- mysql> ALTER TABLE testing MODIFY value FLOAT(4);
- mysql> SELECT * FROM testing;
- result:

+-------+
| value |
+-------+
| 9999 |
+-------+

This seems OK to me, we only specified the precision and set to 4, so this value makes sense. What happens if I try
to insert again a value that is out of range (precision >4):

- mysql> INSERT INTO testing (value) VALUES (12345678);
- mysql> SELECT * FROM testing;
- Result:

+-----------+
| value |
+-----------+
| 9999 |
| 123456000 |
+-----------+

I would have expected 1234 or at least 12340000, but why is the value "truncated" after six digits while the precision is set to 4?

So my questions are:

1. Why is it possible for the column to hold value like 9999.00 when the definition of the column is FLOAT(4,2) (after changing the column definition, see given example)
2. It seems to me that the specified precision is not a hard limit, simply because the value 9999.00 is out of range. how does this work? (see given example)
3. Why is the value "truncated" after six digits while the precision is set to 4 when inserting the value 12345678? (see given example)

System specs:

- Server version: 5.6.42 MySQL Community Server (GPL)
- OS: Ubuntu

- SQL modes:

mysql> SELECT @@sql_mode;
+--------------------------------------------+
| @@sql_mode |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+

Options: ReplyQuote


Subject
Written By
Posted
FLOAT datatype behaviour
January 16, 2019 01:29PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.