Simple query returned no results even though they are in database

[pawlowskim]

Hello, we occurred a bug in our production application. Basically we are gathering users steps, and storing it in local database. In some part of the application we have such piece of code

  Future<int> getStepsSince(String userId, int millisecondsSinceEpoch) {
    return db.rawQuery(
        "SELECT SUM(steps) as steps from $STEPS_TABLE_NAME WHERE userId = ? AND timestamp >= ?",
        [
          userId,
          millisecondsSinceEpoch
        ]).then((List<Map<String, Object?>> results) {
      if (results.length > 0) {
        return (results.first["steps"] as int?) ?? 0;
      } else
        return 0;
    });
  }

It works in general. But last days, we faced a problem that this query stopped working at runtime. We are executing it each time we got an event about new steps. From logs that user sent we found out, that this query returned correct value, and 3 seconds later it started returning 0, until user restarted the application.

No errors occurred meantime, connection was still on. This last for some time, meantime we were writing records to the same database, so even if data was somehow deleted (unlikely) it should return new entered data, but it didn't.

My questions are:

1. What else could be returned in the results response if it's successful (we are 100% sure table is not empty and is not malformed) for such query?
2. What is the right way to handle such query?
3. What could causes this query to stop returning results (maybe it's connected with 1.)?

[davidmartos96]

Hello, I haven't seen similar behavior on my 3+ year experience using this library. It's possible that your app logic has a bug and sends the wrong milliseconds. Have you logged that? Double check your DELETEs on that table as well if you have any.

[davidmartos96]

There is another possible source of bugs. SUM(col) of a column that can contain NULL will return NULL if any of the values is NULL. So your "?? 0" logic would apply in this situation. You can do SUM(COALESCE(steps, 0)) to treat NULL rows as 0 or extend the WHERE clause to filter NULLs with "steps IS NOT NULL"

[pawlowskim]

We are using this library for about 6-9 months so far, and didn't noticed such behaviour yet.

1. We are deleting rows on user logout, so it was not the case (beside, we are inserting steps over and over so even if it happen from within the code, it should count new steps).
2. We are logging changes to the millisecondsSinceEpoch so it's also not the case (but I will add additional logging to be sure in the future).
3. We are using null safety in the application, it should throw an error if we would pass null instead int for insert method (even earlier, we have some DTOs models and couple methods before it reach local db insert method). Also, just before writing this comment, I have tested inserting null instead actual value. Even there are nulls in the database, SUM query returns correct value, skipping nulled rows and our code still works fine. Also, other parts of the application will fail if there are nulls when reading data from DB and mapping to our models. So I'm 99% sure nulls are not the case.

Any other ideas?

Thanks for quick response.

[davidmartos96]

@pawlowskim Unfortunately I cannot help you without a way to reproduce the issue. Is not very probable this is a bug in the library as it simply calls the native SQLCipher implementations for Android and iOS. I would double check your where conditions, which if they are incorrect it will return 0 steps.

[pawlowskim]

Do you know maybe what kind of values could trigger the query to return 0? I couldn't dig into it from native implementation for iOS. And since nulls inserted to the database works as expected (rows are omitted, not returns a 0 as a output from the query) I don't have any idea what to check.

[davidmartos96]

@pawlowskim The only values that could trigger a 0 from your code snippet would be

1. No rows in the table that satisfy your WHERE conditions
2. ALL the rows that match the WHERE have a NULL value in the steps column

You may want to include extra logging in your app when you reach a 0 value, like:

if (steps == 0) {
   // Assert no rows with the where condition
   // Run "SELECT COUNT(*) as num_rows from $STEPS_TABLE_NAME WHERE userId = ? AND timestamp >= ?"
   // Check that the number of rows is actually 0, otherwise there is a bug somewhere
}