Computers don't store values in base-10 fractions when they use doubles or floats; they use base-2 fractions. This causes that your simple base-10 fractions can't be stored exactly.
This is also why those other -not lite- engines have a decimal type, processed using much slower integer logic coded in their engines... but this doesn't make the current floating point arithmetic broken in any way. Bert From: Scott Doctor Sent: vrijdag 23 oktober 2015 09:30 To: General Discussion of SQLite Database Subject: Re: [sqlite] Simple Math Question Something sounds wrong with this floating point issue. His example consists of two digit numbers with simple addition. The number 1.7 would be represented by the value 17 and an exponent of -1, which is an exact number, same with his other numbers. His math operations should give exact results since the operations are simple addition. Since he is not doing any multiplication or division, there should not be any resolution issues. His results using his simple equations should give exact answers. So something is not handling the numbers properly if he is not getting exact results. If the claims about floating point results in sqlite are as stated in this thread of messages then floating point should be entirely avoided and eliminated from sqlite as this implies that floating point operations are improperly implemented rendering useless and incorrect results. ------------ Scott Doctor scott at scottdoctor.com On 10/23/2015 12:14 AM, Dominique Devienne wrote: > On Thu, Oct 22, 2015 at 11:55 PM, Igor Tandetnik <igor at tandetnik.org> > wrote: > >> On 10/22/2015 4:25 PM, Rousselot, Richard A wrote: >> >>> FWIW, MySQL and Oracle both return all yes for that query. >>> >> In MySQL, 9.2 is a literal of DECIMAL type, which is in fact represented >> as a finite decimal fraction. SQLite doesn't have such a type. You would >> likely observe similar results in MySQL if you write your constants like >> this: 9.2e0 (scientific notation makes them have FLOAT or DOUBLE type). >> > And oracle's NUMBER can be up to 20 bytes (21 unsigned), stores number is > base-100, i.e. each byte represents 2 base-10 digits. > http://www.orafaq.com/wiki/Number > > SQLite only uses IEEE double, which often cannot represent accurately even > small (as in text) numbers with a decimal point. You can use Oracle's > BINARY_DOUBLE to force it using an IEEE double. FWIW. --DD > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
