Unexpected NULL when loading an SQLite shared value

[ddunlea]

Hi folks, I'm using the swift-sharing library with sharing-grdb in a composable architecture based app and I'm getting a runtime error when I try to load a particular shared value. I've put together a minimal project to demonstrate it here. I'm posting here as the error is coming from code in the swift-sharing library, though the root cause may well be in structured-queries, or sharing-grdb, or (most likely) my own stupidity!

The DB schema is quite simple:

@Table
struct OneRecord: Identifiable, Sendable, Equatable {
  let id: Int
  var name: String
}

@Table
struct ManyRecord: Identifiable, Sendable, Equatable {
  let id: Int
  var name: String
  let oneId: Int
}

and then I have a selection which allows me to return all of the OneRecords along with a count of the associated ManyRecords

@Selection
struct OneWithManyCount: Equatable, Identifiable, Sendable {
  public let one: OneRecord
  public let manyCount: Int
  
  public var id: Int {
    one.id
  }
  
  public static func all() -> Select<OneWithManyCount.Columns.QueryValue, OneRecord, ManyRecord?> {
    return OneRecord
      .all
      .leftJoin(ManyRecord.all) { one, many in
        one.id.eq(many.oneId)
      }
      .order{ one, many in
        (many.count().desc(), one.name)
      }
      .select{ one, many in
        OneWithManyCount.Columns(one: one, manyCount: many.count())
      }
  }
}

I have a FetchAll field in the state which fetches this and this toy demo app displays the number of records

@Reducer
struct CountingNullsFeature {
  @ObservableState
  struct State {
    @FetchAll(OneWithManyCount.all())
    var oneWithManyCounts: [OneWithManyCount]
  }
}

struct CountingNullsView: View {
  @Bindable var store: StoreOf<CountingNullsFeature>

  var body: some View {
    Form {
      Text("Record count: \(store.oneWithManyCounts.count)")
    }
  }
}

When I run the app, I get an error:

Caught error: Expected column 2 ("manyCount") to not be NULL: …

SELECT "oneRecords"."id", "oneRecords"."name" AS "one", count("manyRecords"."id") AS "manyCount" FROM "oneRecords" LEFT JOIN "manyRecords" ON ("oneRecords"."id" = "manyRecords"."oneId") ORDER BY count("manyRecords"."id") DESC, "oneRecords"."name"

Does anyone have any insight into what might be going wrong here? Many thanks!

[mbrandonw]

Hi @ddunlea, thank you very much for the project, that was helpful to see what is going on!

And it's tricky, but the behavior you are seeing is to be expected. The problem is that a count() query always returns an integer, and so that forces something to be returned for the public let one: OneRecord in your @Selection. But because your tables are empty, it has no choice but to return NULL for the fields of "oneRecords".

You can verify this by running the queries directly in SQLite. I opened an in-memory database from the command line, copy-pasted the "CREATE TABLE" statements that your logs output, and then ran the query:

sqlite> SELECT "oneRecords"."id", "oneRecords"."name" AS "one", count("manyRecords"."id") AS "manyCount" 
FROM "oneRecords" 
LEFT JOIN "manyRecords" ON ("oneRecords"."id" = "manyRecords"."oneId") 
ORDER BY count("manyRecords"."id") DESC, "oneRecords"."name";
┌────┬─────┬───────────┐
│ id │ one │ manyCount │
├────┼─────┼───────────┤
│    │     │ 0         │
└────┴─────┴───────────┘

Notice how "id" and "one" are NULL?

However, I think the query as written just isn't correct. If you are wanting to select each row from "oneRecords" along with the count of its associated "manyRecords", then you need to add a "GROUP BY" clause:

public static func all() -> some Statement<OneWithManyCount> {
  return OneRecord
    .group(by: \.id) // 👈
    .leftJoin(ManyRecord.all) { one, many in
      one.id.eq(many.oneId)
    }
    .order{ one, many in
      (many.count().desc(), one.name)
    }
    .select{ one, many in
      OneWithManyCount.Columns(one: one, manyCount: many.count())
    }
}

With that the query does not error and it will omit the NULL row I showed above.

[ddunlea]

Thank you so much. PEBKAC as suspected! And the really galling thing? I have 2 very similar families of records in my real app and the Selections for the first family all were already correctly using group(by:). And they were in the same file 🤦