WHERE - IS NULL not working in SQLite?

Here's a strange one:

I can filter on NOT NULLS from SQLite, but not NULLS:

This works:

SELECT * FROM project WHERE parent_id NOT NULL;

These don't:

SELECT * FROM project WHERE parent_id IS NULL; 
SELECT * FROM project WHERE parent_id ISNULL; 
SELECT * FROM project WHERE parent_id NULL;

All return:

There is a problem with the syntax of your query (Query was not executed) ...

UPDATE:

I am doing this with PHP- through my code with ezSQl and using the PHPLiteAdmin interface

Using the PHPLiteAdmin demo, this expression works- so now I'm suspecting a version issue with my PHP's SQLite? Could that be? Wasn't this expression always valid?

UPDATE 2:

When I run the code from PHP using ezSQL, the PHP warning is:

PHP Warning: SQL logic error or missing database

Is there a way to get more information out of PHP? This is maddeningly opaque and weird, especially because the same statement in the CLI works fine...

UPDATE 3

The only other possible clue I have is that the databases that I create with PH开发者_如何学运维P cannot be read by the CLI, and vice versa. I get:

Error: file is encrypted or is not a database

So there's definitly two SQlite flavors butting heads here. (See this) Still, why the invalid statment??

UPDATE 4

OK I think I've traced the problem to the culprit, if not the reason- The DB I created with PHP ezSQL is the one where the IS NULL statement fails. If I create the DB using PHP's SQLite3 class, the statement works fine, and moreover, I can access the DB from the CLI, whereas ezSQL created DB gave the file is encrypted error.

So I did a little digging into ezSQL code- Off the bat I see it uses PDO methods, not the newer SQLite3 class. Maybe that's something- I'm not gonna waste further time on it...

In any case, I've found my solution, which is to steer clear of ezSQL, and just use PHPs SQLite3 class.

a IS b and a IS NOT b is the general form where a and b are expressions.

This is generally only seen in a IS NULL and a IS NOT NULL cases. There are also ISNULL and NOTNULL (also NOT NULL) operators which are short-hands for the previous expressions, respectively (they only take in a single operand).

The SQL understood in SQLite expressions is covered in SQLite Query Language: Expressions.

Make sure that (previous) statements have been terminated with a ; first if using the CLI.

These are all valid to negate a "null match":

expr NOT NULL
expr NOTNULL
expr IS NOT NULL

These are all valid to "match null":

expr ISNULL
expr IS NULL

Since all of the above constructs are themselves expressions the negations are also valid (e.g. NOT (expr NOT NULL) is equivalent to expr IS NULL).

Happy coding.

The proof in the pudding:

SQLite version 3.7.7.1 2011-06-28 17:39:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table x (y int null);
sqlite> select * from x where y isnull;
sqlite> select * from x where y notnull;
sqlite> select * from x where y not null;
sqlite> select * from x where y is null;
sqlite> select * from x where y is not null;
sqlite>

The problem could stem from how SQLite handles empty columns. For instance just because a column is empty does not mean it is NULL. Have you tested against ""?

SELECT * FROM project WHERE parent_id = ""

That query might return results.

In Android SQLite, field IS NULL doesn't work either.

field = 'null' does. Give it a try in your environment

This works on SQLite in SQLite Manager for Firefox:

          select * from foo where not baz is not null

The query above returns rows where column [baz] is null. :-) Yarin, maybe it will work for you? (The 'not' before the column name is not a typo).

This query too finds rows where baz is null:

         select * from foo where [baz]  is  null

If you are testing perhaps the PK column (?) and the column is being treated as synonym for rowid, then no rows will have a rowid that's null.

try where your_col_name ISNULL wheres ISNULL contains no space