Can someone give an example of a time you would actually want to compare floats for equality? Every time I've come across it there's always been a better way.
- The representable values are so dense near zero that there will be less practical difference between strict equality and approximate equality. Strict equality would be theoretically significant if you found it, but not really necessary to check for. You'll see mostly false negatives.
- The representable values are so sparse far away from zero that strict equality isn't really valuable theoretically or practically. You'll see mostly false positives.
So perhaps there is a region (the location and size of it depending on what you're modelling), where strict equality is just likely enough that you need to check for it, AND that the ULP at that point is meaningful for your application. But philosophically, in that situation, are you really comparing them for equality, or does your epsilon just happen to be zero? Would it be better to interpret your question as: are there situations where you want to check for strict equality no matter what either of the values are?
Things which are genuinely representable as a float value testing for equality is very rarely useful.
If you think about it, people use 64 bit doubles for representing latitude and longitude. They don't have a lot of precision. A second of a degree is about 25-30 meters (depending on where you are. that's 1/3600th of a degree. So five decimal points gets you there. With 64 bit numbers, you can probably get to cm/mm level accuracy. Which is plenty considering GPS is not accurate to more than about 5 meter.
And distance algorithms are tricky too; they are not that exact. The earth isn't a perfect sphere.
So, you are dealing with an inexact representation of doubles that weren't very exact to begin with and the algorithms add more errors. The solution is to not compare equality but distance with some margin of error. So, subtract your expected and actual values and assert that their difference should be less than whatever margin of error is acceptable to you.
SELECT foo_id, foo.some_float, SUM(bar.some_thing)
FROM foo JOIN bar USING (foo_id)
GROUP BY foo_id, foo.some_float
Though, I would guess the optimizer (at least in postgres) is smart enough to ensure no float equality checks actually happen under-the-hood. They could be necessary, if the schema was different than I'm imagining; but maybe in that case, it would almost always be a bad idea.
Though I have seen some people using a double as a primary key (no idea why) and some database engine (internal, not major vendor) failing to do equality comparisons in certain statements, I suspect because they must be switching to "close enough" which is not what you expect when you write col1 = col2.
I've always liked the way MySQL/MariaDB let you omit things from the GROUP BY if they're provably unique in each group (here, if foo_id is a primary key of foo, and you're grouping by it, there can only ever be one foo.some_float for each foo_id).
I suspect in practice this would get rid of approximately all occurrences of group-by-float.
When they are integers. By construction, floats provide exact arithmetic for small ints. It makes sense to compare those for equality.
Also, when defining a ``sign'' function, you may want to treat the equality case separately, to avoid introducing biases when the input data is quantized.
If you do exact comparisons for any non-trivial cases, you'll find different compilers, optimization settings, runtimes, and processors give different results.