Suggestion to change SQLA equality filter to a between_op filter when a float column is filtered

[Wavemaker]

I ran into the issue that using an equality filter did not return the expected results and that appears to be a common thing when comparing floats, see e.g. https://stackoverflow.com/questions/8839460/why-doesnt-this-sql-query-return-any-results-comparing-floating-point-numbers

If I see correctly, Camelot directly runs queries for equality in camelot.admin.action.list_filter.EditorFilter.decorate_query. Apparently this should work when the precision in the database matches the precision in the filter. However, when not having full control over the database structure and ending up with floats, this could go wrong. One way of dealing with this is to convert an equality query to a between query. I note this was already done in camelot.admin.action.list_filter.Filter.decorate_query. Why there and not also in the EditorFilter?

I ended up with this changed method, I am sure it can be made more elegant. It requires the precision field_attribute to be set.

  from camelot.view.field_attributes import order_operators
> from operator import eq
> from sqlalchemy.sql.operators import between_op
> from sqlalchemy.sql.sqltypes import Float
> import sys
> col_type = getattr(getattr(self.column.class_,self.attribute).class_,self.attribute).property.columns[0].type
 operator, value_1, value_2 = values
 if operator is None:
 return query.filter(self.column==None)
 elif operator == All:
 return query
> elif operator == eq and isinstance(col_type,Float) and not value_1 is None:
> operator = between_op # Note that between_op includes the upper and lower boundary values
> precision = getattr(self.column.class_,self.attribute).class_.Admin.field_attributes[self.attribute]['precision']
> delta = 0.5 * pow( 10, -1*precision)
> value_2 = value_1 + delta - sys.float_info.min # To mimic that the upper boundary is excluded
> value_1 -= delta


 arity = self.get_arity(operator)
 values = [value_1, value_2][:arity]
 none_values = sum( v == None for v in values )
 if (operator in order_operators) and none_values > 0:
 return query
 return query.filter(operator(self.column, *values))