1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
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
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
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
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>Quirks, Caveats, and Gotchas In SQLite</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<div class=fancy>
<div class=nosearch>
<div class="fancy_title">
Quirks, Caveats, and Gotchas In SQLite
</div>
<div class="fancy_toc">
<a onclick="toggle_toc()">
<span class="fancy_toc_mark" id="toc_mk">►</span>
Table Of Contents
</a>
<div id="toc_sub"><div class="fancy-toc1"><a href="#overview">1. Overview</a></div>
<div class="fancy-toc1"><a href="#sqlite_is_embedded_not_client_server">2. SQLite Is Embedded, Not Client-Server</a></div>
<div class="fancy-toc1"><a href="#flexible_typing">3. Flexible Typing</a></div>
<div class="fancy-toc2"><a href="#no_separate_boolean_datatype">3.1. No Separate BOOLEAN Datatype</a></div>
<div class="fancy-toc2"><a href="#no_separate_datetime_datatype">3.2. No Separate DATETIME Datatype</a></div>
<div class="fancy-toc2"><a href="#the_datatype_is_optional">3.3. The datatype is optional</a></div>
<div class="fancy-toc1"><a href="#foreign_key_enforcement_is_off_by_default">4. Foreign Key Enforcement Is Off By Default</a></div>
<div class="fancy-toc1"><a href="#primary_keys_can_sometimes_contain_nulls">5. PRIMARY KEYs Can Sometimes Contain NULLs</a></div>
<div class="fancy-toc1"><a href="#aggregate_queries_can_contain_non_aggregate_result_columns_that_are_not_in_the_group_by_clause">6. Aggregate Queries Can Contain Non-Aggregate Result Columns
That Are Not In The GROUP BY Clause</a></div>
<div class="fancy-toc1"><a href="#sqlite_does_not_do_full_unicode_case_folding_by_default">7. SQLite Does Not Do Full Unicode Case Folding By Default</a></div>
<div class="fancy-toc1"><a href="#double_quoted_string_literals_are_accepted">8. Double-quoted String Literals Are Accepted</a></div>
<div class="fancy-toc1"><a href="#keywords_can_often_be_used_as_identifiers">9. Keywords Can Often Be Used As Identifiers</a></div>
<div class="fancy-toc1"><a href="#dubious_sql_is_allowed_without_any_error_or_warning">10. Dubious SQL Is Allowed Without Any Error Or Warning</a></div>
<div class="fancy-toc1"><a href="#autoincrement_does_not_work_the_same_as_mysql">11. AUTOINCREMENT Does Not Work The Same As MySQL</a></div>
<div class="fancy-toc1"><a href="#nul_characters_are_allowed_in_text_strings">12. NUL Characters Are Allowed In Text Strings</a></div>
<div class="fancy-toc1"><a href="#sqlite_distinguishes_between_integer_and_text_literals">13. SQLite Distinguishes Between Integer And Text Literals</a></div>
<div class="fancy-toc1"><a href="#sqlite_gets_the_precedence_of_comma_joins_wrong">14. SQLite Gets The Precedence Of Comma-Joins Wrong</a></div>
</div>
</div>
<script>
function toggle_toc(){
var sub = document.getElementById("toc_sub")
var mk = document.getElementById("toc_mk")
if( sub.style.display!="block" ){
sub.style.display = "block";
mk.innerHTML = "▼";
} else {
sub.style.display = "none";
mk.innerHTML = "►";
}
}
</script>
</div>
<h1 id="overview"><span>1. </span>Overview</h1>
<p>
The SQL language is a "standard".
Even so, no two SQL database engines work exactly alike.
Every SQL implementation has it own peculiarities and oddities,
and SQLite is no exception to this rule.
</p><p>
This document strives to highlight the principal differences
between SQLite and other SQL implementations, as an aid to developers
that are porting to or from SQLite or who are trying to build a
system that works across multiple database engines.
</p><p>
If you are an SQLite user whose has stumbled over some quirk of
SQLite that is not mentioned here, please let the developers know
by posting a brief message on the
<a href="https://sqlite.org/forum/forum">SQLite Forum</a>.
</p><h1 id="sqlite_is_embedded_not_client_server"><span>2. </span>SQLite Is Embedded, Not Client-Server</h1>
<p>
Whenever comparing SQLite to other SQL database engines like
SQL Server, PostgreSQL, MySQL, or Oracle, it is important first of all
to realize that SQLite is not intended as a replacement or competitor to
any of those systems. SQLite is <a href="serverless.html">serverless</a>. There is no separate
server process that manages the database. An application interacts
with the database engine using function calls, not by sending messages
to a separate process or thread.
</p><p>
The fact that SQLite is embedded and <a href="serverless.html">serverless</a> instead of being
client/server is a feature, not a bug.
</p><p>
Client/server databases like MySQL, PostgreSQL, SQL Server, Oracle, and
others are an important component of modern systems.
These systems solve an important problem.
But SQLite solves a different problem.
Both SQLite and client/server databases have their role.
Developers who are comparing SQLite against other SQL database engines need
to clearly understand this distinction.
</p><p>
See the <a href="whentouse.html">Appropriate Uses For SQLite</a> document for additional information.
</p><h1 id="flexible_typing"><span>3. </span>Flexible Typing</h1>
<p>
SQLite is flexible with regard to datatypes. Datatypes are advisory
rather than mandatory.
</p><p>
Some commentators say that SQLite is "weakly typed" and that other
SQL databases are "strongly typed". We consider these terms to be
inaccurate and even pejorative. We prefer to say that SQLite is
"flexibly typed" and that other SQL database engines are
"rigidly typed".
</p><p>
See the <a href="datatype3.html">Datatypes in SQLite</a> document for a detailed
discussion of the type system in SQLite.
</p><p>
The key point is that SQLite is very forgiving of the type of data that
you put into the database. For example, if a column has a datatype of
"INTEGER" and the application inserts a text string into that column,
SQLite will first try to convert the text string into an integer, just like
every other SQL database engine. Thus, if one inserts <b>'1234'</b> into
an INTEGER column, that value is converted into an integer 1234 and stored.
But, if you insert a non-numeric string like <b>'wxyz'</b> into an INTEGER
column, unlike other SQL databases, SQLite does not throw an error. Instead,
SQLite stores the actual string value in the column.
</p><p>
Similarly, SQLite allows you to store a 2000-character string into a
column of type VARCHAR(50). Other SQL implementations would either throw
an error or truncate the string. SQLite stores the entire 2000-character
string with no loss of information and without complaint.
</p><p>
Where this ends up causing problems is when developers do some initial
coding work using SQLite and get their application working, but then try
to convert to another database like PostgreSQL or SQL Server for deployment.
If the application is initially taking advantage of SQLite's flexible typing,
then it will fail when moved to another database that is more judgmental
about data types.
</p><p>
<a href="flextypegood.html">Flexible typing is a feature</a> of SQLite, not a bug. Flexible typing
is about freedom.
Nevertheless, we recognize that this feature does sometimes cause
confusion for developers who are accustomed to working with
other databases that are more strict with regard to data type rules.
In retrospect, perhaps it would have been less confusing if SQLite had merely
implemented an ANY datatype so that developers could explicitly state
when they wanted to use flexible typing, rather than making flexible
typing the default.
As an accommodation for those who expect rigid typing, SQLite version 3.37.0
(2021-11-27) introduced the option of <a href="stricttables.html">STRICT tables</a>.
These either impose
the mandatory datatype constraints found in other SQL database engines,
or allow the explicit ANY datatype to retain SQLite's flexible typing.
</p><h2 id="no_separate_boolean_datatype"><span>3.1. </span>No Separate BOOLEAN Datatype</h2>
<p>
Unlike most other SQL implementations,
SQLite does not have a separate BOOLEAN data type.
Instead, TRUE and FALSE are (normally) represented as integers 1 and 0,
respectively.
This does not seem to cause many problems, as we seldom get complaints
about it. But it is important to recognize.
</p><p>
Beginning with SQLite <a href="releaselog/3_23_0.html">version 3.23.0</a> (2018-04-02), SQLite also
recognizes TRUE and FALSE keywords as aliases for integer values 1 and 0,
respectively.
This provides better compatibility with other SQL implementations.
But for backwards compatibility, if there are columns named TRUE or
FALSE, then the keywords are treated as identifiers referencing those
columns, rather than BOOLEAN literals.
</p><h2 id="no_separate_datetime_datatype"><span>3.2. </span>No Separate DATETIME Datatype</h2>
<p>
SQLite has no DATETIME datatype.
Instead, dates and times can be stored in any of these ways:
</p><ul>
<li> As a TEXT string in the ISO-8601 format. Example: '2018-04-02 12:13:46'.
</li><li> As an INTEGER number of seconds since 1970 (also known as "unix time").
</li><li> As a REAL value that is the fractional
<a href="https://en.wikipedia.org/wiki/Julian_day">Julian day number</a>.
</li></ul>
<p>
The built-in <a href="lang_datefunc.html">date and time functions</a> of SQLite understand date/times in
all of the formats above, and can freely change between them.
Which format you use, is entirely up to your application.
</p><h2 id="the_datatype_is_optional"><span>3.3. </span>The datatype is optional</h2>
<p>
Because SQLite is flexible and forgiving with regard to datatypes,
table columns can be created that have no specified datatype at all.
For example:
</p><div class="codeblock"><pre>CREATE TABLE t1(a,b,c,d);
</pre></div>
<p>The table "t1" has four columns "a", "b", "c", and "d" that have
no particular datatype assigned. You can store anything you want in
any of those columns.</p>
<h1 id="foreign_key_enforcement_is_off_by_default"><span>4. </span>Foreign Key Enforcement Is Off By Default</h1>
<p>SQLite has parsed foreign key constraints for time out of mind,
but added the ability to actually enforce those constraints much later,
with <a href="releaselog/3_6_19.html">version 3.6.19</a> (2009-10-14). By the time foreign key constraint
enforcement was added, there were already countless millions of databases
in circulation that contained foreign key constraints, some of which
were not correct. To avoid breaking those legacy databases, foreign key
constraint enforcement is turned off by default in SQLite.
</p><p>Applications can activate foreign key enforcement at run-time using
the <a href="pragma.html#pragma_foreign_keys">PRAGMA foreign_keys</a> statement. Or, foreign key enforcement can
be activated at compile-time using the
<a href="compile.html#default_foreign_keys">-DSQLITE_DEFAULT_FOREIGN_KEYS=1</a> compile-time option.
</p><h1 id="primary_keys_can_sometimes_contain_nulls"><span>5. </span>PRIMARY KEYs Can Sometimes Contain NULLs</h1>
<p>
A PRIMARY KEY in an SQLite table is usually just
a UNIQUE constraint. Due to an historical oversight,
the column values of a PRIMARY KEY are allowed to be NULL.
This is a bug, but by the time the problem was discovered there
where so many databases in circulation that depended on the bug that
the decision was made to support the buggy behavior moving forward.
You can work around this problem by adding a NOT NULL constraint on
each column of the PRIMARY KEY.
</p><p>
Exceptions:
</p><ul>
<li><p>
The value of an <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> column must always be a
non-NULL integer because the INTEGER PRIMARY KEY is an alias for
the <a href="lang_createtable.html#rowid">ROWID</a>. If you try to insert a NULL into an INTEGER PRIMARY
KEY column, SQLite automatically convert the NULL into a unique integer.
</p></li><li><p>
The <a href="withoutrowid.html">WITHOUT ROWID</a> and <a href="stricttables.html">STRICT</a> features was added after this bug was
discovered, and so WITHOUT ROWID and STRICT tables work correctly:
They disallow NULLs in the PRIMARY KEY.
</p></li></ul>
<h1 id="aggregate_queries_can_contain_non_aggregate_result_columns_that_are_not_in_the_group_by_clause"><span>6. </span>Aggregate Queries Can Contain Non-Aggregate Result Columns
That Are Not In The GROUP BY Clause</h1>
<p>
In most SQL implementations, output columns of an aggregate query
may only reference aggregate functions or columns named in the
GROUP BY clause. It does not make good sense to reference an ordinary
column in an aggregate query because each output row might be composed
from two or more rows in the input table(s).
</p><p>
SQLite does not enforce this restriction.
The output columns from an aggregate query can be arbitrary expressions
that include columns not found in GROUP BY clause.
This feature has two uses:
</p><ol>
<li><p>
With SQLite (but not any other SQL implementation that we know of) if
an aggregate query contains a single min() or max() function, then the
values of columns used in the output are taken from the row where
the min() or max() value was achieved. If two or more rows have the
same min() or max() value, then the columns values will be chosen arbitrarily
from one of those rows.
</p><p>
For example to find the highest paid employee:
</p><div class="codeblock"><pre>SELECT max(salary), first_name, last_name FROM employee;
</pre></div>
<p>
In the query above, the values for the first_name and last_name columns
will correspond to the row that satisfied the max(salary) condition.
</p></li><li><p>
If a query contains no aggregate functions at all, then a GROUP BY
clause can be added as a substitute for the DISTINCT ON clause. In other words,
output rows are filtered so that only one row is shown for each distinct
set of values in the GROUP BY clause. If two or more output rows would
have otherwise had the same set of values for the GROUP BY columns, then
one of the rows is chosen arbitrarily. (SQLite supports DISTINCT but not
DISTINCT ON, whose functionality is provided instead by GROUP BY.)
</p></li></ol>
<h1 id="sqlite_does_not_do_full_unicode_case_folding_by_default"><span>7. </span>SQLite Does Not Do Full Unicode Case Folding By Default</h1>
<p>
SQLite does not know about the upper-case/lower-case distinction
for all unicode characters. SQL functions like
upper() and lower() only work on ASCII characters. There are two
reasons for this:
</p><ol>
<li><p> Though stable now, when SQLite was first designed, the rules for
unicode case folding were still in flux. That means that the
behavior might have changed with each new unicode release, disrupting
applications and corrupting indexes in the process.
</p></li><li><p> The tables necessary to do full and proper unicode case folding are
larger than the whole SQLite library.
</p></li></ol>
<p>
Full unicode case folding is supported in SQLite if it is compiled
with the <a href="compile.html#enable_icu">-DSQLITE_ENABLE_ICU</a> option and linked against the
<a href="https://icu.unicode.org">International Components for Unicode</a>
library.
<a name="dblquote"></a>
</p><h1 id="double_quoted_string_literals_are_accepted"><span>8. </span>Double-quoted String Literals Are Accepted</h1>
<p>
The SQL standard requires double-quotes around identifiers
and single-quotes around string literals. For example:
</p><ul>
<li> <tt>"this is a legal SQL column name"</tt>
</li><li> <tt>'this is an SQL string literal'</tt>
</li></ul>
<p>
SQLite accepts both of the above. But, in an effort to be compatible
with MySQL 3.x (which was one of the most widely used RDBMSes
when SQLite was first being designed) SQLite will also interpret
a double-quotes string as
string literal if it does not match any valid identifier.
</p><p>
This misfeature means that a misspelled double-quoted
identifier will be interpreted as a string literal, rather than generating
an error.
It also lures developers who are new to the SQL language into the
bad habit of using double-quoted string literals when they
really need to learn to use the correct single-quoted string literal form.
</p><p>
In hindsight, we should not have tried to make SQLite accept MySQL 3.x
syntax, and should have never allowed double-quoted string literals.
However, there are countless applications that make use of
double-quoted string literals and so we continue to support
that capability to avoid breaking legacy.
</p><p>
As of SQLite 3.27.0 (2019-02-07) the use of a double-quoted
string literal causes a warning message to be sent to the <a href="errlog.html">error log</a>.
</p><p>
As of SQLite 3.29.0 (2019-07-10) the use of double-quoted
string literals can be disabled at run-time using the
<a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigdqsddl">SQLITE_DBCONFIG_DQS_DDL</a> and <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigdqsdml">SQLITE_DBCONFIG_DQS_DML</a> actions
to <a href="c3ref/db_config.html">sqlite3_db_config()</a>. The default settings can be altered
at compile-time using the <a href="compile.html#dqs">-DSQLITE_DQS=<i>N</i></a> compile-time
option. Application developers are encouraged to compile using
-DSQLITE_DQS=0 in order to disable the double-quoted string literal
misfeature by default. If that is not possible, then disable
double-quoted string literals for individual database connections
using C-code like this:
</p><blockquote><pre>
sqlite3_db_config(db, SQLITE_DBCONFIG_DQS_DDL, 0, (void*)0);
sqlite3_db_config(db, SQLITE_DBCONFIG_DQS_DML, 0, (void*)0);
</pre></blockquote>
<p>Or, if double-quoted string literals are disabled by default, but need
to be selectively enabled for some historical database connections,
that can be done using the same C-code as shown above except with the
third parameter changed from 0 to 1.
</p><p>
As of SQLite 3.41.0 (2023-02-21) SQLITE_DBCONFIG_DQS_DDL and
SQLITE_DBCONFIG_DQS_DML are disabled by default in the <a href="cli.html">CLI</a>. Use
the ".dbconfig" dot-command to reenable the legacy behavior if
desired.
</p><h1 id="keywords_can_often_be_used_as_identifiers"><span>9. </span>Keywords Can Often Be Used As Identifiers</h1>
<p>
The SQL language is rich in keywords.
Most SQL implementations do not allow keywords to be used as identifiers
(names of tables or columns) unless they are enclosed in double-quotes.
But SQLite is more flexible. Many keywords can be used as identifiers without
needing to be quoted, as long as those keywords are used in a context where
it is clear that they are intended to be an identifier.
</p><p>
For example, the following statement is valid in SQLite:
</p><div class="codeblock"><pre>CREATE TABLE union(true INT, with BOOLEAN);
</pre></div>
<p>
The same SQL statement will fail on every other SQL implementation that
we know of due to the use of keywords "union", "true", and "with" as
identifiers.
</p><p>
The ability to use keywords as identifiers promotes backwards compatibility.
As new keywords are added, legacy schemas that just happen to use those
keywords as table or column names continue to work. However, the ability
to use a keyword as an identifier sometimes leads to surprising outcomes.
For example:
</p><div class="codeblock"><pre>CREATE TRIGGER AFTER INSERT ON tableX BEGIN
INSERT INTO tableY(b) VALUES(new.a);
END;
</pre></div>
<p>The trigger created by the previous statement is named "AFTER"
and it is a "BEFORE" trigger. The "AFTER" token is used as an identifier
instead of as a keyword, as that is the only way to parse the statement.
Another example:
</p><div class="codeblock"><pre>CREATE TABLE tableZ(INTEGER PRIMARY KEY);
</pre></div>
<p>The tableZ table has a single column named "INTEGER". That column
has no datatype specified, but it is the PRIMARY KEY.
The column is <em>not</em> the <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> for the table because
it has no datatype. The "INTEGER" token is used as an identifier for the
column name, not as a datatype keyword.</p>
<h1 id="dubious_sql_is_allowed_without_any_error_or_warning"><span>10. </span>Dubious SQL Is Allowed Without Any Error Or Warning</h1>
<p>
The original implementation of SQLite sought to follow
<a href="https://en.wikipedia.org/wiki/Robustness_principle">Postel's Law</a> which
states in part "Be liberal in what you accept".
This used to be considered good design - that a system would accept
dodgy inputs and try to do the best it could without complaining too much.
But lately, people have come to realize that it is sometimes better to
be strict in what you accept, so as to more easily find errors in the
input.
</p><p>
</p><h1 id="autoincrement_does_not_work_the_same_as_mysql"><span>11. </span>AUTOINCREMENT Does Not Work The Same As MySQL</h1>
<p>The <a href="autoinc.html">AUTOINCREMENT</a> feature in SQLite works differently than
it does in MySQL. This often causes confusion for people who
initially learned SQL on MySQL and then start using SQLite, and
expect the two systems to work identically.
</p><p>See the <a href="autoinc.html">SQLite AUTOINCREMENT documentation</a> for
detailed instructions on what AUTOINCREMENT does and does not do
in SQLite.
</p><h1 id="nul_characters_are_allowed_in_text_strings"><span>12. </span>NUL Characters Are Allowed In Text Strings</h1>
<p>NUL characters (ASCII code 0x00 and Unicode \u0000) may appear in
the middle of strings in SQLite. This can lead to unexpected behavior.
See the "<a href="nulinstr.html">NUL characters in strings</a>" document for further information.
</p><h1 id="sqlite_distinguishes_between_integer_and_text_literals"><span>13. </span>SQLite Distinguishes Between Integer And Text Literals</h1>
<p>SQLite says that the following query returns false:
</p><div class="codeblock"><pre>SELECT 1='1';
</pre></div>
<p>It does this because an integer is not a string.
Every other major SQL database engine says this is true, for reasons
that the creator of SQLite does not understand.
</p><h1 id="sqlite_gets_the_precedence_of_comma_joins_wrong"><span>14. </span>SQLite Gets The Precedence Of Comma-Joins Wrong</h1>
<p>SQLite gives all join operators equal precedence and processes them
from left to right. But this is not quite correct. It should be that
comma-joins have lower precedence than all others join operators.
In other words, a FROM clause like this:
</p><blockquote><p>
... FROM a, b RIGHT JOIN c, d ...
</p></blockquote>
<p>Should be parsed as follows:
</p><div class="imgcontainer">
<div style="max-width:153px;"><svg xmlns='http://www.w3.org/2000/svg' class="pikchr" viewBox="0 0 153.328 245.544">
<path d="M67,32L120,32L120,2L67,2Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="93" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">JOIN</text>
<polygon points="53,72 58,61 65,67" style="fill:rgb(0,0,0)"/>
<path d="M93,32L57,68" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="134,72 122,67 128,61" style="fill:rgb(0,0,0)"/>
<path d="M93,32L129,68" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M27,102L80,102L80,72L27,72Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="53" y="87" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">JOIN</text>
<path d="M121,102L146,102L146,72L121,72Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="134" y="87" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">D</text>
<polygon points="13,142 18,131 24,137" style="fill:rgb(0,0,0)"/>
<path d="M53,102L17,138" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="93,142 82,137 88,131" style="fill:rgb(0,0,0)"/>
<path d="M53,102L89,138" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M36,173L151,173L151,142L36,142Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="93" y="157" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">RIGHT JOIN</text>
<path d="M2,173L25,173L25,142L2,142Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="13" y="157" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">A</text>
<polygon points="53,213 58,201 65,208" style="fill:rgb(0,0,0)"/>
<path d="M93,173L57,209" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="134,213 122,208 128,201" style="fill:rgb(0,0,0)"/>
<path d="M93,173L129,209" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M42,243L65,243L65,213L42,213Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="53" y="228" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">B</text>
<path d="M121,243L146,243L146,213L121,213Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="134" y="228" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">C</text>
</svg>
</div>
</div>
<p>But SQLite instead parses the FROM clause like this:
</p><div class="imgcontainer">
<div style="max-width:188px;"><svg xmlns='http://www.w3.org/2000/svg' class="pikchr" viewBox="0 0 188.691 245.544">
<path d="M107,32L160,32L160,2L107,2Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="134" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">JOIN</text>
<polygon points="93,72 99,61 105,67" style="fill:rgb(0,0,0)"/>
<path d="M134,32L98,68" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="174,72 162,67 169,61" style="fill:rgb(0,0,0)"/>
<path d="M134,32L170,68" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M36,102L151,102L151,72L36,72Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="93" y="87" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">RIGHT JOIN</text>
<path d="M161,102L186,102L186,72L161,72Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="174" y="87" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">D</text>
<polygon points="53,142 58,131 65,137" style="fill:rgb(0,0,0)"/>
<path d="M93,102L57,138" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="134,142 122,137 128,131" style="fill:rgb(0,0,0)"/>
<path d="M93,102L129,138" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M27,173L80,173L80,142L27,142Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="53" y="157" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">JOIN</text>
<path d="M121,173L146,173L146,142L121,142Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="134" y="157" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">C</text>
<polygon points="13,213 18,201 24,208" style="fill:rgb(0,0,0)"/>
<path d="M53,173L17,209" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="93,213 82,208 88,201" style="fill:rgb(0,0,0)"/>
<path d="M53,173L89,209" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M2,243L25,243L25,213L2,213Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="13" y="228" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">A</text>
<path d="M82,243L105,243L105,213L82,213Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="93" y="228" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">B</text>
</svg>
</div>
</div>
<p>The problem can only makes a difference in the result when using
RIGHT OUTER JOIN or FULL OUTER JOIN in the same FROM clause with
comma-joins, which rarely happens in practice. And
the problem can be easily overcome using parentheses in the FROM clause:
</p><blockquote><p>
... FROM a, (b RIGHT JOIN c), d ...
</p></blockquote>
<p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/quirks.in?m=4b8c32b617">2024-08-10 18:17:30</a> UTC </small></i></p>
|
