WHERE - IS NULL not working in SQLite?

Question

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 PHP 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.

Your first `IS NULL` syntax is correct. In this case you'll need to give us an idea how you're sending that command to SQLite. That error message does not look like it's coming from the sqlite3 command line interface. — Larry Lustig, Sep 22 '11 at 18:40
Larry- through PHP- using phpliteadmin http://www.danedesigns.com/phpliteadmin.php and in my php code with ezsql — Yarin, Sep 22 '11 at 18:43
My guess is that PHPLiteAdmin is misreporting some other problem. Could be a missing or mislocated database file, or anything else. — Larry Lustig, Sep 22 '11 at 18:53
No- I was using the PHPLiteAdmin to check why I was getting the error in my own code. In PHP the warning is "PHP Warning: SQL logic error or missing database"- Again, the database, table, and data is there- the exact statement works fine when NOT is included... I know is sounds strange but there it is — Yarin, Sep 22 '11 at 18:55
Okay, so it's a bad or missing database file. Possibilities are (severely) different versions of SQLite or, more likely, you're not directing your SQL library to open the file you think you're opening. — Larry Lustig, Sep 22 '11 at 19:07
It would be useful if you would add your solution as an answer and accepted it. Not everyone will read through your question until "UPDATE 4". — , Jul 06 '14 at 07:33

score 47 · Answer 1 · 2011-09-22T20:14:07.973

47

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>

edited Sep 22 '11 at 20:14

answered Sep 22 '11 at 18:32

1

@pst- I appreciate the answer but IS NULL is not working. My statements are delimited fine, as evidence by the fact that IS NOT NULL expression works. I know what the docs say, but these are the results I'm getting... – Yarin Sep 22 '11 at 18:40
@pst- If it's any help, I'm running these through PHP ezsql – Yarin Sep 22 '11 at 18:41
@Yarin What version of SQLite? I have updated my answer showing it works as expected (in my version). The one thing that is *very suspicious* is that `x IS NULL` "doesn't work". – Sep 22 '11 at 18:45
@pst- Im suspicious too (see my edits)- the prob is I know SQLite in PHP is different from the CLI SQLite- Is this accurate? How would I determine the version in that case? – Yarin Sep 22 '11 at 18:48
SQLite version 3.6.12 from the CLI... Mac OSX 10.6 – Yarin Sep 22 '11 at 18:49
PHP 5.3.4 (cli) (built: Jan 21 2011 00:08:14) – Yarin Sep 22 '11 at 18:50
From the CLI your tests run fine ... strange – Yarin Sep 22 '11 at 18:52
@Yarin The syntax understood by SQLite (for the given version) will not change (the CLI is just a primitive interaction layer). Perhaps there is something trying to "re-interpret" the query? – Sep 22 '11 at 19:00
@pst- See my updates- Thanks for sticking with this, really appreciate it. Wish I could give you more than an upvote, but the actual answer's still elusive. Thanks for your help-- – Yarin Sep 22 '11 at 19:49
Hope you get it solved (and post a solution/reason). My idea of "success" on SO is not getting a down-vote ;-) – Sep 23 '11 at 04:39

score 8 · Answer 2 · answered Nov 09 '11 at 00:56

8

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.

answered Nov 09 '11 at 00:56

Robert Brisita

5,461
3
36
35

+1 parent_id = "" and parent_id <> "" will always work – Reign.85 May 14 '14 at 15:19

score 7 · Answer 3 · answered Oct 05 '12 at 23:40

7

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

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

answered Oct 05 '12 at 23:40

Jose_GD

2,279
1
21
34

Weird... I've just found that in some cases *where field is null* works. – Jose_GD Nov 07 '12 at 14:34
1

In my case column type was INTEGER. Android API15 emulator column_name=0 worked. Android API15 phone column_name ISNULL (and IS NULL) worked. Hope that helps someone else. – IT-Dan Jul 06 '14 at 13:42

Tim · Answer 4 · 2011-09-22T20:04:39.150

2

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

edited Sep 22 '11 at 20:04

answered Sep 22 '11 at 19:59

Tim

8,669
31
105
183

What is the exact datatype (verbatim, as declared in your CREATE TABLE statement) of the column you are trying to test for null? – Tim Sep 22 '11 at 20:06

score 0 · Answer 5 · answered Sep 22 '11 at 20:08

0

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.

answered Sep 22 '11 at 20:08

Tim

8,669
31
105
183

score -2 · Answer 6 · answered Dec 25 '14 at 06:31

-2

try where your_col_name ISNULL wheres ISNULL contains no space

answered Dec 25 '14 at 06:31

Xmobile Vietnam

37
1

WHERE - IS NULL not working in SQLite?

6 Answers6

Linked