SQLite中的空处理的比较，SQLite处理比较
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 |
第三个表格
| 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. |
下面的脚本被用来收集关于上面表格的信息。
-- 我认为SQL关于空值的处理是不定的，所以不能靠逻辑来推断，必须同过实验来发现结果。为了实现这个目标，我已经准备了下列的脚本来测试不同的SQL数据库如何处理空值。
我的目标是使用这个脚本收集的信息来使SQLite尽量像其它数据库一样工作。 如果你可以在你的数据库引擎里运行这个脚本并把结果发送到drh@hwaci.com，那将是对我的莫大帮助。
请标识你为这个测试所使用的引擎。谢谢。如果你为了在你的数据库引擎上运行测试而修改了原来的脚本，请把你所修改的脚本和结果一起发送过来。
-- 创建一个具有数据的测试表格
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);-- 看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中，空值是清晰的。