Обработка NULL в SQLite

Цель состоит в том, чтобы заставить SQLite обращаться с NULL совместимым со стандартами способом. Но описания в стандартах SQL о том, как обращаться с NULL кажутся неоднозначными. Не ясно из документов стандартов точно, как NULL должен быть обработан при всех обстоятельствах.

Таким образом вместо того, чтобы пользоваться документами стандартов, различные популярные движки SQL были проверены, чтобы видеть, как они обращаются с NULL. Идея состояла в том, чтобы заставить SQLite работать как все другие системы.Сценарий тестирования SQL разрабатывался и управлялся волонтерами на различных SQL RDBMS и результаты тех тестов использовались, чтобы вывести, как каждый движок обработал NULL. Оригинальные тесты были запущены в мае 2002 г. Копия сценария тестирования есть в конце этого документа.

SQLite был первоначально закодирован таким способом, которым ответ на все вопросы в диаграмме ниже будет "Да". Но эксперименты, которыми управляют на других движках SQL, показали, что ни один из них так не делает. Таким образом, SQLite был изменен, чтобы работать как Oracle, PostgreSQL и DB2. NULL все еще отличен в колонке UNIQUE. Это кажется несколько произвольным, но желание быть совместимым с другими перевесило возражения.

Возможно заставить SQLite рассматривать NULL как отличные в целях SELECT DISTINCT и UNION. Чтобы сделать так, нужно изменить #define NULL_ALWAYS_DISTINCT в исходном файле sqliteInt.h и пересобрать пакет.

Обновлено 2003-07-13: После того, как этот документ был первоначально написан, некоторые проверенные ядра базы данных были обновлены, и пользователи были достаточно любезны, чтобы послать исправления в диаграмму ниже. Оригинальные данные показали большое разнообразие поведений, но со временем диапазон поведений сходился к модели PostgreSQL/Oracle. Единственная значительная разница в том, что Informix и MS-SQL оба рассматривают NULL как одинаковый в колонке UNIQUE.

То, что NULL отличен для колонок UNIQUE, но неясен для SELECT DISTINCT и UNION, продолжает быть озадачивающим. Кажется, что NULL должен быть отличным везде или нигде. И документы стандартов SQL предлагают, чтобы NULL был отличен везде. На момент написания этого никто не не рассматривает NULL как отличный в SELECT DISTINCT или в UNION.

Следующая таблица показывает результаты экспериментов обработки NULL.

Следующий скрипт использовался, чтобы собрать информацию для приведенной выше таблицы.

-- 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 gathered 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 multiply 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;