Will using IFNULL prevent SQLite from using an index?

Question

Let's say I have a table T and an index on field f. I want to filter on f based on some integer myF. If the integer is 0, I want all records where f is null. A sure way to write this would be:

db.rawQuery("SELECT someField FROM T WHERE "
        + (myF == 0 ? "f IS NULL" : "f = ?"),
        (myF == 0 ? new String[] {}, new String[] {String.valueOf(myF)}));

This is a bit inconvenient; especially if the query is more complex than this and has additional parameters. So I thought I'd write

db.rawQuery("SELECT someField FROM T WHERE IFNULL(f, 0) = ?", 
        new String[] {String.valueOf(myF)});

instead, which is much simpler, easier to read and easier to maintain.¹

My question is: If there is an index on f, will SQLite still use that index or will it resort to a table scan? I'm asking because in the latter case I'm not comparing a field and a parameter but an expression and a parameter, and I'm not sure how "smart" the SQLite query optimizer is.

---

¹ Note that there are no records with f = 0 in the database.

Answer 1

It will result in a table scan.

Example using the sqlite3 command line client:

sqlite> create table t(f);
sqlite> create index tf on t(f);
sqlite> explain query plan select * from t where ifnull(f,0)=1;
0|0|0|SCAN TABLE t (~500000 rows)
sqlite> explain query plan select * from t where f is null;
0|0|0|SEARCH TABLE t USING COVERING INDEX tf (f=?) (~10 rows)
sqlite> explain query plan select * from t where f=1;
0|0|0|SEARCH TABLE t USING COVERING INDEX tf (f=?) (~10 rows)