IssueTracker

Component used: Room. Version used: 2.4. Devices/Android versions reproduced on: Android 11.

I am trying to get data with LEFT JOIN using a multimap. This is a very simple LEFT JOIN request such as in the documentation.

But both my tables have the non-null field named "identifier". When you call LEFT JOIN query in SQLite, but there is no row in the second table, you will get NULL for ALL values of the second table (see screenshot). So, it seems Room just try to verify for null these null values. It is a bug. Room should return an empty list for List<PostEntity>.

DB schema:

CREATE TABLE IF NOT EXISTS `categories` (
    `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    `identifier` TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS `posts` (
    `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    `category_id` INTEGER NOT NULL,
    `identifier` TEXT NOT NULL,
    
    FOREIGN KEY(`category_id`) REFERENCES `categories`(`id`)
);

INSERT INTO `categories` VALUES (null, "test-1");
INSERT INTO `categories` VALUES (null, "test-2");
INSERT INTO `categories` VALUES (null, "test-3");

SELECT * FROM `categories` LEFT JOIN `posts` ON `posts`.`category_id` = `categories`.`id`;

Dao:

@Dao
interface CategoryDao {
    @Query("SELECT * FROM `categories` LEFT JOIN `posts` ON `posts`.`category_id` = `categories`.`id`")
    fun getCategoryAndPostsLiveDataTest(): LiveData<Map<CategoryEntity, List<PostEntity>>>
}

Error:

java.lang.RuntimeException: Exception while computing database live data.

Caused by: java.lang.NullPointerException: Parameter specified as non-null is null: method kotlin.jvm.internal.Intrinsics.checkNotNullParameter, parameter identifier