OrmLite does not bring results on "where" with "not equals" comparator and null column values

Question

What's wrong with this line?

qB.where().ne(AppConstant.SYNC_STATUS_FIELD_NAME, strSyncOK);

i'm doing a simple where not equals with ORMlite and brings 0 results no errors:

full code:

dao = DaoManager.createDao(connectionSource, clazz);
QueryBuilder qB = dao.queryBuilder();
SelectArg strSyncOK = new SelectArg(AppConstant.SYNC_OK); // ==> "OK" string
qB.where().ne(AppConstant.SYNC_STATUS_FIELD_NAME, strSyncOK);
List<T> var = dao.query(qB.prepare());

When i remove the line

qB.where().ne(AppConstant.SYNC_STATUS_FIELD_NAME, strSyncOK);

all is ok.

When the line is there, no results found, i checked data and all data with AppConstant.SYNC_STATUS_FIELD_NAME is null

Parameter:

public static final String SYNC_OK = "OK";

Output:

SELECT * FROM `Evento` WHERE `syncStatus` <> ? 

i'm missing something?

Answer 1

I believe this a Sqlite FAQ and not an ORMLite problem. If you try it directly:

sqlite> CREATE TABLE table1 (foo int, bar string);
sqlite> INSERT INTO table1 (foo, bar) VALUES (1, 2);
sqlite> INSERT INTO table1 (foo, bar) VALUES (2, null);

Now we look at the queries:

sqlite> SELECT * FROM table1 WHERE foo == 1;
1|2
sqlite> SELECT * FROM table1 WHERE foo == 2;
2|

Notice that when we say bar != a value, the null field is not matched:

sqlite> SELECT * FROM table1 WHERE bar != 3;
1|2

However when we use Sqlite's IS NOT operator, it does match the null:

sqlite> SELECT * FROM table1 WHERE bar is not 3;
1|2
2|    <---- here it is!!

I believe that this is due to Sqlite's null handling although I can't find the specific docs on it. Sqlite's IS NOT documentation mentions it somewhat but again not specifically.

Aha. Here's the reference to read: Not equal <> != operator on NULL