Room (SQLite) WHERE clause with null arguments doesn't work

Question

I have entity class Person and I want to query persons from database by feature which may or may not be null. When I set feature to non-null the query works but when the feature is null the query returns empty list. What I am doing wrong?

TEST

Context context = InstrumentationRegistry.getContext();
AppDb db = Room.inMemoryDatabaseBuilder(context, AppDb.class).allowMainThreadQueries().build();
PersonDao dao = db.personDao();

dao.insert(new Person("El Risitas", "Funny"));
dao.insert(new Person("Elon Musk", "Alien"));
dao.insert(new Person("Donald Trump", null));

assertEquals(3, dao.getAll().size());

assertEquals("Funny", dao.getByFeature("Funny").get(0).getFeature());

// fails because dao.getByFeature(null) = EMPTY LIST
assertEquals(null, dao.getByFeature(null).get(0).getFeature());

Person.java

import androidx.room.ColumnInfo;
import androidx.room.Entity;
import androidx.room.PrimaryKey;

@Entity(tableName = "person")
public class Person {

    @ColumnInfo(name = "id") @PrimaryKey(autoGenerate = true) private int id;
    @ColumnInfo(name = "name") private String name;
    @ColumnInfo(name = "feature") private String feature;

    public Person(String name, String feature) {
        this.name = name;
        this.feature = feature;
    }
    public void setId(int id) { this.id = id; }
    public int getId() { return id; }
    public String getName() { return name; }
    public String getFeature() { return feature; }
}

PersonDao.java

import androidx.room.Dao;
import androidx.room.Insert;
import androidx.room.Query;
import java.util.List;

@Dao
public interface PersonDao {

    @Insert
    void insert(Person person);

    @Query("SELECT * FROM person")
    List<Person> getAll();

    @Query("SELECT * FROM person WHERE feature = :feature")
    List<Person> getByFeature(String feature);

}

AppDb.java

import androidx.room.Database;
import androidx.room.RoomDatabase;

@Database(
        version = 1,
        exportSchema = false,
        entities = {Person.class}
)
public abstract class AppDb extends RoomDatabase {

    public abstract PersonDao personDao();
}

Answer 1

In SQL, nothing is ever equal to null. (Nothing is ever not equal to null, either.) You have to use is null. So your query could be something like (untested)

SELECT * FROM person WHERE feature = :feature or (feature is null and :feature is null)

Answer 2

You can also use IS operator instead of =.

SELECT * FROM person WHERE feature IS :feature

From SQLite documentation:

The IS and IS NOT operators work like = and != except when one or both of the operands are NULL. In this case, if both operands are NULL, then the IS operator evaluates to 1 (true) and the IS NOT operator evaluates to 0 (false). If one operand is NULL and the other is not, then the IS operator evaluates to 0 (false) and the IS NOT operator is 1 (true). It is not possible for an IS or IS NOT expression to evaluate to NULL.