Debian Codegraph @

www/nulls.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>NULL Handling in SQLite</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118
119
120 <h2>NULL Handling in SQLite Versus Other Database Engines</h2>
121
122 <p>
123 The goal is
124 to make SQLite handle NULLs in a standards-compliant way.
125 But the descriptions in the SQL standards on how to handle
126 NULLs seem ambiguous.
127 It is not clear from the standards documents exactly how NULLs should
128 be handled in all circumstances.
129 </p>
130
131 <p>
132 So instead of going by the standards documents, various popular
133 SQL engines were tested to see how they handle NULLs. The idea
134 was to make SQLite work like all the other engines.
135 An SQL test script was developed and run by volunteers on various
136 SQL RDBMSes and the results of those tests were used to deduce
137 how each engine processed NULL values.
138 The original tests were run in May of 2002.
139 A copy of the test script is found at the end of this document.
140 </p>
141
142 <p>
143 SQLite was originally coded in such a way that the answer to
144 all questions in the chart below would be "Yes". But the
145 experiments run on other SQL engines showed that none of them
146 worked this way. So SQLite was modified to work the same as
147 Oracle, PostgreSQL, and DB2. This involved making NULLs
148 indistinct for the purposes of the SELECT DISTINCT statement and
149 for the UNION operator in a SELECT. NULLs are still distinct
150 in a UNIQUE column. This seems somewhat arbitrary, but the desire
151 to be compatible with other engines outweighed that objection.
152 </p>
153
154 <p>
155 It is possible to make SQLite treat NULLs as distinct for the
156 purposes of the SELECT DISTINCT and UNION. To do so, one should
157 change the value of the NULL_ALWAYS_DISTINCT #define in the
158 <tt>sqliteInt.h</tt> source file and recompile.
159 </p>
160
161 <blockquote>
162 <p>
163 <i>Update 2003-07-13:</i>
164 Since this document was originally written some of the database engines
165 tested have been updated and users have been kind enough to send in
166 corrections to the chart below. The original data showed a wide variety
167 of behaviors, but over time the range of behaviors has converged toward
168 the PostgreSQL/Oracle model. The only significant difference
169 is that Informix and MS-SQL both treat NULLs as
170 indistinct in a UNIQUE column.
171 </p>
172
173 <p>
174 The fact that NULLs are distinct for UNIQUE columns but are indistinct for
175 SELECT DISTINCT and UNION continues to be puzzling. It seems that NULLs
176 should be either distinct everywhere or nowhere. And the SQL standards
177 documents suggest that NULLs should be distinct everywhere. Yet as of
178 this writing, no SQL engine tested treats NULLs as distinct in a SELECT
179 DISTINCT statement or in a UNION.
180 </p>
181 </blockquote>
182
183
184 <p>
185 The following table shows the results of the NULL handling experiments.
186 </p>
187
188 <table border=1 cellpadding=3 width="100%">
189 <tr><th>&nbsp;&nbsp;</th>
190 <th>SQLite</th>
191 <th>PostgreSQL</th>
192 <th>Oracle</th>
193 <th>Informix</th>
194 <th>DB2</th>
195 <th>MS-SQL</th>
196 <th>OCELOT</th>
197 </tr>
198
199 <tr><td>Adding anything to null gives null</td>
200 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
201 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
202 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
203 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
204 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
205 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
206 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
207 </tr>
208 <tr><td>Multiplying null by zero gives null</td>
209 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
210 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
211 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
212 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
213 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
214 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
215 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
216 </tr>
217 <tr><td>nulls are distinct in a UNIQUE column</td>
218 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
219 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
220 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
221 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
222 <td valign="center" align="center" bgcolor="#aaaad2">(Note 4)</td>
223 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
224 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
225 </tr>
226 <tr><td>nulls are distinct in SELECT DISTINCT</td>
227 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
228 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
229 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
230 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
231 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
232 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
233 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
234 </tr>
235 <tr><td>nulls are distinct in a UNION</td>
236 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
237 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
238 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
239 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
240 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
241 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
242 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
243 </tr>
244 <tr><td>"CASE WHEN null THEN 1 ELSE 0 END" is 0?</td>
245 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
246 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
247 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
248 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
249 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
250 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
251 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
252 </tr>
253 <tr><td>"null OR true" is true</td>
254 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
255 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
256 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
257 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
258 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
259 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
260 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
261 </tr>
262 <tr><td>"not (null AND false)" is true</td>
263 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
264 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
265 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
266 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
267 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
268 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
269 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
270 </tr>
271 </table>
272
273 <table border=1 cellpadding=3 width="100%">
274 <tr><th>&nbsp;&nbsp;</th>
275 <th>MySQL<br>3.23.41</th>
276 <th>MySQL<br>4.0.16</th>
277 <th>Firebird</th>
278 <th>SQL<br>Anywhere</th>
279 <th>Borland<br>Interbase</th>
280 </tr>
281
282 <tr><td>Adding anything to null gives null</td>
283 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
284 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
285 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
286 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
287 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
288 </tr>
289 <tr><td>Multiplying null by zero gives null</td>
290 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
291 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
292 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
293 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
294 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
295 </tr>
296 <tr><td>nulls are distinct in a UNIQUE column</td>
297 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
298 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
299 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
300 <td valign="center" align="center" bgcolor="#aaaad2">(Note 4)</td>
301 <td valign="center" align="center" bgcolor="#aaaad2">(Note 4)</td>
302 </tr>
303 <tr><td>nulls are distinct in SELECT DISTINCT</td>
304 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
305 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
306 <td valign="center" align="center" bgcolor="#c7a9a9">No (Note 1)</td>
307 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
308 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
309 </tr>
310 <tr><td>nulls are distinct in a UNION</td>
311 <td valign="center" align="center" bgcolor="#aaaad2">(Note 3)</td>
312 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
313 <td valign="center" align="center" bgcolor="#c7a9a9">No (Note 1)</td>
314 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
315 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
316 </tr>
317 <tr><td>"CASE WHEN null THEN 1 ELSE 0 END" is 0?</td>
318 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
319 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
320 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
321 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
322 <td valign="center" align="center" bgcolor="#aaaad2">(Note 5)</td>
323 </tr>
324 <tr><td>"null OR true" is true</td>
325 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
326 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
327 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
328 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
329 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
330 </tr>
331 <tr><td>"not (null AND false)" is true</td>
332 <td valign="center" align="center" bgcolor="#c7a9a9">No</td>
333 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
334 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
335 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
336 <td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
337 </tr>
338 </table>
339
340 <table border=0 align="right" cellpadding=0 cellspacing=0>
341 <tr>
342 <td valign="top" rowspan=5>Notes:&nbsp;&nbsp;</td>
343 <td>1.&nbsp;</td>
344 <td>Older versions of firebird omit all NULLs from SELECT DISTINCT
345 and from UNION.</td>
346 </tr>
347 <tr><td>2.&nbsp;</td>
348 <td>Test data unavailable.</td>
349 </tr>
350 <tr><td>3.&nbsp;</td>
351 <td>MySQL version 3.23.41 does not support UNION.</td>
352 </tr>
353 <tr><td>4.&nbsp;</td>
354 <td>DB2, SQL Anywhere, and Borland Interbase
355 do not allow NULLs in a UNIQUE column.</td>
356 </tr>
357 <tr><td>5.&nbsp;</td>
358 <td>Borland Interbase does not support CASE expressions.</td>
359 </tr>
360 </table>
361 <br clear="both">
362
363 <p>&nbsp;</p>
364 <p>
365 The following script was used to gather information for the table
366 above.
367 </p>
368
369 <pre>
370 -- I have about decided that SQL's treatment of NULLs is capricious and cannot be
371 -- deduced by logic. It must be discovered by experiment. To that end, I have
372 -- prepared the following script to test how various SQL databases deal with NULL.
373 -- My aim is to use the information gathered from this script to make SQLite as
374 -- much like other databases as possible.
375 --
376 -- If you could please run this script in your database engine and mail the results
377 -- to me at [email protected], that will be a big help. Please be sure to identify the
378 -- database engine you use for this test. Thanks.
379 --
380 -- If you have to change anything to get this script to run with your database
381 -- engine, please send your revised script together with your results.
382 --
383
384 -- Create a test table with data
385 create table t1(a int, b int, c int);
386 insert into t1 values(1,0,0);
387 insert into t1 values(2,0,1);
388 insert into t1 values(3,1,0);
389 insert into t1 values(4,1,1);
390 insert into t1 values(5,null,0);
391 insert into t1 values(6,null,1);
392 insert into t1 values(7,null,null);
393
394 -- Check to see what CASE does with NULLs in its test expressions
395 select a, case when b<>0 then 1 else 0 end from t1;
396 select a+10, case when not b<>0 then 1 else 0 end from t1;
397 select a+20, case when b<>0 and c<>0 then 1 else 0 end from t1;
398 select a+30, case when not (b<>0 and c<>0) then 1 else 0 end from t1;
399 select a+40, case when b<>0 or c<>0 then 1 else 0 end from t1;
400 select a+50, case when not (b<>0 or c<>0) then 1 else 0 end from t1;
401 select a+60, case b when c then 1 else 0 end from t1;
402 select a+70, case c when b then 1 else 0 end from t1;
403
404 -- What happens when you multiply a NULL by zero?
405 select a+80, b*0 from t1;
406 select a+90, b*c from t1;
407
408 -- What happens to NULL for other operators?
409 select a+100, b+c from t1;
410
411 -- Test the treatment of aggregate operators
412 select count(*), count(b), sum(b), avg(b), min(b), max(b) from t1;
413
414 -- Check the behavior of NULLs in WHERE clauses
415 select a+110 from t1 where b<10;
416 select a+120 from t1 where not b>10;
417 select a+130 from t1 where b<10 OR c=1;
418 select a+140 from t1 where b<10 AND c=1;
419 select a+150 from t1 where not (b<10 AND c=1);
420 select a+160 from t1 where not (c=1 AND b<10);
421
422 -- Check the behavior of NULLs in a DISTINCT query
423 select distinct b from t1;
424
425 -- Check the behavior of NULLs in a UNION query
426 select b from t1 union select b from t1;
427
428 -- Create a new table with a unique column. Check to see if NULLs are considered
429 -- to be distinct.
430 create table t2(a int, b int unique);
431 insert into t2 values(1,1);
432 insert into t2 values(2,null);
433 insert into t2 values(3,null);
434 select * from t2;
435
436 drop table t1;
437 drop table t2;
438 </pre>
439 <p align="center"><small><i>This page was last updated on 2022-04-18 02:55:50Z </small></i></p>
440