Since I couldn't find a more appropriate GitHub repository to post this issue I'll do it here:
When I have a query like the following, I run into the risk of a NullPointerException since max() and min() could return NULL if there are no rows in the table yet. But Room actually doesn't evaluate this circumstance during compilation. I think this should be taken into account, since debugging can be pretty painful. I actually found this bug through Crashlytics; it occurred during a race condition (reading values before the first value was inserted). I fixed this issue by replacing Boolean with Boolean?@Query("SELECT ((max(timestamp) - min(timestamp)) / ${Constants.milliSecondsPerDay}) >= :numberOfDays FROM just_a_table") suspend fun isSufficientNumberOfDays(numberOfDays: Int): Boolean
A potential fix might be this statement, which only invokes max or min if there are rows in the table and otherwirse returns FALSE, which is 0 in this case. But in theory this would increase the number of instructions from 2 to 3: count -> max -> min
@Query("SELECT CASE WHEN count(*) THEN 0 ELSE ((max(timestamp) - min(timestamp)) / ${Constants.milliSecondsPerDay}) >= :numberOfDays END FROM just_a_table") suspend fun isSufficientNumberOfDays(numberOfDays: Int): Boolean
Maybe there is a smarter way to get a Boolean value out of such a query...
The max() aggregate function returns the maximum value of all values in the group. The maximum value is the value that would be returned last in an ORDER BY on the same column. Aggregate max() returns NULL if and only if there are no non-NULL values in the group.
min(X)
The min() aggregate function returns the minimum non-NULL value of all values in the group. The minimum value is the first non-NULL value that would appear in an ORDER BY of the column. Aggregate min() returns NULL if and only if there are no non-NULL values in the group.
Since I couldn't find a more appropriate GitHub repository to post this issue I'll do it here:
When I have a query like the following, I run into the risk of a NullPointerException since max() and min() could return NULL if there are no rows in the table yet. But Room actually doesn't evaluate this circumstance during compilation. I think this should be taken into account, since debugging can be pretty painful. I actually found this bug through Crashlytics; it occurred during a race condition (reading values before the first value was inserted). I fixed this issue by replacing Boolean with Boolean?
@Query("SELECT ((max(timestamp) - min(timestamp)) / ${Constants.milliSecondsPerDay}) >= :numberOfDays FROM just_a_table") suspend fun isSufficientNumberOfDays(numberOfDays: Int): Boolean
A potential fix might be this statement, which only invokes max or min if there are rows in the table and otherwirse returns FALSE, which is 0 in this case. But in theory this would increase the number of instructions from 2 to 3: count -> max -> min
@Query("SELECT CASE WHEN count(*) THEN 0 ELSE ((max(timestamp) - min(timestamp)) / ${Constants.milliSecondsPerDay}) >= :numberOfDays END FROM just_a_table") suspend fun isSufficientNumberOfDays(numberOfDays: Int): Boolean
Maybe there is a smarter way to get a Boolean value out of such a query...
Reference: SQLite Aggregate Functions