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<Person> 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<Person> 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));

update 2:

Entity

For the sake of your mental health, I removed many lines (more columns of the db) of the entity file.

@Entity(tableName = "person")
public class Person {
    @PrimaryKey(autoGenerate = true)
    int id;
    public String firm;
    public String model;
    public String aberration_correction;
    

    @Ignore
    public Person(String    firm    ,     String    model   ,
                  String    aberration_correction   ,
                   {
        this.   firm    =   firm    ;
        this.   model   =   model   ;
        this.   aberration_correction   =   aberration_correction   ;
        
    }

    public Person(int id, String    firm    ,
                  String    model   ,
                  String    aberration_correction   ,
                  
                  ) {
        this.id = id;
        this.   firm    =   firm    ;
        this.   model   =   model   ;
        this.   aberration_correction   =   aberration_correction   ;
        
    }

    public static final String[] PERSONS = {

    };

    public Person() {

    }

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }

    public String getFirm   () {return  firm    ;} public void setFirm  (String     firm    ){this. firm    =   firm    ;}
    public String getModel  () {return  model   ;} public void setModel (String     model   ){this. model   =   model   ;}
    public String getAberration_correction  () {return  aberration_correction   ;} public void setAberration_correction (String     aberration_correction   ){this. aberration_correction   =   aberration_correction   ;}
    
}

A snapshot of the database: seems like I don't have enough points to upload images, a link is provided

Solution: As proposed by @MikeT, decimals at the database should be separated by "." instead of ",".

Answer 1

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<Person> 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<Person> 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

Additional (after entity and actual data provided)

Issue 1.

The entity indicates that you should be using the aberration_correction_double column for the WHERE clause, not the aberration_correction column (the latter appears to only be storing 0 or 1, perhaps representing a boolean)

Issue 2.

The screen shot shows ,s in the double, where you would expect a period. e.g. -0,20 instead of expected -0.20. SQLite will , if asked for a REAL (double), return a 0 (hence why 0 locates all).

Not sure how the , got into the data but that's the issue. A get around could be to use:-

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

• probably over the top as you probably only need the first replace.

You may wish to consider updating the underlying data e.g. a one off run of:-

@Query("UPDATE PERSON SET aberration_correction_double = CAST(replace(aberration_correction_double,',','') AS REAL), aberration_correction = CAST(replace(aberration_correction_double,',','') AS TEXT) WHERE instr(aberration_correction_double,',');"

• this assumes that both columns should contain the same value.