What is the expectations regarding nullable parame...

# room

[Olivier Patry]

What is the expectations regarding nullable parameters in Room DAO?

I have the following function not working

@Query(
    """
        SELECT * FROM task
        WHERE parent_list_local_id = :taskListLocalId
          AND parent_local_id = :parentTaskLocalId
          AND position <= :position
          AND is_completed = false
        ORDER BY position ASC
    """
)
suspend fun getTasksUpToPosition(taskListLocalId: Long, parentTaskLocalId: Long?, position: String): List<TaskEntity>

If I force parent_local_id IS NULL, it's ok
If I force parent_local_id = 0L, it's ok
But can't have both at the same time using (equivalent of = NULL)

I went for COALESCE(parent_local_id, -1) = COALESCE(:parentTaskLocalId, -1) for concision (knowing my ID is always positive)
I also made it work with an OR parent_local_id = :parentTaskLocalId OR (:parentTaskLocalId IS NULL AND parent_local_id IS NULL)

I was expecting the generator to deal with nullable parameter itself.
In the generated code, it deals with it, and use bindNull but nothing more fancy.

Do I miss something? Is it a requirement of Room somehow? Why? Would it be desirable to have it working?

(FTR, I'm using Room on desktop with SQLite Bundled)

[danysantiago]

Since NULL is a valid SQL param / value Room doesn't do anything special here other than bindNull, its up to you to further adjust the query if a null params for your applications means to filter certain rows. I've seen the COALESCE strategy used the most to handle null params. Both are OK as long as they produce your expected result.

[Olivier Patry]

I'm not using DB very often, so sorry for the naivety of my question:

What would be a use case for NULL as valid SQL Param/value?
My rows contains no value/`NULL` for this parent_local_id column, what the difference with what you mentionned?

[danysantiago]

Inserting a NULL value is the most basic case of using a nullable SQL param: INSERT INTO Cat (name, owners) VALUES (?, ?) followed by bindText(1, 'Tom') and bindNull(2)

[danysantiago]

Unfortunately SQL (the language) defines that NULL values are not considered equal to each other, which is why NULL = NULL evaluates to false and the usage of IS NULL with an OR like you did or with COALESCE to use a default value when doing a query are right approaches.

[Olivier Patry]

Ok, thanks for explanation 👍