0

I have an application that utilizes NHibernate to handle database functionality. Now I have a table with a column mapped as a float. NHibernate created the table in the MySql database, and I have validated the column to be "FLOAT" as well. NOTE: There is not FLOAT(M<,D) precision defined in the column, it's just "FLOAT".

Partial SQL table create statement:

CREATE TABLE `X` (
    `TresholdLow` float NOT NULL,

Now when I try to insert the domain model (POCO) with a float property of value 5.46, for example, all goes well, so the domain model is mapped properly and is working.

But when I try to insert the domain model with the property set at float.MinValue resulting in a value of 3.40282347E+38 I get the following error:

ERROR 1264: Out of range value for column 'TresholdLow' at row 1

I think it might have to do something with the following. This is input (marked by ?) and output of the immediate window in Visual Studio:

?float.MinValue.ToString();
"-3.402823E+38"
?float.MinValue
-3.40282347E+38

The first value IS accepted by MySQL, the latter ISN'T!!

How do I work around this issue? Practically it won't be a problem, but theoretically it is not so nice to have a database float column not accepting all possible float values coming from the application that is storing the data.

carols10cents
  • 6,943
  • 7
  • 39
  • 56
Mike de Klerk
  • 11,906
  • 8
  • 54
  • 76
  • How are you trying to insert the value? Using a string, or via parameterized SQL? – Jon Skeet Sep 16 '13 at 07:59
  • @JonSkeet (Off topic: Nice to have your attention on this question :) ). I was hoping not to be bothered with that level, as NHibernate takes care of saving the POCO to database. I think NHibernate uses parameterized SQL. The SQL string, caught in `public override NHibernate.SqlCommand.SqlString OnPrepareStatement(NHibernate.SqlCommand.SqlString sql)` yields: question marks at the location of the insert values. – Mike de Klerk Sep 16 '13 at 08:07
  • Ah, I hadn't noticed the NHibernate part. Yes, that should be fine then. The string representation you're seeing in the immediate window is almost certainly a red herring, I'm afraid. – Jon Skeet Sep 16 '13 at 08:09
  • Is the type of the property on the POCO `double` or `float`? – Daniel Hilgarth Sep 16 '13 at 08:11
  • 1
    I had to look up `red herring`. The property type in the POCO is of `System.Single` / `float` as its defined like this: `public virtual float TresholdLow { get; set; }` – Mike de Klerk Sep 16 '13 at 08:11
  • Me too. And I am still not sure what exactly it means related to this question... ;-) – Daniel Hilgarth Sep 16 '13 at 08:13
  • To make certain there isn't any rounding in the string representation, what does e.g value.ToString("E10", CultureInfo.InvariantCulture) show? – Oskar Berggren Sep 16 '13 at 08:33
  • @OskarBerggren Executed in immediate window: `?float.MinValue.ToString("E10", System.Globalization.CultureInfo.InvariantCulture) "-3.4028234700E+038"` – Mike de Klerk Sep 16 '13 at 08:47

1 Answers1

1

Since you report that -3.402823E+38 is accepted but -3.40282347E+38 is not, I speculate the problem is that some software is not rounding correctly.

The least finite IEEE-754 32-bit binary floating-point value is not –3.40282347E+38, since that is an approximation. The exact value is –340282346638528859811704183484516925440, which is slightly smaller in magnitude. For brevity, I will use M for –340282346638528859811704183484516925440.

–3.40282347E+38 is between the two representable values M and –infinity. According to the IEEE-754 rules for rounding to nearest, it ought to be rounded to M if it is closer to M than it is to the next value that would continue the finite pattern instead of jumping to infinity. Since –3.40282347E+38 is closer to M than it is to that next number, the result should be M.

However, people do not always write software that rounds correctly. It is conceivable that some software observes that –3.40282347E+38 exceeds M in magnitude and returns –infinity or a range error instead of rounding it correctly.

To work around the problem, I suggest trying -3.402823466E+38. This is less than M in magnitude but is close enough that it should round to exactly M.

Eric Postpischil
  • 195,579
  • 13
  • 168
  • 312
  • The MySQL documentation does state (in a user-supplied comment) that the smallest possible value for FLOAT is indeed -3.402823466E+38. The PostgreSQL documentation does not state a minimum value and seems to imply that the details may vary depending on platform. The documentation for MS SQL server states the minimum value as -3.40E+38, which do seem likely to be rounded compared to the true minimum value. – Oskar Berggren Sep 17 '13 at 07:22
  • I see, `-3.4028234**7**E+38` is rounded, seems an illogical approach from Microsoft to round it like that if the official value is `M` and their rounded value is greater than `M` (refering to `M` from your answer). I can't use `float.[Min/Max]Value` then to determine whether values are in range. Solutions: 1. Of course I can define a global constant, but the problem is, it doesn't speak for itself, other developers might just use float.MinValue. 2. Extensions? Only works for methods, not for properties, and only on instances, not on types. 3. Obsolete attribute? Can't add that to System.Single – Mike de Klerk Sep 17 '13 at 07:31
  • Thanks for clarifying it with IEEE-754, I found the document here: http://www.eecs.berkeley.edu/~wkahan/ieee754status/IEEE754.PDF Could you point out where you obtained the value of `M` from? I only find formula's and such in the document linked above. Its really technical, I am eager to learn. Thanks for your answer. – Mike de Klerk Sep 17 '13 at 07:40
  • Microsoft sometimes does conversion from `string` to `float` via `double`; if in the process of going from the computer's `float` to the database representation a number gets converted to `string` and then `double`, the resulting `double` would be less than the minimum legal `float` value. The code for Conversion to `float` might check against that limit and squawk if the value is out of range. – supercat Sep 17 '13 at 15:04
  • @MikedeKlerk: In IEEE-754 64-bit binary format, the largest exponent (of 2) for a finite number is 1023. This is because the field for the exponent encoding has 11 bits, the 2047 value is reserved for infinities and NaNs, and the 2046 value is biased by 1023, so the represented exponent value is 2046–1023 = 1023. The significand is 53 bits (encoding in a 52-bit field with a leading bit determined by other data), so its largest value is “1.111…1” in binary, where there are 53 bits, and this is 2 – 2**–52. Thus the largest representable finite magnitude is 2**1023 • (2 – 2**–52). – Eric Postpischil Sep 17 '13 at 15:17
  • @EricPostpischil Gee thanks! That is a most useful explanation! – Mike de Klerk Sep 18 '13 at 06:10