SQLite

Check-in [e22e3ab53a]
Login

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

Overview
Comment:Added information on MySQL 4.0.16 to the NULL-handling survey. (CVS 1117)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e22e3ab53a841b26984240760b0438efc83661d7
User & Date: drh 2003-11-08 12:07:01.000
Context
2003-11-11
23:30
Fix a typo in a #define that was commented out. No impact to working code. Ticket #476. (CVS 1119) (check-in: 21c2f96e81 user: drh tags: trunk)
2003-11-08
12:07
Added information on MySQL 4.0.16 to the NULL-handling survey. (CVS 1117) (check-in: e22e3ab53a user: drh tags: trunk)
2003-11-01
01:53
Revised date/time functions - now broken out into a separate source file. See the DateAndTimeFunctions wiki page for additional information. (CVS 1116) (check-in: 68ef9b45bd 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.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">




|








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.5 2003/11/08 12:07:01 drh Exp $}

puts {<html>
<head>
<title>NULL Handling In SQLite Versus Other Database Engines</title>
</head>
<body bgcolor="white">
<h1 align="center">
︙︙
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

<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
</tr>
</table>

<table border=1 cellpadding=3 width="100%">
<tr><th>&nbsp&nbsp;</th>
<th>MySQL</th>

<th>Firebird</th>
<th>SQL Anywhere</th>
<th>Borland Interbase</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>
</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>

</tr>
<tr><td>nulls are distinct in a UNIQUE column</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 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>
<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="#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>
</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>
<tr><td>4.&nbsp;</td>
<td>SQL Anywhere and Borland Interbase 
do not allow NULLs in a UNIQUE column.</td>
</tr>
<tr><td>5.&nbsp;</td>
<td>Borland Interbase does not support CASE expressions.</td>








|
>

|
|



>










>


>






>







>





>










>



>

















|








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

<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
<td valign="center" align="center" bgcolor="#a9c7a9">Yes</td>
</tr>
</table>

<table border=1 cellpadding=3 width="100%">
<tr><th>&nbsp&nbsp;</th>
<th>MySQL<br>3.23.41</th>
<th>MySQL<br>4.0.16</th>
<th>Firebird</th>
<th>SQL<br>Anywhere</th>
<th>Borland<br>Interbase</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>
</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>
</tr>
<tr><td>nulls are distinct in a UNIQUE column</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 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</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</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="#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>
<td valign="center" align="center" bgcolor="#a9c7a9">Yes</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="#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>
<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:&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>MySQL version 3.23.41 does not support UNION.</td>
</tr>
<tr><td>4.&nbsp;</td>
<td>SQL Anywhere and Borland Interbase 
do not allow NULLs in a UNIQUE column.</td>
</tr>
<tr><td>5.&nbsp;</td>
<td>Borland Interbase does not support CASE expressions.</td>
︙︙