I fully expected something like this from a fractional number like 1/3 but didn't realize that this would be the case if when the digits were explicitly stated and they summed to a whole number. I will use a Cast statement to resolve.
FWIW, MySQL and Oracle both return all yes for that query. Thanks to everyone that replied. Richard -----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Stephan Beal Sent: Thursday, October 22, 2015 2:57 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Simple Math Question On Thu, Oct 22, 2015 at 9:45 PM, Rousselot, Richard A < Richard.A.Rousselot at centurylink.com> wrote: > Doing the following math, why is it that the results are not all > returning "yes"? > > SELECT > (9.2+7.9+0+4.0+2.6+1.3), > case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else > "no" end, > (9.2+7.8+0+3.0+1.3+1.7), > case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else > "no" end, > (9.2+7.9+0+1.0+1.3+1.6), > case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else > "no" end FROM > sometable; > Try the same with integers as you'll likely see different results. Floating-point math is fraught with problems when it comes to expecting exact results at a specific precision. See: http://floating-point-gui.de/ the first example of which demonstrates the problem you are seeing. -- ----- stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments.