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

Обработка NULL в SQLite по сравнению с другими СУБД

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

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

SQLite изначально был написан таким образом, что ответ на все вопросы в таблице ниже был бы «Да». Но эксперименты, проведенные с другими SQL-движками, показали, что ни один из них не работал таким образом. Поэтому SQLite был изменен, чтобы работать так же, как Oracle, PostgreSQL и DB2. Это включало в себя то, что NULL не был различимым для целей оператора SELECT DISTINCT и оператора UNION в SELECT. NULL по-прежнему различим в столбце UNIQUE. Это кажется несколько произвольным, но стремление к совместимости с другими движками перевешивало это возражение.

Можно заставить SQLite обрабатывать NULL как различимые для целей SELECT DISTINCT и UNION. Для этого нужно изменить значение определения NULL_ALWAYS_DISTINCT в файле исходного кода sqliteInt.h и перекомпилировать.

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

Тот факт, что NULL различим для столбцов UNIQUE, но не различим для SELECT DISTINCT и UNION, продолжает оставаться загадкой. Похоже, что NULL должен быть либо везде различимым, либо нигде. А документы стандартов SQL предполагают, что NULL должен быть везде различимым. Тем не менее, на момент написания этого документа ни один протестированный SQL-движок не обрабатывает 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;