Debian Codegraph @

www/quirks.html

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