sqlite: LEFT JOIN COALESCE has unwanted nullability

[vanniktech]

Dialect

SQLite

Failing SQL

SELECT
  coach.id, coach.firstName, coach.lastName, coach.badge, coach.email,
  COALESCE(jamesTemplateDatabaseCoach.jamesTemplateDatabaseId, :jamesTemplateDatabaseId) AS jamesTemplateDatabaseId,
  jamesTemplateDatabaseCoach.state
  FROM coach
  LEFT JOIN jamesTemplateDatabaseCoach
    ON jamesTemplateDatabaseCoach.coachId = coach.id AND jamesTemplateDatabaseCoach.jamesTemplateDatabaseId = :jamesTemplateDatabaseId
  WHERE coach.id NOT IN :botIds AND (coach.firstName LIKE :searchTerm OR coach.lastName LIKE :searchTerm)
  GROUP BY 1
  ORDER BY state DESC, firstName COLLATE NOCASE ASC, lastName COLLATE NOCASE ASC
;

Description

Even though I'm using a LEFT JOIN I want the jamesTemplateDatabaseId property to be always present in the generated Kotlin Data class.

This is what I came up with (if there's another solution, please let me know!)

COALESCE(jamesTemplateDatabaseCoach.jamesTemplateDatabaseId, :jamesTemplateDatabaseId) AS jamesTemplateDatabaseId,

this also works fine and the generated code also looks good:

      return driver.executeQuery(null, """
      |SELECT
      |  coach.id, coach.firstName, coach.lastName, coach.badge, coach.email,
      |  COALESCE(jamesTemplateDatabaseCoach.jamesTemplateDatabaseId, ?) AS jamesTemplateDatabaseId,
      |  jamesTemplateDatabaseCoach.state
      |  FROM coach
      |  LEFT JOIN jamesTemplateDatabaseCoach
      |    ON jamesTemplateDatabaseCoach.coachId = coach.id AND jamesTemplateDatabaseCoach.jamesTemplateDatabaseId ${ if (jamesTemplateDatabaseId == null) "IS" else "=" } ?
      |  WHERE coach.id NOT IN $botIdsIndexes AND (coach.firstName LIKE ? OR coach.lastName LIKE ?)
      |  GROUP BY 1
      |  ORDER BY state DESC, firstName COLLATE NOCASE ASC, lastName COLLATE NOCASE ASC
      """.trimMargin(), 4 + botIds.size) {
        bindString(1, jamesTemplateDatabaseId)
        bindString(2, jamesTemplateDatabaseId)
        botIds.forEachIndexed { index, botIds_ ->
            bindString(index + 3, botIds_)
            }
        bindString(botIds.size + 3, searchTerm)
        bindString(botIds.size + 4, searchTerm)
      }

The only problem is the generated class where jamesTemplateDatabaseId is null. It should be able to infer that the value is always present:

public data class SearchJamesTemplateDatabaseCoach(
  public val id: String,
  public val firstName: String,
  public val lastName: String,
  public val badge: Int?,
  public val email: String?,
-  public val jamesTemplateDatabaseId: String?,
+  public val jamesTemplateDatabaseId: String,
  public val state: Int?
) {
  public override fun toString(): String = """
  |SearchJamesTemplateDatabaseCoach [
  |  id: $id
  |  firstName: $firstName
  |  lastName: $lastName
  |  badge: $badge
  |  email: $email
  |  jamesTemplateDatabaseId: $jamesTemplateDatabaseId
  |  state: $state
  |]
  """.trimMargin()
}

[AlecKazakova]

You’re expecting the runtime patan to be non null? It’s still valid to pass null as the final param to coalesce so I think that’s expected

…

On Tue, Nov 30, 2021 at 7:05 AM Niklas Baudy ***@***.***> wrote: Dialect SQLite Failing SQL SELECT coach.id, coach.firstName, coach.lastName, coach.badge, coach.email, COALESCE(jamesTemplateDatabaseCoach.jamesTemplateDatabaseId, :jamesTemplateDatabaseId) AS jamesTemplateDatabaseId, jamesTemplateDatabaseCoach.state FROM coach LEFT JOIN jamesTemplateDatabaseCoach ON jamesTemplateDatabaseCoach.coachId = coach.id AND jamesTemplateDatabaseCoach.jamesTemplateDatabaseId = :jamesTemplateDatabaseId WHERE coach.id NOT IN :botIds AND (coach.firstName LIKE :searchTerm OR coach.lastName LIKE :searchTerm) GROUP BY 1 ORDER BY state DESC, firstName COLLATE NOCASE ASC, lastName COLLATE NOCASE ASC ; Description Even though I'm using a LEFT JOIN I want the jamesTemplateDatabaseId property to be always present in the generated Kotlin Data class. This is what I came up with (if there's another solution, please let me know!) COALESCE(jamesTemplateDatabaseCoach.jamesTemplateDatabaseId, :jamesTemplateDatabaseId) AS jamesTemplateDatabaseId, this also works fine and the generated code also looks good: return driver.executeQuery(null, """ |SELECT | coach.id, coach.firstName, coach.lastName, coach.badge, coach.email, | COALESCE(jamesTemplateDatabaseCoach.jamesTemplateDatabaseId, ?) AS jamesTemplateDatabaseId, | jamesTemplateDatabaseCoach.state | FROM coach | LEFT JOIN jamesTemplateDatabaseCoach | ON jamesTemplateDatabaseCoach.coachId = coach.id AND jamesTemplateDatabaseCoach.jamesTemplateDatabaseId ${ if (jamesTemplateDatabaseId == null) "IS" else "=" } ? | WHERE coach.id NOT IN $botIdsIndexes AND (coach.firstName LIKE ? OR coach.lastName LIKE ?) | GROUP BY 1 | ORDER BY state DESC, firstName COLLATE NOCASE ASC, lastName COLLATE NOCASE ASC """.trimMargin(), 4 + botIds.size) { bindString(1, jamesTemplateDatabaseId) bindString(2, jamesTemplateDatabaseId) botIds.forEachIndexed { index, botIds_ -> bindString(index + 3, botIds_) } bindString(botIds.size + 3, searchTerm) bindString(botIds.size + 4, searchTerm) } The only problem is the generated class where jamesTemplateDatabaseId is null. It should be able to infer that the value is always present: public data class SearchJamesTemplateDatabaseCoach( public val id: String, public val firstName: String, public val lastName: String, public val badge: Int?, public val email: String?,- public val jamesTemplateDatabaseId: String?,+ public val jamesTemplateDatabaseId: String, public val state: Int? ) { public override fun toString(): String = """ |SearchJamesTemplateDatabaseCoach [ | id: $id | firstName: $firstName | lastName: $lastName | badge: $badge | email: $email | jamesTemplateDatabaseId: $jamesTemplateDatabaseId | state: $state |] """.trimMargin() } — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#300>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAMZBQBOBNY4LJBRNBXN67DUOS42FANCNFSM5JBUA6XQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[vanniktech]

