Comparing Integers and Doubles
10 days ago
- #database
- #comparison
- #precision
- Transitive comparisons can fail in systems when comparing different numeric types due to precision loss.
- Example SQL query shows a case where a=b and a=c are true, but b=c is false due to double precision limitations.
- Hash joins and optimizers can behave differently due to these comparison issues, leading to inconsistencies.
- A correct comparison method involves converting int to double, checking equality, and handling precision loss.
- Python and SQLLite handle these comparisons correctly, while many other systems and languages do not.
- A proposed function `cmpDoubleInt64` provides a solution for accurate int/double comparisons.