http://www.PHPchina.com/archives/view-33860-1.html
各种流行的sql引擎被用来测试，看它们是如何处理空值的。我的目的是想sqlite像其他引擎一样工作。志愿者们开发了sql的测试脚本并使之在sql RDBMSes上运行，运用测试的结果来推论空值在各种引擎上是如何被处理的。
我的目标是使sqlite用一种标准和顺从的方法来处理空值。但是在sql标准中关于如何处理空值的描述似乎不太明确。从标准文档中，我们不太容易弄清楚空值在所有场合下是如何被处理的。
所以标准文档被取代，各种流行的sql引擎被用来测试，看它们是如何处理空值的。我的目的是想sqlite像其他引擎一样工作。志愿者们开发了sql的测试脚本并使之在sql RDBMSes上运行，运用测试的结果来推论空值在各种引擎上是如何被处理的。最初的测试是在2002年5月运行的。测试脚本的副本在这篇文档的最后。
sqlite最初是这样编译的，对于下面表格中的所有问题，它的答案都是"Yes"。但是在其它sql引擎上的测试表明没有一个引擎是这样工作的。所以sqlite被改进了，改进后它像Oracle,Postgresql,and DB2一样工作。改进后，对于SELECT DISTINCT 语句和SELECT中的UNIQUE操作符，空值是模糊的。在UNIQUE列中空值仍然是清晰的。这看起来有些独裁的意思，但是使sqlite和其它数据库引擎兼容似乎比这个缺陷更重要。
为了SELECT DISTINCT和UNION，使sqlite认为空值是清晰的是有可能的。但是你需要在sqliteInt.h原文件中改变NULL_ALWAYS_DISTINCT #define的值，并重新编译。
下面的表格显示了空处理实验的结果。
| 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 |
第三个表格
下面的脚本被用来收集关于上面表格的信息。
-- 我认为sql关于空值的处理是不定的，所以不能靠逻辑来推断，必须同过实验来发现结果。为了实现这个目标，我已经准备了下列的脚本来测试不同的sql数据库如何处理空值。
我的目标是使用这个脚本收集的信息来使sqlite尽量像其它数据库一样工作。 如果你可以在你的数据库引擎里运行这个脚本并把结果发送到 drh@hwaci.com ，那将是对我的莫大帮助。
请标识你为这个测试所使用的引擎。谢谢。如果你为了在你的数据库引擎上运行测试而修改了原来的脚本，请把你所修改的脚本和结果一起发送过来。
-- 创建一个具有数据的测试表格
create table t1(a int,b int,c int);
insert into t1 values(1,0);
insert into t1 values(2,1);
insert into t1 values(3,1,0);
insert into t1 values(4,1);
insert into t1 values(5,null,0);
insert into t1 values(6,1);
insert into t1 values(7,null);-- 看CASE在测试表达式中如何处理空值
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;-- 如果你用空值乘以零会发生什么结果？
select a+80,b*0 from t1;
select a+90,b*c from t1;-- 其它操作符和空值会发生什么？
select a+100,b+c from t1;-- 关于集合操作符的测试
select count(*),count(b),sum(b),avg(b),min(b),max(b) from t1;-- 在WHERE子句中检查空值的状态
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);-- 在DISTINCT查询中检查空值的状态
select distinct b from t1;-- 在UNION查询中检查空值的状态
select b from t1 union select b from t1;-- 用unique列创建一个新表格，检查空值是否被认为是清晰的。
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;更新于2003-07-13:这篇文档写的很早，一些被测试的数据库引擎已经被更新，忠实地使用者也发送了一些关于下面表格的修正意见。原始数据显示了各种不同的状态，但是随着时间的变化，数据的状态已经逐渐向Postgresql/Oracle模式汇合。唯一的突出的不同是Informix and MS-sql在UNIQUE列中都认为空值是模糊的。 令人迷惑的一点是，NULLs对于UNIQUE列是清晰的，但对于 SELECT DISTINCT和UNION是模糊的。空值应该是清晰或模糊都可以。但sql标准文档建议空值在所有地方都是清晰的。但在这篇作品中，被测试的sql引擎认为在SELECT DISTINCT或在UNION中，空值是清晰的。
原文链接：https://www.f2er.com/sqlite/201511.html