diff options
Diffstat (limited to 'www/quirks.html')
| -rw-r--r-- | www/quirks.html | 534 |
1 files changed, 534 insertions, 0 deletions
diff --git a/www/quirks.html b/www/quirks.html new file mode 100644 index 0000000..d46e7ca --- /dev/null +++ b/www/quirks.html @@ -0,0 +1,534 @@ +<!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="#does_not_do_full_unicode_case_folding_by_default">7. 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> +</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 uses a more rigid +and unforgiving type enforcement policy. + +</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> +Usually (the exceptions are <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> tables and +<a href="withoutrowid.html">WITHOUT ROWID</a> tables) a PRIMARY KEY in an SQLite table is really +the same as a UNIQUE constraint. Due to an historical oversight, +the column values of such 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. +</p><p> +The value of an <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> column must always be a +non-NULL integer. The PRIMARY KEY columns of a <a href="withoutrowid.html">WITHOUT ROWID</a> +table are also required to be non-NULL. + +</p><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="does_not_do_full_unicode_case_folding_by_default"><span>7. </span>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> 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. +</li><li> The tables necessary to do full and proper unicode case folding are + larger than the whole SQLite library. +</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="http://site.icu-project.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><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><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=d8c33568dd5491a25">2022-11-16 22:21:04</a> UTC </small></i></p> + |