The behavior you are seeing is what is expected. NULL only every matches the IS NULL criteria. It doesn't pass equality, inequality, greater than or less than tests.
The best option is to define the field as NOT NULL. NULLs cause all manner of trouble, and the best option is usually to avoid dealing with them. Clay Dowling Kareem Badr said: > I read the section on the SQLite website regarding the inconsistencies > of how NULL values are handled in various databases. I didn't see > anything that mentioned what I am running into, though. > > In a nutshell, the following queries do not return rows when test_field > is NULL. It seems counter-intuitive to me. > > SELECT * FROM my_table WHERE test_field NOT LIKE 'test value%' > SELECT * FROM my_table WHERE test_field != 'test value' > > I can understand the NOT LIKE query not returning rows with NULL more > than I can understand the != query not returning any rows. NULL does not > equal 'test value'. > > Is there an easy way around this, other than adding "OR test_field IS > NULL" to my queries? The example above is a lot simpler than the case I > need to handle in production, so I would like to avoid having to modify > every query to handle NULL values in a special case. > -- Simple Content Management http://www.ceamus.com
