You are thinking (and typing) in base 10. Computers use this new-fangled thing called binary -- base 2. The "floating point" type is IEEE 754 double precision binary (base 2) floating point.
SQLite does not use decimal floating point (IEEE 854-1987) or General Decimal Arithmetic http://speleotrove.com/decimal/decarith.html as specified by IBM and implemented in many non-lite SQL Engines. > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Scott Doctor > Sent: Friday, 23 October, 2015 01:31 > 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
