Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add documentation on NULL handling in SQLite versus other SQL database engines. (CVS 741) |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
dd8867fc479891ed8fa36c5cf2359e49 |
| User & Date: | drh 2002-09-02 14:11:03.000 |
Context
|
2002-09-03
| ||
| 19:43 | Always fill in the 5th parameter to sqlite_exec if there is an error. (CVS 742) (check-in: 7f8fd5c75d 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: dd8867fc47 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: 9ca2c50770 user: drh tags: trunk) | |
Changes
Changes to main.mk.
| ︙ | ︙ | |||
304 305 306 307 308 309 310 311 312 313 314 315 316 317 | 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 \ | > > > | 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 | 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 \ |
| ︙ | ︙ | |||
328 329 330 331 332 333 334 | speed.html \ faq.html \ formatchng.html \ conflict.html \ omitted.html \ datatypes.html \ quickstart.html \ | | > | 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 | 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 | # # Run this TCL script to generate HTML for the index.html file. # | | | 1 2 3 4 5 6 7 8 9 10 11 |
#
# 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>"
|
| ︙ | ︙ | |||
135 136 137 138 139 140 141 142 143 144 145 146 147 148 |
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.
| > > | 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 |
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>
}
|