SQLiteと他のデータベース・エンジンのNULLの取り扱いの違い

目標は、 SQLiteにNULLを標準規格に対応した方法で扱わせることです。 しかし、どうNULLを扱うかに関するSQL標準規格の説明文はあいまいに見えます。 標準規格文書は、すべての環境でNULLがどう扱われるべきか正確に明記していません。

したがって、標準規格文書に追従する代わりに、 各種の著名な SQLエンジンがどうNULLを扱うか調査しました。 他のすべてのエンジンと同じように扱わせるようにSQLiteを作り上げようと考えたのです。 様々なSQL RDBMS用のSQLテスト・スクリプトがボランティアによって開発されて、 実行されました。 そして、それらのテスト結果は、 各エンジンがどうNULL値を処理したかを推論するのに使用されました。 最初のテストは2002年5月に実行されました。 このドキュメントの終わりにテスト・スクリプトのコピーがあります。

SQLiteは元々、下記の表の質問への答えが全て「はい」になるように コーディングされていました。 しかし、他のSQLエンジンで実行された実験は、 それらのいずれもこのように働かないことを示しました。 したがって、SQLiteは、オラクル、PostgreSQL、およびDB2と同じように動作するように 変更されました。 これによって、SELECT DISTINCTステートメントの用途とSELECTのUNION演算子で NULLは同一になりました。 しかしながら、UNIQUEカラムでは、NULLは個別です。 これは多少強引に思えますが、他のエンジンとの互換性への願望は、異論よりも重要でした。

SQLiteに、SELECT DISTINCTとUNIONの目的で、NULLを個別として扱わせることは可能です。 そうするためには、 sqliteInt.h の#define NULL_ALWAYS_DISTINCTの値を変更して 再コンパイルします。

2003年7月13日追記 このドキュメントが最初に書かれて以来、 テストされたデータベース・エンジンのいくつかを更新しています。 そして、ユーザは修正を以下の表に送るほど親切です。 オリジナルのデータはさまざまな振る舞いを示しましたが、 時間がたつにつれて、振る舞いの範囲は PostgreSQL / オラクル・モデルに向かって 一点に集まりました。 唯一の重要な違いは、Informix と MS-SQL が UNIQUE カラムで NULL を一つにすることです。

NULL が UNIQUE カラムにおいては個別で、 SELECT DISTINCT と UNION では同一であるという事実は、当惑させ続けます。 NULL はどんな場合でも個別であるべきだと思えます。 そして、SQL 標準規格文書は、NULL がいたる所で個別であるべきだと示唆します。 しかし、これを書いている時点で、テストしたどのSQLエンジンも SELECT DISTINCT ステートメントや UNION で NULL を個別のものとしませんでした。

以下の表は NULLの取り扱い実験の結果を示します。

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 drh@hwaci.com, 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;