For purposes of given math module (that performs some statistical computations) it's sufficient to store reals in IEEE754 floating point format. I'm considering to migrate to SQLLite as persistent storage. But documentation of SQLLite doesn't clearly state what standard it supports. SQLLite wiki says it supports REAL:

REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.

But there are two different IEEE standards for floating-point computation. IEEE 754 (b = 2) and IEEE 854(b = 2 or b = 10). I need to store IEEE 754 single precision float or double in SQLLite column and later read this value into variable with original precision (single or double) in IEEE 754 format (no need any arithmetic).

Two sub-problems appear: one about storing doubles and another about storing single precision values in target unknown 8-byte floating point number.

  1. storing doubles: can I be confident that I will read the same binary value (754 has unique binary representation), including all kinds of NaN, pos/neg infinity, etc. (in other words: is SQLLite lossless with regard to IEEE754 doubles)?

  2. storing single float to double: is this conversion reversible? This problem is about IEEE754 standard in general and doesn't relate to any specific software.

This question is not only about math-software and SQLLite - it's about representation of floats in different formats and losses related to conversions. Useful introduction article about this topic here - What Every Computer Scientist Should Know About Floating-Point Arithmetic


closed as off-topic by quid, achille hui, Thomas Andrews, Winther, Antonio Vargas Apr 30 '16 at 0:30

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "This question is not about mathematics, within the scope defined in the help center." – quid, achille hui, Thomas Andrews, Winther, Antonio Vargas
If this question can be reworded to fit the rules in the help center, please edit the question.

  • $\begingroup$ This is not a mathematical question. You may want to ask this on another site, I am not sure which one is best suited though. $\endgroup$ – quid Apr 29 '16 at 23:32
  • 1
    $\begingroup$ This is a programming question. It probably belongs on stackoverflow but it certainly doesn't belong here. $\endgroup$ – or1426 Apr 29 '16 at 23:32
  • $\begingroup$ SQLLite is hand-written in C (or C++) by one guy, and it uses the file API as database, hence real are C double $\endgroup$ – reuns Apr 29 '16 at 23:35
  • $\begingroup$ It would be great that they understand this question... $\endgroup$ – Kaponir Apr 29 '16 at 23:35
  • $\begingroup$ If you can not deduce it from the source code why not try to set up some run time tests? It might even behave different on different systems. $\endgroup$ – mvw Apr 29 '16 at 23:36

The answer is, you have encountered a documentation deficiency in the SQLLite wiki. They should have specified an 8-byte IEEE floating point number following the standard of IEEE 754 for double precision numbers. This is how all the data bases behave; implementers just assumed that this was the behavior dictated by the standard, and indeed the "standard" writers meant this behavior.

Don't think harshly of the writers of the wiki. I've been involved in the C++ standard, and OMG it is hard to get everything perfectly right!

But the answer to your real question, concerning NANs and there brethren, is that implementers will have felt free to ignore that issue or invent their own way of comping with division by zero and the like. So I would not count, for example, on reproducing the NAN you have sent into the database.

  • $\begingroup$ I have to disagree it is a deficiency of SQLLite wiki. Most people who know what an IEEE double is know what it means. $\endgroup$ – achille hui Apr 29 '16 at 23:43
  • $\begingroup$ Confusing: wiki says : SQLite4 does all numeric computations using decimal arithmetic. SQLite4 never uses C datatypes double or float (except in interface routines when converting between double and the internal decimal representation). Instead, all numeric values are represented internally as an 18-digit decimal number with a 3-digit base-10 exponent. $\endgroup$ – Kaponir Apr 29 '16 at 23:50
  • $\begingroup$ Probably storing double as decimal is not a loss - article I gave link says: Theorem 15. When a binary IEEE single precision number is converted to the closest eight digit decimal number, it is not always possible to uniquely recover the binary number from the decimal one. However, if nine decimal digits are used, then converting the decimal number to the closest binary number will recover the original floating-point number. $\endgroup$ – Kaponir Apr 29 '16 at 23:55
  • $\begingroup$ So probably sufficient number of decimal digits should preserve double - that is recover the same. $\endgroup$ – Kaponir Apr 29 '16 at 23:57
  • $\begingroup$ It seems SQLLite doesn't follow the standard of IEEE 754 - they insert NULL instead of NaN. $\endgroup$ – Kaponir Apr 30 '16 at 0:14

Not the answer you're looking for? Browse other questions tagged or ask your own question.