diff options
| author | drh <drh@noemail.net> | 2002-09-02 14:11:02 +0000 |
|---|---|---|
| committer | drh <drh@noemail.net> | 2002-09-02 14:11:02 +0000 |
| commit | 96f453107784200db76c2f000573c294228dd4ed (patch) | |
| tree | 074adf6e4c9f2e595a5119f729a54069e6101d71 | |
| parent | 0e1cfb857482def131f258216d0e61835ecdc342 (diff) | |
| download | sqlite-96f453107784200db76c2f000573c294228dd4ed.tar.gz sqlite-96f453107784200db76c2f000573c294228dd4ed.zip | |
Add documentation on NULL handling in SQLite versus other SQL database
engines. (CVS 741)
FossilOrigin-Name: dd8867fc479891ed8fa36c5cf2359e49c6754a8c
| -rw-r--r-- | main.mk | 6 | ||||
| -rw-r--r-- | manifest | 15 | ||||
| -rw-r--r-- | manifest.uuid | 2 | ||||
| -rw-r--r-- | www/index.tcl | 4 | ||||
| -rw-r--r-- | www/nulls.tcl | 261 |
5 files changed, 278 insertions, 10 deletions
@@ -308,6 +308,9 @@ quickstart.html: $(TOP)/www/quickstart.tcl fileformat.html: $(TOP)/www/fileformat.tcl tclsh $(TOP)/www/fileformat.tcl >fileformat.html +nulls.html: $(TOP)/www/nulls.tcl + tclsh $(TOP)/www/nulls.tcl >nulls.html + # Files to be published on the website. # @@ -332,7 +335,8 @@ DOC = \ omitted.html \ datatypes.html \ quickstart.html \ - fileformat.html + fileformat.html \ + nulls.html doc: $(DOC) mkdir -p doc @@ -1,5 +1,5 @@ -C Detect\swhen\sthe\stest\sscripts\sare\sbeing\srun\sas\sroot\sand\sissue\san\sappropriate\nerror\smessage.\s(CVS\s740) -D 2002-09-02T12:14:51 +C Add\sdocumentation\son\sNULL\shandling\sin\sSQLite\sversus\sother\sSQL\sdatabase\nengines.\s(CVS\s741) +D 2002-09-02T14:11:03 F Makefile.in 420fada882179cb72ffd07313f3fd693f9f06640 F Makefile.linux-gcc b86a99c493a5bfb402d1d9178dcdc4bd4b32f906 F README f1de682fbbd94899d50aca13d387d1b3fd3be2dd @@ -14,7 +14,7 @@ F doc/report1.txt a031aaf37b185e4fa540223cb516d3bccec7eeac F install-sh 9d4de14ab9fb0facae2f48780b874848cbf2f895 F libtool c56e618713c9510a103bda6b95f3ea3900dcacd6 F ltmain.sh e9ed72eb1d690f447c13945eaf69e28af531eda1 -F main.mk 8e34134476c039c89bb404f2712bcbb2f044bdfe +F main.mk 14a6933b738143bf939e9c04c59163d738239fae F publish.sh a7a8d23e6525bd25d4f5ba9b0fc6edc107d94050 F spec.template 238f7db425a78dc1bb7682e56e3834c7270a3f5e F sqlite.1 83f4a9d37bdf2b7ef079a82d54eaf2e3509ee6ea @@ -140,9 +140,10 @@ F www/dynload.tcl 02eb8273aa78cfa9070dd4501dca937fb22b466c F www/faq.tcl 207d3e31597c63ed3bbecd58aaeaa38c53d39dd4 F www/fileformat.tcl a4b5c2c6e89b7d42d09f97fd4d7bbd39cbf24936 F www/formatchng.tcl b4449e065d2da38b6563bdf12cf46cfe1d4d765e -F www/index.tcl 33881038e9664a36e56df3b80ef0828594c8dcd9 +F www/index.tcl 190f767c4b0bf44f75bf4af023d644c3e7a1e579 F www/lang.tcl bf9d830aa0042718ae157e25fc7539f5378c46ff F www/mingw.tcl f1c7c0a7f53387dd9bb4f8c7e8571b7561510ebc +F www/nulls.tcl 29497dac2bc5b437aa7e2e94577dad4d8933ed26 F www/omitted.tcl 118062f40a203fcb88b8d68ef1d7c0073ac191ec F www/opcode.tcl 33c5f2061a05c5d227c72b84c080b3bf74c74f8b F www/quickstart.tcl fde79aa2de20074842b60f780800cdeee6a5dec2 @@ -150,7 +151,7 @@ F www/speed.tcl a20a792738475b68756ea7a19321600f23d1d803 F www/sqlite.tcl ae3dcfb077e53833b59d4fcc94d8a12c50a44098 F www/tclsqlite.tcl 1db15abeb446aad0caf0b95b8b9579720e4ea331 F www/vdbe.tcl 2013852c27a02a091d39a766bc87cff329f21218 -P 8c2a0836980341faa479cfe6c716409e6057367d -R d69d74396bd3d8fa6110e1bb4d145b9c +P 9ca2c507704f85446d873d5e8429554f2ad4df9e +R a486780ea79cbf7fa41ff4e723ebd4b5 U drh -Z 5b7e102d83ef9c4dd3f46fe8bd1002c5 +Z 27d152bf1210ee491944a9449323bf4b diff --git a/manifest.uuid b/manifest.uuid index e3f162774..30f3f0087 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -9ca2c507704f85446d873d5e8429554f2ad4df9e
\ No newline at end of file +dd8867fc479891ed8fa36c5cf2359e49c6754a8c
\ No newline at end of file diff --git a/www/index.tcl b/www/index.tcl index a0ebe7e69..2d5cb8fbf 100644 --- a/www/index.tcl +++ b/www/index.tcl @@ -1,7 +1,7 @@ # # Run this TCL script to generate HTML for the index.html file. # -set rcsid {$Id: index.tcl,v 1.67 2002/08/18 19:09:24 drh Exp $} +set rcsid {$Id: index.tcl,v 1.68 2002/09/02 14:11:04 drh Exp $} puts {<html> <head><title>SQLite: An Embeddable SQL Database Engine</title></head> @@ -139,6 +139,8 @@ puts {<h2>Documentation</h2> <li>SQLite is <a href="datatypes.html">typeless</a>. <li>The <a href="lang.html">SQL Language</a> subset understood by SQLite.</li> <li>The <a href="c_interface.html">C/C++ Interface</a>.</li> +<li>The <a href="nulls.html">NULL handling</a> in SQLite versus + other SQL database engines.</li> <li>The <a href="tclsqlite.html">Tcl Binding</a> to SQLite.</li> <li>The <a href="arch.html">Architecture of the SQLite Library</a> describes how the library is put together.</li> diff --git a/www/nulls.tcl b/www/nulls.tcl new file mode 100644 index 000000000..ee7b5f92f --- /dev/null +++ b/www/nulls.tcl @@ -0,0 +1,261 @@ +# +# Run this script to generated a nulls.html output file +# +set rcsid {$Id: nulls.tcl,v 1.1 2002/09/02 14:11:04 drh Exp $} + +puts {<html> +<head> +<title>NULL Handling In SQLite Versus Other Database Engines</title> +</head> +<body bgcolor="white"> +<h1 align="center"> +NULL Handling in SQLite Versus Other Database Engines +</h1> +} +puts "<p align=\"center\"> +(This page was last modified on [lrange $rcsid 3 4] UTC) +</p>" + +puts { +<p> +The goal is +to make SQLite handle NULLs in a standards-compliant way. +But the descriptions in the SQL standards on how to handle +NULLs seem ambiguous. +It is not clear from the standards documents exactly how NULLs should +be handled in all circumstances. +</p> + +<p> +So instead of going by the standards documents, various popular +SQL engines were tested to see how they handle NULLs. The idea +was to make SQLite work like all the other engines. +A SQL test script was developed and run by volunteers on various +SQL RDBMSes and the results of those tests were used to deduce +how each engine processed NULL values. +A copy of the test script is found at the end of this document. +</p> + +<p> +SQLite was originally coded in such a way that the answer to +all questions in the chart below would be "Yes". But the +expriments run on other SQL engines showed that none of them +worked this way. So SQLite was modified to work the same as +Oracle, PostgreSQL, and DB2. This involved making NULLs +indistinct for the purposes of the SELECT DISTINCT statement and +for the UNION operator in a SELECT. NULLs are still distinct +in a UNIQUE index. This seems somewhat arbitrary, but the desire +to be compatible with other engines outweighted that objection. +</p> + +<p> +It is possible to make SQLite treat NULLs as distinct for the +purposes of the SELECT DISTINCT and UNION. To do so, one should +change the value of the NULL_ALWAYS_DISTINCT #define in the +<tt>sqliteInt.h</tt> source file and recompile. +</p> + +<p> +The following table shows the results of the NULL handling experiments. +</p> + +<table border=1 cellpadding=5 width="100%"> +<tr><th>  </th> +<th>SQLite</th> +<th>PostgreSQL</th> +<th>Oracle</th> +<th>Informix</th> +<th>DB2</th> +<th>MS-SQL</th> +<th>MySQL</th> +<th>OCELOT</th> +<th>Firebird</th> +</tr> + +<tr><td>Adding anything to null gives null</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +</tr> +<tr><td>Multiplying null by zero gives null</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +</tr> +<tr><td>nulls are distinct in a UNIQUE index</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#c7a9a9">No</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#c7a9a9">No</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +</tr> +<tr><td>nulls are distinct in SELECT DISTINCT</td> +<td valign="center" align="center" bgcolor="#c7a9a9">No</td> +<td valign="center" align="center" bgcolor="#c7a9a9">No</td> +<td valign="center" align="center" bgcolor="#c7a9a9">No</td> +<td valign="center" align="center" bgcolor="#c7a9a9">No</td> +<td valign="center" align="center" bgcolor="#c7a9a9">No</td> +<td valign="center" align="center" bgcolor="#c7a9a9">No</td> +<td valign="center" align="center" bgcolor="#c7a9a9">No</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#aaaad2">(Note 1)</td> +</tr> +<tr><td>nulls are distinct in a UNION</td> +<td valign="center" align="center" bgcolor="#c7a9a9">No</td> +<td valign="center" align="center" bgcolor="#c7a9a9">No</td> +<td valign="center" align="center" bgcolor="#c7a9a9">No</td> +<td valign="center" align="center" bgcolor="#c7a9a9">No</td> +<td valign="center" align="center" bgcolor="#c7a9a9">No</td> +<td valign="center" align="center" bgcolor="#c7a9a9">No</td> +<td valign="center" align="center" bgcolor="#aaaad2">(Note 3)</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#aaaad2">(Note 1)</td> +</tr> +<tr><td>"CASE WHEN null THEN 1 ELSE 0 END" is 0?</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#aaaad2">(Note 2)</td> +</tr> +<tr><td>"null OR true" is true</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#c7a9a9">No</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +</tr> +<tr><td>"not (null AND false)" is true</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +<td valign="center" align="center" bgcolor="#c7a9a9">No</td> +<td valign="center" align="center" bgcolor="#c7a9a9">No</td> +<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td> +</tr> +</table> + +<table border=0 align="right" cellpadding=0 cellspacing=0> +<tr> +<td valign="top" rowspan=3>Notes: </td> +<td>1. </td> +<td>Firebird omits all NULLs from SELECT DISTINCT and from UNION.</td> +</tr> +<tr><td>2. </td> +<td>Test data unavailable.</td></tr> +<tr><td>3. </td> +<td>The version of MySQL tested (3.23.41) does not support UNION.</td></tr> +</table> +<br clear="both"> + +<p> </p> +<p> +The following script was used to gather information for the table +above. +</p> + +<pre> +-- 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; +</pre> + +<p><hr /></p> +<p><a href="index.html"><img src="/goback.jpg" border=0 /> +Back to the SQLite Home Page</a> +</p> +</body> +</html> +} |