On 23 October 2015 at 16:08, Dominique Devienne <ddevienne at gmail.com> wrote:
> Another good good way to think of IEEE I was presented once with, and which > kind of gave me a ah-ah moment, is the fact that numbers with exact > representation fall on the nodes of grid, and there's plenty of "space" in > between the nodes for values which cannot be exactly represented. The > "spacing" of the grid varies depending on the magnitude of your number > (i.e. the exponent) and the size of the mantissa (i.e. how small your 1/2^i > fractions get). IEEE is complex... I'm not an expert, but hopefully the > above helps. --DD > Very true, and a corollary is that using a fixed tolerance for floating point comparisons (like 1e-14 suggested elsewhere in this thread) doesn't work in general. It's fine if you know the magnitude of the numbers you're comparing, but once you get to numbers above 100, abs(f1 - f2) < 1e-14 becomes the same as saying f1 == f2, because even a single bit of error at this magnitude will be larger than 1e-14. Some languages provide a way to determine the distance between a given floating point value and the next largest representable number (eg the Math.ulp function in java), but I'm not sure if SQL provides such? Or if its even in scope... -Rowan