Ah right the generated signature is:

  public fun searchJamesTemplateDatabaseCoach(
    jamesTemplateDatabaseId: String?,
    botIds: Collection<String>,
    searchTerm: String
  ): Query<SearchJamesTemplateDatabaseCoach>

How can I enforce jamesTemplateDatabaseId to be non null?

[AlecKazakova]

since we're just creating an API to mirror what is accepted in SQL, I don't think there's a way with sql functions since most accept null.

[vanniktech]

Fair enough, then I guess I'll just do:

-COALESCE(jamesTemplateDatabaseCoach.jamesTemplateDatabaseId, :jamesTemplateDatabaseId AS jamesTemplateDatabaseId,
+COALESCE(jamesTemplateDatabaseCoach.jamesTemplateDatabaseId, :jamesTemplateDatabaseId, "") AS jamesTemplateDatabaseId,

[vanniktech]

I thought about this again and it does seem wrong to me. This is my column definition (on which I'm joining):

CREATE TABLE jamesTemplateDatabaseCoach (
  jamesTemplateDatabaseId TEXT NOT NULL
)

It's non null. This is my join statement:

LEFT JOIN jamesTemplateDatabaseCoach
    ON jamesTemplateDatabaseCoach.coachId = coach.id AND jamesTemplateDatabaseCoach.jamesTemplateDatabaseId = :jamesTemplateDatabaseId

Why would I want to have a nullable jamesTemplateDatabaseId if in the table statement the column is non null? Probably the LEFT JOIN triggers the unwanted nullability. It should not though.

[AlecKazakova]

yes, that part does seem like a bug, during the join clause you still have rows present so the arg there should be non null