5

When casting 1 to decimal in MySQL, I was expecting 1.0000, but instead the output is 0.9999.

What exactly is happening here?

SELECT CAST(1 AS DECIMAL(4,4))
Zack
  • 3,819
  • 3
  • 27
  • 48
  • 3
    Possible duplicate of [Is floating point math broken?](http://stackoverflow.com/questions/588004/is-floating-point-math-broken) – tkausl Aug 28 '16 at 03:24
  • 2
    Because you specified that there will be four digits after the decimal point. – 1000111 Aug 28 '16 at 03:25
  • 1
    @tkausl not a duplicate, that question has nothing to do with MySQL. – Zack Aug 28 '16 at 03:26
  • 1
    Look at this http://dev.mysql.com/doc/refman/5.7/en/fixed-point-types.html. With `Decimal(4,4)` the range of values would be `-0.9999 to 0.9999` – 1000111 Aug 28 '16 at 03:27
  • Shouldn't this be an overflow? I just tested on SQL Server and got that as my error. – shawnt00 Aug 28 '16 at 03:28
  • @1000111 ah that makes sense, 4 precision and 4 scale. So to get 1.0000 I need 5,4. – Zack Aug 28 '16 at 03:30
  • 1
    Exactly! `Decimal(x,y)` will reserve `(x minus y)` digits before the decimal and `y` digits after the decimal. – 1000111 Aug 28 '16 at 03:31
  • @shawnt00 this is for MySQL, not SQL. – Zack Aug 28 '16 at 03:31
  • Postgres `ERROR: numeric field overflow Detail: A field with precision 4, scale 4 must round to an absolute value less than 1.` – shawnt00 Aug 28 '16 at 03:35
  • Seems to work on SQLite though. – shawnt00 Aug 28 '16 at 03:36
  • SQL and SQLite are not MySQL. They are different databases all together, so their implementation of the query syntax will vary. – Zack Aug 28 '16 at 03:37
  • `ORA-01438: value larger than specified precision allowed for this column` – shawnt00 Aug 28 '16 at 03:38
  • @ZackTeater I'm well aware of the various platforms. This is probably a departure from SQL standards which is why I was going to the trouble of investigating all that. There could be a setting that impacts the way values are rounded though and I don't know enough about MySQL to say for sure. – shawnt00 Aug 28 '16 at 03:40
  • 1
    @tkausl This doesn't really even involve floating point math so it's not a duplicate of that question at least. Of course the concept of floating point certainly applies to it very much when using those types. – shawnt00 Aug 28 '16 at 03:42
  • @shawnt00 retracted my close vote. Looked like floating point math on the first glance though. – tkausl Aug 28 '16 at 03:44
  • 1
    @shawnt00 MySQL does throw a warning with this query `1 row in set, 1 warning (0.00 sec)`. Some broken clients seem to ignore warnings (What could possibly go wrong when they do that? I digress.) but `SHOW WARNINGS;` would reveal the explanation; with `@@SQL_MODE` including `STRICT_TRANS_TABLES`, MySQL, MySQL should not allow this value to actually be inserted into a table. – Michael - sqlbot Aug 28 '16 at 04:26
  • `1.0000` requires `DECIMAL(5, 4)`, not `DECIMAL(4, 4)`. – Bohemian Aug 28 '16 at 05:59

3 Answers3

5

MySQL is clipping the converted value to the largest one that will fit in the target type. A decimal(4, 4) doesn't allot any significant digits to the left of the decimal place.

This cast does not cause overflow only when the server is not running a strict mode as described in the documentation.

shawnt00
  • 16,443
  • 3
  • 17
  • 22
3

out of range

For example, DECIMAL(5,2) store any value with five digits and two decimals, its range from -999.99 to 999.99.

SELECT CAST(1 AS DECIMAL(5,4)) -> 1.0000
SELECT CAST(1 AS DECIMAL(4,3)) -> 1.000
SELECT CAST(0.0001 AS DECIMAL(4,4)) -> 0.0001
SELECT CAST(0.00001 AS DECIMAL(4,4)) -> 0.0000
SELECT CAST(12345 AS DECIMAL(5,4)) -> 9.9999

More info:

https://dev.mysql.com/doc/refman/5.7/en/precision-math-decimal-characteristics.html https://dev.mysql.com/doc/refman/5.7/en/fixed-point-types.html

Anthony Zhan
  • 861
  • 8
  • 11
1

As you are trying to convert an integer (In your case its value is 1) into Decimal number DECIMAL(n,m) that have precision n i.e. total number of digit (n=4 in your case) and scale as m i.e. number of digits after decimal (m=4 in your case)

So In your case decimal number will range from (-0.9999 to 0.9999)

And so when you try to convert any integer > 0 it will be converted as 0.9999(Maximum possible value)

and when you try to convert any integer < 0 it will be converted as -0.9999(minimum possible value)

this kind of conversion will cause an error if server run in strict mode.

Sandesh Jain
  • 704
  • 3
  • 13