| 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 |
|