Overview
| Comment: | Add documentation on NULL handling in SQLite versus other SQL database engines. (CVS 741) |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: | dd8867fc479891ed8fa36c5cf2359e49c6754a8c |
| User & Date: | drh 2002-09-02 14:11:03 |
Context
|
2002-09-03
| ||
| 19:43 | Always fill in the 5th parameter to sqlite_exec if there is an error. (CVS 742) check-in: 7f8fd5c7 user: drh tags: trunk | |
|
2002-09-02
| ||
| 14:11 | Add documentation on NULL handling in SQLite versus other SQL database engines. (CVS 741) check-in: dd8867fc user: drh tags: trunk | |
| 12:14 | Detect when the test scripts are being run as root and issue an appropriate error message. (CVS 740) check-in: 9ca2c507 user: drh tags: trunk | |
Changes
Changes to main.mk.
304
305
306
307
308
309
310
311
312
313
314
315
316
317
...
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
|
quickstart.html: $(TOP)/www/quickstart.tcl
tclsh $(TOP)/www/quickstart.tcl >quickstart.html
fileformat.html: $(TOP)/www/fileformat.tcl
tclsh $(TOP)/www/fileformat.tcl >fileformat.html
# Files to be published on the website.
#
DOC = \
index.html \
sqlite.html \
changes.html \
................................................................................
speed.html \
faq.html \
formatchng.html \
conflict.html \
omitted.html \
datatypes.html \
quickstart.html \
fileformat.html
doc: $(DOC)
mkdir -p doc
mv $(DOC) doc
install: sqlite libsqlite.a sqlite.h
mv sqlite /usr/bin
|
>
>
>
|
>
|
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
...
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
|
quickstart.html: $(TOP)/www/quickstart.tcl tclsh $(TOP)/www/quickstart.tcl >quickstart.html 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. # DOC = \ index.html \ sqlite.html \ changes.html \ ................................................................................ speed.html \ faq.html \ formatchng.html \ conflict.html \ omitted.html \ datatypes.html \ quickstart.html \ fileformat.html \ nulls.html doc: $(DOC) mkdir -p doc mv $(DOC) doc install: sqlite libsqlite.a sqlite.h mv sqlite /usr/bin |
Changes to www/index.tcl.
1
2
3
4
5
6
7
8
9
10
11
...
135
136
137
138
139
140
141
142
143
144
145
146
147
148
|
#
# 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 $}
puts {<html>
<head><title>SQLite: An Embeddable SQL Database Engine</title></head>
<body bgcolor=white>
<h1 align=center>SQLite<br>An Embeddable SQL Database Engine</h1>
<p align=center>}
puts "This page was last modified on [lrange $rcsid 3 4] UTC<br>"
................................................................................
5 minutes or less.</li>
<li><a href="faq.html">Frequently Asked Questions</a> are available online.</li>
<li>Information on the <a href="sqlite.html">sqlite</a>
command-line utility.</li>
<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="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>
<li>A description of the <a href="opcode.html">virtual machine</a> that
SQLite uses to access the database.</li>
<li>A description of the
<a href="fileformat.html">database file format</a> used by SQLite.
|
|
>
>
|
1
2
3
4
5
6
7
8
9
10
11
...
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
|
#
# Run this TCL script to generate HTML for the index.html file.
#
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>
<body bgcolor=white>
<h1 align=center>SQLite<br>An Embeddable SQL Database Engine</h1>
<p align=center>}
puts "This page was last modified on [lrange $rcsid 3 4] UTC<br>"
................................................................................
5 minutes or less.</li>
<li><a href="faq.html">Frequently Asked Questions</a> are available online.</li>
<li>Information on the <a href="sqlite.html">sqlite</a>
command-line utility.</li>
<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>
<li>A description of the <a href="opcode.html">virtual machine</a> that
SQLite uses to access the database.</li>
<li>A description of the
<a href="fileformat.html">database file format</a> used by SQLite.
|
Added www/nulls.tcl.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 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> } |