Room query with math functions only works when original value is 0

Question

Building up a database full of data on Android Studio, with Java and Room (not kotlin). Some are columns are text, some are numbers with decimals. However, all columns are identified as text but the first one (id, primary key).

The user writes a value (sphecornea) and submits... the app should return all lines that match that value.

Algorithm.java includes this function

 public void cnvalgorithm (View view) {
        EditText sphecornea = findViewById(R.id.sphecornea);
        String sphecorneastr = sphecornea.getText().toString();
                AppExecutors.getInstance().diskIO().execute(() -> {
                  final List persons = mDb.personDao().loadPersonByAbe(sphecorneastr);
            runOnUiThread(() -> mAdapter.setTasks(persons));
        });
    }

PersonDao

@Query("SELECT id,firm,model,aberration_correction FROM PERSON" + " WHERE aberration_correction = :sphecorneastr")
public List loadPersonByAbe(String sphecorneastr);

As is, I only get the proper results when the entered value is 0. Results are empty for any other number. I figured that the system is not recognizing "-0.20" as a number, so I tried to manipulate it as Double by changing both the Dao and Algorithm.java section. Then, I don't get any results at all.

Update:

I just realised that the app doesn't consider the data (other than 0) as numbers. I tried this code (very inelegant, I know). It adds the 0+1, but it crashes when dealing with any other number...

Double aberration_correctiondou = Double.parseDouble(mPersonList.get(i).getAberration_correction());
Double aberration_correctiondou2 = aberration_correctiondou + 1;
        myViewHolder.aberration_correction.setText(String.valueOf(aberration_correctiondou2));

MikeT · Answer

Try

@Query("SELECT id,firm,model,aberration_correction FROM PERSON" + " WHERE round(CAST(aberration_correction AS REAL),2) = round(CAST(:sphecorneastr AS REAL),2)"

or :-

@Query("SELECT id,firm,model,aberration_correction FROM PERSON" + " WHERE round(aberration_correction,2) = round(:sphecorneastr,2)")

This should convert the string literal (as Room will wrap the value in single quotes so '-0.2').

See https://www.sqlite.org/lang_expr.html#castexpr See https://www.sqlite.org/lang_corefunc.html#round

Double's are stored to a very high precision and, for example -0.20 will not be stored as such, instead it will be stored as something like -0.20000000000000093 ( or -0.19500000000000092).

Demo

Perhaps consider this example/demo:-

First the Person class:-

@Entity
class Person {
    @PrimaryKey
    Long id=null;
    String firm;
    String model;
    String aberration_correction;
    double aberration_correction_double;

    Person(){}
    @Ignore
    Person(Long id,String firm, String model, double aberration_correction) {
        this.id  = id;
        this.firm = firm;
        this.model = model;
        this.aberration_correction = String.valueOf(aberration_correction);
        this.aberration_correction_double = aberration_correction;
    }
    @Ignore
    Person(String firm, String model, double aberration_correction) {
        this.id = null;
        this.firm = firm;
        this.model = model;
        this.aberration_correction = String.valueOf(aberration_correction);
        this.aberration_correction_double = aberration_correction;
    }
}

note that the correction has both a double and a String representation (both based upon a double)

The @Dao annotated abstract class (could be an interface) PersonDao:-

@Dao
abstract class PersonDao {
   @Insert(onConflict = OnConflictStrategy.IGNORE)
   abstract long insert(Person person);
   @Query("SELECT id,firm,model,aberration_correction, aberration_correction_double FROM PERSON" + " WHERE round(CAST(aberration_correction AS REAL),2) = round(CAST(:sphecorneastr AS REAL),2)")
   abstract List getPeopleByCorrection(String sphecorneastr);
   @Query("SELECT id,firm,model,aberration_correction, aberration_correction_double FROM PERSON" + " WHERE round(aberration_correction,2) = round(:sphecorneastr,2)")
   abstract List getPeopleByCorrectionV2(String sphecorneastr);
}

Using a basic @Database annotated class with .allowMainThreadQueries (for convenience and brevity) and with the following code in an Activity:-

public class MainActivity extends AppCompatActivity {

    TheDatabase db;
    PersonDao dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        db = TheDatabase.getInstance(this);
        dao = db.getPersonDao();

        Double correction = 1.0;
        for (int i=0;i<1000;i++) {
            dao.insert(new Person("Firm" + i,"Model" + i,correction));
            correction = correction - 0.005;
        }

        correction = -0.20;
        for (Person p: dao.getPeopleByCorrection(correction.toString())) {
            Log.d("DBINFO","Firm is " + p.firm + " Model is " + p.model + " C1 is " + p.aberration_correction + " C2 is " + p.aberration_correction_double);
        }
        for (Person p: dao.getPeopleByCorrectionV2(correction.toString())) {
            Log.d("DBINFO","Firm is " + p.firm + " Model is " + p.model + " C1 is " + p.aberration_correction + " C2 is " + p.aberration_correction_double);
        }
    }
}

1000 rows will be inserted with corrections values from 1.0 to close to - 4.0, decreasing by 0.005 (thus covering the -0.20 value).

Then both queries are used, showing no need for the CAST, but a need for the round.

When run (only designed to run the once) then the Log includes:-

2022-09-25 07:27:42.838 D/DBINFO: Firm is Firm239 Model is Model239 C1 is -0.19500000000000092 C2 is -0.19500000000000092
2022-09-25 07:27:42.839 D/DBINFO: Firm is Firm240 Model is Model240 C1 is -0.20000000000000093 C2 is -0.20000000000000093


2022-09-25 07:27:42.841 D/DBINFO: Firm is Firm239 Model is Model239 C1 is -0.19500000000000092 C2 is -0.19500000000000092
2022-09-25 07:27:42.841 D/DBINFO: Firm is Firm240 Model is Model240 C1 is -0.20000000000000093 C2 is -0.20000000000000093

As can be seen in either case that -0.20 is not a stored value but by rounding to 2 dp then close values are obtained. You may have to adjust the rounding to suit.

Looking at the actual data stored, Via App Inspection, then

The initial rows are:-

The last rows are :-

And rows around the -0.20 mark are :-

with the extracted rows highlighted

Room query with math functions only works when original value is 0

1 Answers