Equivalent Comparison Failures with Floating-Point Numbers

Possible Causes

Equivalent comparison of floating-point numbers is a common problem. In computing, floating-point numbers are stored as approximate values instead of exact values. Therefore, unexpected problems may occur during equivalent comparison and mathematical operations.

In GaussDB(for MySQL), FLOAT and DOUBLE are floating-point types. The following figure shows an example for equivalent comparison using floating-point numbers:

Solution

1. Decide on an acceptable tolerance for differences between the field and the value and then do the comparison against the tolerance value. For example:

   

2. Use the fixed-point number type (DECIMAL) to replace the floating-point number type. Example: