ROOM database query on primary key field returns an int > 0 from COUNT(*)[etc.] but returned null from SELECT(*)[etc.]

we have a ROOM database with a table containing a Date field and and an amount field. The Date field is the (@NonNull) primary key. An initial query counts the number of records where the date <= an input date. If the first query returns a result > 0, a second query selects the maximum date using the same criteria, and a third query retrieves the record with that maximum date and returns the value of the amount field. The logic behind this was: is there a date -> if so, what is the date -> use the date to find the amount.

the DAO contains:

@Query("SELECT COUNT(*) FROM theTable WHERE date_field <= :inputDate")
int runFirstQuery (Date inputDate);

@Query("SELECT MAX(date_field) FROM theTable WHERE date_field <= :inputDate") 
Date runSecondQuery (Date inputDate);

@Query("SELECT * FROM theTable WHERE date_field = :inputDate")
TableEntity getRecord (Date inputDate);

in the activity:

BigDecimal theAmount = BigDecimal.ZERO; 
Date theInputDate = someCalendarWhichIsntTheProblem.getTime();
int checkForRecords = theDatabase.theDAO.runFirstQuery(theInputDate);
if (checkForRecords > 0){
    Date resultDate = theDatabase.theDAO.runSecondQuery(theInputDate);
    theAmount = theDatabase.theDAO.getRecord(resultDate).getTheAmount();
}

This code has been performing correctly without incident since publication (several years now), but recently the last line referenced above threw a null pointer exception somewhere in the wild (twice, on the same day, for the same user), and I haven't been successful in duplicating the error behavior. As I understand the logic here, the NPE would be thrown only if resultDate == null, but how would that be possible since a) the date field can’t contain a null value and b) we checked for matching records before running the second query? There must be at least one record found by the first query in order for the second query to be executed, so what's missing here?

so what's missing here?

The logic does not consider if the getTheAmount method retrieves null (or issues the NPE) even though the row exists, that is it appears that values, other than the date_field, could be a factor that results in the NPE.

Perhaps consider using:-

@Query("SELECT coalesce((SELECT theAmount_field FROM theTable WHERE date_field=:inputDate),0.0)")
BigDecimal getTheAmount(Date inputDate);

and then instead of:-

BigDecimal theAmount = BigDecimal.ZERO; 
Date theInputDate = someCalendarWhichIsntTheProblem.getTime();
int checkForRecords = theDatabase.theDAO.runFirstQuery(theInputDate);
if (checkForRecords > 0){
    Date resultDate = theDatabase.theDAO.runSecondQuery(theInputDate);
    theAmount = theDatabase.theDAO.getRecord(resultDate).getTheAmount();
}

just

BigDecimal theAmount=theDatabase.theDao.getTheAmount(theInputDate);

• where theAmount_field is the value/values as would be obtained by the getTheAmount method.
• this would also be more efficient.

Note this is in-principle code, it has not been compiled and may therefore contain some errors. However, the following shows the underlying query in action (albeit with integers instead of dates):-

DROP TABLE IF EXISTS theTable;
CREATE TABLE IF NOT EXISTS theTable (date_field INTEGER PRIMARY KEY, theAmount_field REAL);
INSERT OR IGNORE INTO theTable  VALUES (1,null),(2,1.1),(3,2.2),(4,3.3),(5,4.4);

SELECT coalesce((SELECT theAmount_field FROM theTable WHERE date_field=1),0.0); /*<<<<< NULL in theAmount_field */
SELECT coalesce((SELECT theAmount_field FROM theTable WHERE date_field=2),0.0);
SELECT coalesce((SELECT theAmount_field FROM theTable WHERE date_field=3),0.0);
SELECT coalesce((SELECT theAmount_field FROM theTable WHERE date_field=4),0.0);
SELECT coalesce((SELECT theAmount_field FROM theTable WHERE date_field=500),0.0); /*<<<<< no valid row */

/*Cleanup */
DROP TABLE IF EXISTS theTable;

So 5 rows are inserted with values 1-5 representing the dates. 5 Queries are run.

1. The first extracting the errant null, even though the date(sic) is found (the suspected issue not covered by the logic in the question). The result is that 0.0 is returned as opposed to the errant null:-

   1. 

2. The second returning 1.1 as exected

   1. 

3. The third and fourth returning 2.2 and 3.3 respectively, as expected

4. The fifth uses 500 as the inputDate, for which there is no row, this returns 0.0, as expected.

   1. 

So all bases are covered (no match for the inputDate returns 0.0 and errant null in the theAmount_field) all within the single transaction.

If preferred the coalesce function could be replaced by the ifnull function, they, in this situation, are identical. See https://www.sqlite.org/lang_corefunc.html#coalesce and https://www.sqlite.org/lang_corefunc.html#ifnull