~ubuntu-branches/ubuntu/wily/sqlite3/wily

onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">

112

113

</form>

114

</div>

115

</table>

116

</div></div></div></div>

117

</td></tr></table>

118

119

120

121

122

<h2>NULL Handling in SQLite Versus Other Database Engines</h2>

123

124

<p>

125

The goal is

126

to make SQLite handle NULLs in a standards-compliant way.

127

But the descriptions in the SQL standards on how to handle

128

NULLs seem ambiguous.

129

It is not clear from the standards documents exactly how NULLs should

130

be handled in all circumstances.

131

</p>

132

133

<p>

134

So instead of going by the standards documents, various popular

135

SQL engines were tested to see how they handle NULLs. The idea

136

was to make SQLite work like all the other engines.

137

An SQL test script was developed and run by volunteers on various

138

SQL RDBMSes and the results of those tests were used to deduce

139

how each engine processed NULL values.

140

The original tests were run in May of 2002.

141

A copy of the test script is found at the end of this document.

142

</p>

143

144

<p>

145

SQLite was originally coded in such a way that the answer to

146

all questions in the chart below would be "Yes". But the

147

experiments run on other SQL engines showed that none of them

148

worked this way. So SQLite was modified to work the same as

149

Oracle, PostgreSQL, and DB2. This involved making NULLs

150

indistinct for the purposes of the SELECT DISTINCT statement and

151

for the UNION operator in a SELECT. NULLs are still distinct

152

in a UNIQUE column. This seems somewhat arbitrary, but the desire

153

to be compatible with other engines outweighed that objection.

154

</p>

155

156

<p>

157

It is possible to make SQLite treat NULLs as distinct for the

158

purposes of the SELECT DISTINCT and UNION. To do so, one should

159

change the value of the NULL_ALWAYS_DISTINCT #define in the

160

<tt>sqliteInt.h</tt> source file and recompile.

161

</p>

162

163

164

<p>

165

<i>Update 2003-07-13:</i>

166

Since this document was originally written some of the database engines

167

tested have been updated and users have been kind enough to send in

168

corrections to the chart below. The original data showed a wide variety

169

of behaviors, but over time the range of behaviors has converged toward

170

the PostgreSQL/Oracle model. The only significant difference

171

is that Informix and MS-SQL both treat NULLs as

172

indistinct in a UNIQUE column.

173

</p>

174

175

<p>

176

The fact that NULLs are distinct for UNIQUE columns but are indistinct for

177

SELECT DISTINCT and UNION continues to be puzzling. It seems that NULLs

178

should be either distinct everywhere or nowhere. And the SQL standards

179

documents suggest that NULLs should be distinct everywhere. Yet as of

180

this writing, no SQL engine tested treats NULLs as distinct in a SELECT

181

DISTINCT statement or in a UNION.

182

</p>

183

</blockquote>

184

185

186

<p>

187

The following table shows the results of the NULL handling experiments.

188

</p>

189

190

191

192

<th>SQLite</th>

193

<th>PostgreSQL</th>

194

<th>Oracle</th>

195

<th>Informix</th>

196

197

198

<th>OCELOT</th>

199

</tr>

200

201

<tr><td>Adding anything to null gives null</td>

202

203

204

205

206

207

208

209

</tr>

210

<tr><td>Multiplying null by zero gives null</td>

211

212

213

214

215

216

217

218

</tr>

219

<tr><td>nulls are distinct in a UNIQUE column</td>

220

221

222

223

224

225

226

227

</tr>

228

<tr><td>nulls are distinct in SELECT DISTINCT</td>

229

230

231

232

233

234

235

236

</tr>

237

<tr><td>nulls are distinct in a UNION</td>

238

239

240

241

242

243

244

245

</tr>

246

247

248

249

250

251

252

253

254

</tr>

255

256

257

258

259

260

261

262

263

</tr>

264

<tr><td>"not (null AND false)" is true</td>

265

266

267

268

269

270

271

272

</tr>

273

</table>

274

275

276

277

<th>MySQL<br>3.23.41</th>

278

<th>MySQL<br>4.0.16</th>

279

<th>Firebird</th>

280

<th>SQL<br>Anywhere</th>

281

<th>Borland<br>Interbase</th>

282

</tr>

283

284

<tr><td>Adding anything to null gives null</td>

285

286

287

288

289

290

</tr>

291

