SQLite – compare null values

Hi,

this is a quick post describing issue I have found when using SQLite in flash. I’ve tried to fetch some rows using following snippet, but couldn’t get correct results. Then after digging on the net I have found that there is a different syntax to compare null values. The problem was in comparing values that sometimes can be null.

The idea was to get how many empty values I have in the database, the result was always 0. I have start with plain text query (no parameters), but it didn’t worked as I’ve found on the SQLite page you can’t just use equal to compare NULL values, you have special syntax IS NULL or IS NOT NULL.

First sample data:

-- Create a test table with data
create table t1(a int, b varchar(8), c int);
insert into t1 values(1,0,0);
insert into t1 values(2,0,1);
insert into t1 values(3,1,0);
insert into t1 values(4,1,1);
insert into t1 values(5,null,0);
insert into t1 values(6,null,1);
insert into t1 values(7,null,null);

test snippet (simplified).

var statement:SQLStatement = new SQLStatement();
statement.sqlConnection = m_oConnection;
statement.text = "SELECT * FROM t1 WHERE b=NULL";

so this will not work, to actually have it working and return null or not null values in column b, you have to use proper syntax for comparing NULL values, revised query:

var statement:SQLStatement = new SQLStatement();
statement.sqlConnection = m_oConnection;
statement.text = "SELECT * FROM t1 WHERE b IS NULL";//or statement.text = "SELECT * FROM t1 WHERE b IS NOT NULL";

now it will return what was expected, but this doesn’t solve the problem, because you may found that your query doesn’t return expected values, this will happen in queries where parameters object is used to pass values to the query.

The example above was much simplified, normally you will use the parameters object of the SQLStatement instance, as follows.

This will not return expected row (real life example of problem with comparing null values).

var compare_a:int= 6;
var compare_b:String = null;
var compare_c:int= 1;
//expected 1 row in result
var statement:SQLStatement = new SQLStatement();
statement.sqlConnection = m_oConnection;
statement.text = "SELECT * FROM t1 WHERE a = ? AND b = ? AND c = ?";
statement.parameters[0] = compare_a;
statement.parameters[1] = compare_b;
statement.parameters[2] = compare_c;

as you can see we have a query that accepts any parameter values, including null and because just comparing null value requires different syntax you may find this query to return unexpected results (if any) when either of parameters will be null.

The solution or workaround is to not to allow in database design for null values but some defaults instead i.e. “Not set” etc. and use this values in the query, sometimes it is not possible to change database schema, but always you can change the query:)

you can improve above simple example by adding logic branching and checking for null parameter:

var compare_a:int= 6;
var compare_b:String = null;
var compare_c:int= 1;
//expected 1 row in result
var statement:SQLStatement = new SQLStatement();
statement.sqlConnection = m_oConnection;

if (compare_b != null)
{
	statement.text = "SELECT * FROM t1 WHERE a = ? AND b = ? AND c = ?";

	statement.parameters[0] = compare_a;
	statement.parameters[1] = compare_b;
	statement.parameters[2] = compare_c;
}
else
{
	statement.text = "SELECT * FROM t1 WHERE a = ? AND b IS NULL AND c = ?"

	statement.parameters[0] = compare_a;
	statement.parameters[1] = compare_c;
}

It works but only for this simple case, it will be much harder for variable number of parameters where some or all can have null values. To actually make it useful one needs to built some classes that will support dynamically created SQLStatements for which this behaviour will be done automatically, i.e.

var query:SelectQuery = new SelectQuery();
//
query.addFilter("a",6);
query.addFilter("b",null);
query.addFilter("c",1);
//

and in the hypothetical SelectQuery class:

/**
 * Returns string for filtering the query (WHERE clause).
 */
protected function getFilters():String
{
	var _sWhere:String = "";
	if (m_aFilters.length == 0) return _sWhere;
	_sWhere = " WHERE ";
	//m_aFilters is an array of 
       //Objects {column_name, compare_value}
	for (var i:int = 0; i < m_aFilters.length; i++)
	{
		var filter:Object = m_aFilters[i];
		_sWhere += filter.column_name;
		
		if (filter.compare_value == null)
		{
			_sWhere += " IS NULL ";
		}
		else
		{
			_sWhere += " = ? ";
                        //? will be used in parameters 
                        //object;
		}
		if(i < m_aFilters.length-1) _sWhere += " AND ";
                            //for simplicity 
                            //it will always group with AND
	}
	return _sWhere;
}
//calling this method with previous snippets data (addFilter), 
//produces: WHERE a = ?  AND b IS NULL  AND c = ? 

Happy coding:)