NULL Handling in SQLite

NULL Handling in SQLite Versus Other Database Engines

The goal is to make SQLite handle NULLs in a standards-compliant way. But the descriptions in the SQL standards on how to handle NULLs seem ambiguous. It is not clear from the standards documents exactly how NULLs should be handled in all circumstances.

So instead of going by the standards documents, various popular SQL engines were tested to see how they handle NULLs. The idea was to make SQLite work like all the other engines. An SQL test script was developed and run by volunteers on various SQL RDBMSes and the results of those tests were used to deduce how each engine processed NULL values. The original tests were run in May of 2002. A copy of the test script is found at the end of this document.

SQLite was originally coded in such a way that the answer to all questions in the chart below would be "Yes". But the experiments run on other SQL engines showed that none of them worked this way. So SQLite was modified to work the same as Oracle, PostgreSQL, and DB2. This involved making NULLs indistinct for the purposes of the SELECT DISTINCT statement and for the UNION operator in a SELECT. NULLs are still distinct in a UNIQUE column. This seems somewhat arbitrary, but the desire to be compatible with other engines outweighed that objection.

It is possible to make SQLite treat NULLs as distinct for the purposes of the SELECT DISTINCT and UNION. To do so, one should change the value of the NULL_ALWAYS_DISTINCT #define in the sqliteInt.h source file and recompile.

Update 2003-07-13: Since this document was originally written some of the database engines tested have been updated and users have been kind enough to send in corrections to the chart below. The original data showed a wide variety of behaviors, but over time the range of behaviors has converged toward the PostgreSQL/Oracle model. The only significant difference is that Informix and MS-SQL both treat NULLs as indistinct in a UNIQUE column.

The fact that NULLs are distinct for UNIQUE columns but are indistinct for SELECT DISTINCT and UNION continues to be puzzling. It seems that NULLs should be either distinct everywhere or nowhere. And the SQL standards documents suggest that NULLs should be distinct everywhere. Yet as of this writing, no SQL engine tested treats NULLs as distinct in a SELECT DISTINCT statement or in a UNION.

The following table shows the results of the NULL handling experiments.

	SQLite	PostgreSQL	Oracle	Informix	DB2	MS-SQL	OCELOT
Adding anything to null gives null	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Multiplying null by zero gives null	Yes	Yes	Yes	Yes	Yes	Yes	Yes
nulls are distinct in a UNIQUE column	Yes	Yes	Yes	No	(Note 4)	No	Yes
nulls are distinct in SELECT DISTINCT	No	No	No	No	No	No	No
nulls are distinct in a UNION	No	No	No	No	No	No	No
"CASE WHEN null THEN 1 ELSE 0 END" is 0?	Yes	Yes	Yes	Yes	Yes	Yes	Yes
"null OR true" is true	Yes	Yes	Yes	Yes	Yes	Yes	Yes
"not (null AND false)" is true	Yes	Yes	Yes	Yes	Yes	Yes	Yes

	MySQL 3.23.41	MySQL 4.0.16	Firebird	SQL Anywhere	Borland Interbase
Adding anything to null gives null	Yes	Yes	Yes	Yes	Yes
Multiplying null by zero gives null	Yes	Yes	Yes	Yes	Yes
nulls are distinct in a UNIQUE column	Yes	Yes	Yes	(Note 4)	(Note 4)
nulls are distinct in SELECT DISTINCT	No	No	No (Note 1)	No	No
nulls are distinct in a UNION	(Note 3)	No	No (Note 1)	No	No
"CASE WHEN null THEN 1 ELSE 0 END" is 0?	Yes	Yes	Yes	Yes	(Note 5)
"null OR true" is true	Yes	Yes	Yes	Yes	Yes
"not (null AND false)" is true	No	Yes	Yes	Yes	Yes

Notes:	1.	Older versions of firebird omits all NULLs from SELECT DISTINCT and from UNION.
	2.	Test data unavailable.
	3.	MySQL version 3.23.41 does not support UNION.
	4.	DB2, SQL Anywhere, and Borland Interbase do not allow NULLs in a UNIQUE column.
	5.	Borland Interbase does not support CASE expressions.

The following script was used to gather information for the table above.

-- I have about decided that SQL's treatment of NULLs is capricious and cannot be
-- deduced by logic.  It must be discovered by experiment.  To that end, I have 
-- prepared the following script to test how various SQL databases deal with NULL.
-- My aim is to use the information gather from this script to make SQLite as much
-- like other databases as possible.
--
-- If you could please run this script in your database engine and mail the results
-- to me at [email protected], that will be a big help.  Please be sure to identify the
-- database engine you use for this test.  Thanks.
--
-- If you have to change anything to get this script to run with your database
-- engine, please send your revised script together with your results.
--

-- Create a test table with data
create table t1(a int, b int, 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);

-- Check to see what CASE does with NULLs in its test expressions
select a, case when b<>0 then 1 else 0 end from t1;
select a+10, case when not b<>0 then 1 else 0 end from t1;
select a+20, case when b<>0 and c<>0 then 1 else 0 end from t1;
select a+30, case when not (b<>0 and c<>0) then 1 else 0 end from t1;
select a+40, case when b<>0 or c<>0 then 1 else 0 end from t1;
select a+50, case when not (b<>0 or c<>0) then 1 else 0 end from t1;
select a+60, case b when c then 1 else 0 end from t1;
select a+70, case c when b then 1 else 0 end from t1;

-- What happens when you multiple a NULL by zero?
select a+80, b*0 from t1;
select a+90, b*c from t1;

-- What happens to NULL for other operators?
select a+100, b+c from t1;

-- Test the treatment of aggregate operators
select count(*), count(b), sum(b), avg(b), min(b), max(b) from t1;

-- Check the behavior of NULLs in WHERE clauses
select a+110 from t1 where b<10;
select a+120 from t1 where not b>10;
select a+130 from t1 where b<10 OR c=1;
select a+140 from t1 where b<10 AND c=1;
select a+150 from t1 where not (b<10 AND c=1);
select a+160 from t1 where not (c=1 AND b<10);

-- Check the behavior of NULLs in a DISTINCT query
select distinct b from t1;

-- Check the behavior of NULLs in a UNION query
select b from t1 union select b from t1;

-- Create a new table with a unique column.  Check to see if NULLs are considered
-- to be distinct.
create table t2(a int, b int unique);
insert into t2 values(1,1);
insert into t2 values(2,null);
insert into t2 values(3,null);
select * from t2;

drop table t1;
drop table t2;