SQLITE (fmdb) select where x is null not working

Question

I'm using FMDB on iOS, attempting to query a table with a statement such as,

select * from test where foo isNull

In the sqlite C API, this ends up binding to null using this API,

int sqlite3_bind_null(sqlite3_stmt*, int);

This isn't working. The select invoked through fmdb which seems to be correctly binding the column to null is not finding matching records.

If in the sqlite3 command line session I do the following command, it works, but not through the sqlite C API via FMDB.

select * from test where foo isNull;

Here's fmdb code which reproduces the problem. It looks like fmdb is doing the proper steps invoking sqlite3_bind_null.

        NSString *stmt = @"select * from test where shipToCode=:foo";
        NSDictionary *dict = @{@"foo" : [NSNull null]};
        FMResultSet *rs = [db executeQuery:stmt withParameterDictionary:dict];
        int count = 0;
        while ([rs next]) {
            count++;
        }
        NSLog(@"Count %d", count);

Answer 1

NULL cannot be compared with the = operator; it is not equal with any value, even itself.

To compare with NULL, you have to use the IS NULL operator:

stmt = @"select * from test where shipToCode is null";

Answer 2

Try the following and check if there are any changes:

NSString *query = @"SELECT * FROM test WHERE shipToCode is NULL";

FMResultSet *rs = [db executeQuery:query];
int count = 0;

while ([rs next]) {
    count++;
}

NSLog(@"Count %d", count);