ormlite weird query results (involving double/float)

Question

In my Android app, I've got the following class:

@DatabaseTable(tableName="things")
public class Thing {

    public static final String ID_FIELD_NAME = "id";
    public static final String FLOAT_FIELD_NAME = "myFloatField";

    @DatabaseField(generatedId=true,columnName=ID_FIELD_NAME)
    private int id;
    @DatabaseField(columnName=FLOAT_FIELD_NAME)
    private float myFloatField;

  //[snip constructor and getters]

}

Then, in my DBManager, I do the following:

Dao<Thing, Integer> dao = helper.getThingDao();
Thing tmp = dao.queryBuilder()
                    .where()
                    .eq(Thing.FLOAT_FIELD_NAME, otherThing.getFloatField());

Turns out that evvery once in a while tmp will be null. I tried many things and varied, and what I found to be working to get the right Thing every time is the following:

Dao<Thing, Integer> dao = helper.getThingDao();
Thing tmp = dao.queryBuilder()
                    .where()
                    .eq(Thing.FLOAT_FIELD_NAME, Double.valueOf(otherThing.getFloatField()));

I read in the docs:

float or Float (DataType.FLOAT or DataType.FLOAT_OBJ) Persisted as SQL type FLOAT.

double or Double (DataType.DOUBLE or DataType.DOUBLE_OBJ) Persisted as SQL type DOUBLE.

So my question is: is this a bug in my code (but I don't see how, am I not using that where() correctly?), a weird thing ormlite has with floats or something else entirely?

Edit: I'm using ormlite-core-4.48.jar and ormlite-android-4.48.jar.

Edit 2: what's more, if I change the field from float to double and then execute the query, it still doesn't work. I need to use Double.valueOf().

First thought is floating point number equality. It's not trivial, as floating point numbers can't represent all numbers, have limited accuracy, and you get rounding errors. Generally, it's better to set a threshold you allow the numbers to differ (epsilon), and query with abs(a-b) < epsilon. — Arve, Aug 20 '14 at 08:05
That was my thought, as I remember having similar problems with floats in the past. In fact in my tests I find the behavior quite consistent. Ex: myFloatFiled=0.25 it will find it; myFloatField=0.33 it will not. — Stephan, Aug 20 '14 at 08:20
On a side note: What does the myFloatField represent? What's the precision required? — Arve, Aug 20 '14 at 09:04
It depends, I have multiple classes like that, but in general it's banking/insurance stuff, so I guess the precision should be pretty high. I'll ask around what could be allowed as 'epsilon'. — Stephan, Aug 20 '14 at 09:56
NEVER use floating point numbers for money. Have a look at http://stackoverflow.com/questions/3730019/why-not-use-double-or-float-to-represent-currency — Arve, Aug 20 '14 at 10:09

Arve · Accepted Answer · 2014-08-20T08:53:41.103

0

Avoid == testing of floating points, as most cannot be represented accurately, and rounding errors are frequent.

Instead of equals, test that abs(a - b) < epsilon where epsilon is your accepted difference.

SQL:

SELECT a, b from myTable where abs(a - b) < 0.001

Not too familiar with ORMLite, but I see you can do where.raw(sql) which should at least allow you to do this.

edited Aug 20 '14 at 08:53

answered Aug 20 '14 at 08:26

Arve

8,058
2
22
25

So you're suggesting to use `Thing tmp = dao.queryBuilder().where().between(Thing.FLOAT_FIELD_NAME, otherThing.getFloatField()-epsilon, otherThing.getFloatField+epsilon);`? – Stephan Aug 20 '14 at 08:39
Not sure what's the most efficient way, and I haven't used ORMLite for a while. Added SQL example. – Arve Aug 20 '14 at 08:55
Another option is to use BigDecimal instead of float. – Arve Aug 20 '14 at 09:01
In the end this thing bit me multiple times, so I went for your solution. Only thing to notice: the epsilon in my case needed to be dependent on the number. Eg, it could be 0.5 or 0.4890145, hence I calculate the epsilon based on the current value. – Stephan Sep 24 '14 at 13:12
On a side note: how many digits of precision does your percentages have? – Arve Sep 25 '14 at 08:50
it depends. There are cases where it's only 1 decimal, and others where it's 7. As they're values coming from a server call I have no idea, beforehand. – Stephan Sep 25 '14 at 08:58

ormlite weird query results (involving double/float)

1 Answers1