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.

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

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)

Solution or Workaround