English

Problem: Unexpected result returned when binding FLOAT32 data type in a dynamic WHERE clause constraint

Description

When a FLOAT32 data type is used in an SDE dynamic WHERE clause constraint, the query could return unexpected results.

In the following example, the variable float_val is bound dynamically to the WHERE clause in the query and the constraint is "SE_FLOAT_TYPE > ?". When the query is executed, 0.03 is also returned even though the constraint asked for values greater than the variable.

(1) Check the contents of the table to see what values are present.

Code:
SQL> select * from qa_cq00238016;

SE_FLOAT_TYPE
-------------
.01
.02
.03
.04
.05
.06
.07
.08
.09

9 rows selected.


(2) Execute the query using SDE dynamic binding.

Code:
float_val = 0.03;
where = "SE_FLOAT_TYPE > ?"


The query results in the following fetched rows:

0.030000
0.040000
0.050000
0.060000
0.070000
0.080000
0.090000

Cause

This unexpected result is not a defect of SDE, but is caused by the nature of floating point data types: Floating point math is not an exact arithmetic; different compilers and CPU architectures store temporary results at different precisions, which could lead to different floating point comparison results.

For instance, in the following simple IF statement:

Code:
if (result = = expectedResult)


it is unlikely that the comparison will be true. If the comparison is true, it is a very unstable condition—changes in the input values, compiler, or CPU may render the comparison false.

Solution or Workaround

It is generally not a good idea to check for equality of two floating point values. If you really need to use FLOAT32 in the constraint, avoid equality checking, and use a range instead.