SQLite

Check-in [17bdfeb284]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Update the NULL-handling chart with new information about Firebird. (CVS 1072)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 17bdfeb284880d82cd546e6c191c3a37121ec861
User & Date: drh 2003-08-13 11:29:24.000
Context
2003-08-15
13:24
Fix a segfault that occurs in the VACUUM command if run on an empty database with the EMPTY_RESULT_CALLBACKS pragma enabled. Ticket #427. (CVS 1073) (check-in: 3563e9cf9d user: drh tags: trunk)
2003-08-13
11:29
Update the NULL-handling chart with new information about Firebird. (CVS 1072) (check-in: 17bdfeb284 user: drh tags: trunk)
2003-08-10
16:16
Make the sqliteOsCurrentTime() function work for Windows. Code contributed by "e4liberty" on the mailing list. (CVS 1071) (check-in: 02fac304c9 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to www/nulls.tcl. 
1
2
3
4
5
6
7
8
9
10
11

#
# Run this script to generated a nulls.html output file
#
set rcsid {$Id: nulls.tcl,v 1.3 2003/07/07 00:10:40 drh Exp $}

puts {<html>
<head>
<title>NULL Handling In SQLite Versus Other Database Engines</title>
</head>
<body bgcolor="white">
<h1 align="center">




|








1
2
3
4
5
6
7
8
9
10
11

#
# Run this script to generated a nulls.html output file
#
set rcsid {$Id: nulls.tcl,v 1.4 2003/08/13 11:29:24 drh Exp $}

puts {<html>
<head>
<title>NULL Handling In SQLite Versus Other Database Engines</title>
</head>
<body bgcolor="white">
<h1 align="center">
︙︙
29
30
31
32
33
34
35

36
37
38
39
40
41
42

<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








>








29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

<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.
The original tests were run in May of 2002.
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
︙︙
50
51
52
53
54
55
56























57
58
59
60
61
62
63


<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=3 width="100%">
<tr><th>&nbsp&nbsp;</th>








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>








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


<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>

<blockquote>
<p>
<i>Update 2003-07-13:</i>
Since this document was originally written some of the database engines
tested have been updated and users have been kind enough to send in
corrections to the chart below.  The original data showed a wide variety
of behaviors, but over time the range of behaviors has converged toward
the PostgreSQL/Oracle model.  The only significant difference 
is that Informix and MS-SQL both threat NULLs as
indistinct in a UNIQUE column.
</p>

<p>
The fact that NULLs are distinct for UNIQUE columns but are indistinct for
SELECT DISTINCT and UNION continues to be puzzling.  It seems that NULLs
should be either distinct everywhere or nowhere.  And the SQL standards
documents suggest that NULLs should be distinct everywhere.  Yet as of
this writing, no SQL engine tested treats NULLs as distinct in a SELECT
DISTINCT statement or in a UNION.
</p>
</blockquote>


<p>
The following table shows the results of the NULL handling experiments.
</p>

<table border=1 cellpadding=3 width="100%">
<tr><th>&nbsp&nbsp;</th>
︙︙
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

<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 4)</td>
<td valign="center" align="center" bgcolor="#aaaad2">(Note 4)</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="#aaaad2">(Note 1)</td>
<td valign="center" align="center" bgcolor="#c7a9a9">No</td>
<td valign="center" align="center" bgcolor="#c7a9a9">No</td>
</tr>
<tr><td>nulls are distinct in a UNION</td>
<td valign="center" align="center" bgcolor="#aaaad2">(Note 3)</td>
<td valign="center" align="center" bgcolor="#aaaad2">(Note 1)</td>
<td valign="center" align="center" bgcolor="#c7a9a9">No</td>
<td valign="center" align="center" bgcolor="#c7a9a9">No</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="#aaaad2">(Note 2)</td>
<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
<td valign="center" align="center" bgcolor="#aaaad2">(Note 5)</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>








|





|





|








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

<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 4)</td>
<td valign="center" align="center" bgcolor="#aaaad2">(Note 4)</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 (Note 1)</td>
<td valign="center" align="center" bgcolor="#c7a9a9">No</td>
<td valign="center" align="center" bgcolor="#c7a9a9">No</td>
</tr>
<tr><td>nulls are distinct in a UNION</td>
<td valign="center" align="center" bgcolor="#aaaad2">(Note 3)</td>
<td valign="center" align="center" bgcolor="#c7a9a9">No (Note 1)</td>
<td valign="center" align="center" bgcolor="#c7a9a9">No</td>
<td valign="center" align="center" bgcolor="#c7a9a9">No</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="#aaaad2">(Note 5)</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>
︙︙
202
203
204
205
206
207
208
209

210
211
212
213
214
215
216

</tr>
</table>

<table border=0 align="right" cellpadding=0 cellspacing=0>
<tr>
<td valign="top" rowspan=5>Notes:&nbsp;&nbsp;</td>
<td>1.&nbsp;</td>
<td>Firebird omits all NULLs from SELECT DISTINCT and from UNION.</td>

</tr>
<tr><td>2.&nbsp;</td>
<td>Test data unavailable.</td>
</tr>
<tr><td>3.&nbsp;</td>
<td>The version of MySQL tested (3.23.41) does not support UNION.</td>
</tr>








|
>








226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241

</tr>
</table>

<table border=0 align="right" cellpadding=0 cellspacing=0>
<tr>
<td valign="top" rowspan=5>Notes:&nbsp;&nbsp;</td>
<td>1.&nbsp;</td>
<td>Older versions of firebird omits all NULLs from SELECT DISTINCT
and from UNION.</td>
</tr>
<tr><td>2.&nbsp;</td>
<td>Test data unavailable.</td>
</tr>
<tr><td>3.&nbsp;</td>
<td>The version of MySQL tested (3.23.41) does not support UNION.</td>
</tr>
︙︙