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 | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
17bdfeb284880d82cd546e6c191c3a37 |
| User & Date: | drh 2003-08-13 11:29:24 |
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: 3563e9cf user: drh tags: trunk | |
|
2003-08-13
| ||
| 11:29 | Update the NULL-handling chart with new information about Firebird. (CVS 1072) check-in: 17bdfeb2 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: 02fac304 user: drh tags: trunk | |
Changes
Changes to www/nulls.tcl.
1 2 3 4 5 6 7 8 9 10 11 .. 29 30 31 32 33 34 35 36 37 38 39 40 41 42 .. 50 51 52 53 54 55 56 57 58 59 60 61 62 63 ... 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 ... 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 |
#
# 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">
................................................................................
<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
................................................................................
<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>  </th>
................................................................................
<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>
................................................................................
</tr>
</table>
<table border=0 align="right" cellpadding=0 cellspacing=0>
<tr>
<td valign="top" rowspan=5>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>
|
| > > > > > > > > > > > > > > > > > > > > > > > > | | | | > |
1 2 3 4 5 6 7 8 9 10 11 .. 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 .. 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 ... 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 ... 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 |
#
# 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">
................................................................................
<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
................................................................................
<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>  </th>
................................................................................
<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>
................................................................................
</tr>
</table>
<table border=0 align="right" cellpadding=0 cellspacing=0>
<tr>
<td valign="top" rowspan=5>Notes: </td>
<td>1. </td>
<td>Older versions of 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>
|