<tr><td>Multiplying null by zero gives null</td>

292

293

294

295

296

297

</tr>

298

<tr><td>nulls are distinct in a UNIQUE column</td>

299

300

301

302

303

304

</tr>

305

<tr><td>nulls are distinct in SELECT DISTINCT</td>

306

307

308

309

310

311

</tr>

312

<tr><td>nulls are distinct in a UNION</td>

313

314

315

316

317

318

</tr>

319

320

321

322

323

324

325

</tr>

326

327

328

329

330

331

332

</tr>

333

<tr><td>"not (null AND false)" is true</td>

334

335

336

337

338

339

</tr>

340

</table>

341

342

343

<tr>

344

<td valign="top" rowspan=5>Notes:  </td>

345

346

<td>Older versions of firebird omits all NULLs from SELECT DISTINCT

347

and from UNION.</td>

348

</tr>

349

350

<td>Test data unavailable.</td>

351

</tr>

352

353

<td>MySQL version 3.23.41 does not support UNION.</td>

354

</tr>

355

356

<td>DB2, SQL Anywhere, and Borland Interbase

357

do not allow NULLs in a UNIQUE column.</td>

358

</tr>

359

360

<td>Borland Interbase does not support CASE expressions.</td>

361

</tr>

362

</table>

363

364

365

366

<p>

367

The following script was used to gather information for the table

368

above.

369

</p>

370

371

<pre>

372

-- I have about decided that SQL's treatment of NULLs is capricious and cannot be

373

-- deduced by logic. It must be discovered by experiment. To that end, I have

374

-- prepared the following script to test how various SQL databases deal with NULL.

375

-- My aim is to use the information gather from this script to make SQLite as much

376

-- like other databases as possible.

377

378

-- If you could please run this script in your database engine and mail the results

379

-- to me at drh@hwaci.com, that will be a big help. Please be sure to identify the

380

-- database engine you use for this test. Thanks.

381

382

-- If you have to change anything to get this script to run with your database

383

-- engine, please send your revised script together with your results.

384

385

386

-- Create a test table with data

387

create table t1(a int, b int, c int);

388

insert into t1 values(1,0,0);

389

insert into t1 values(2,0,1);

390

insert into t1 values(3,1,0);

391

insert into t1 values(4,1,1);

392

insert into t1 values(5,null,0);

393

insert into t1 values(6,null,1);

394

insert into t1 values(7,null,null);

395

396

-- Check to see what CASE does with NULLs in its test expressions

397

select a, case when b<>0 then 1 else 0 end from t1;

398

select a+10, case when not b<>0 then 1 else 0 end from t1;

399

select a+20, case when b<>0 and c<>0 then 1 else 0 end from t1;

400

select a+30, case when not (b<>0 and c<>0) then 1 else 0 end from t1;

401

select a+40, case when b<>0 or c<>0 then 1 else 0 end from t1;

402

select a+50, case when not (b<>0 or c<>0) then 1 else 0 end from t1;

403

select a+60, case b when c then 1 else 0 end from t1;

404

select a+70, case c when b then 1 else 0 end from t1;

405

406

-- What happens when you multiple a NULL by zero?

407

select a+80, b*0 from t1;

408

select a+90, b*c from t1;

409

410

-- What happens to NULL for other operators?

411

select a+100, b+c from t1;

412

413

-- Test the treatment of aggregate operators

414

select count(*), count(b), sum(b), avg(b), min(b), max(b) from t1;

415

416

-- Check the behavior of NULLs in WHERE clauses

417

select a+110 from t1 where b<10;

418

select a+120 from t1 where not b>10;

419

select a+130 from t1 where b<10 OR c=1;

420

select a+140 from t1 where b<10 AND c=1;

421

select a+150 from t1 where not (b<10 AND c=1);

422

select a+160 from t1 where not (c=1 AND b<10);

423

424

-- Check the behavior of NULLs in a DISTINCT query

425

select distinct b from t1;

426

427

-- Check the behavior of NULLs in a UNION query

428

select b from t1 union select b from t1;

429

430

-- Create a new table with a unique column. Check to see if NULLs are considered

431

-- to be distinct.

432

create table t2(a int, b int unique);

433

insert into t2 values(1,1);

434

insert into t2 values(2,null);

435

insert into t2 values(3,null);

436

select * from t2;

437

438

drop table t1;

439

drop table t2;

440

</pre>

441