diff options
Diffstat (limited to '')
| -rw-r--r-- | www/quirks.html | 43 |
1 files changed, 27 insertions, 16 deletions
diff --git a/www/quirks.html b/www/quirks.html index 4ddc439..c42c7ca 100644 --- a/www/quirks.html +++ b/www/quirks.html @@ -135,7 +135,7 @@ Table Of Contents <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="#sqlite_does_not_do_full_unicode_case_folding_by_default">7. SQLite Does Not Do Full Unicode Case Folding By Default</a></div> <div class="fancy-toc1"><a href="#double_quoted_string_literals_are_accepted">8. Double-quoted String Literals Are Accepted</a></div> <div class="fancy-toc1"><a href="#keywords_can_often_be_used_as_identifiers">9. Keywords Can Often Be Used As Identifiers</a></div> <div class="fancy-toc1"><a href="#dubious_sql_is_allowed_without_any_error_or_warning">10. Dubious SQL Is Allowed Without Any Error Or Warning</a></div> @@ -250,8 +250,8 @@ 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. +then it will fail when moved to another database that is more judgmental +about data types. </p><p> <a href="flextypegood.html">Flexible typing is a feature</a> of SQLite, not a bug. Flexible typing @@ -336,19 +336,30 @@ be activated at compile-time using the </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. +A PRIMARY KEY in an SQLite table is usually just +a UNIQUE constraint. Due to an historical oversight, +the column values of a PRIMARY KEY are allowed to be NULL. This is a bug, but by the time the problem was discovered there where so many databases in circulation that depended on the bug that the decision was made to support the buggy behavior moving forward. +You can work around this problem by adding a NOT NULL constraint on +each column of the PRIMARY KEY. </p><p> +Exceptions: +</p><ul> +<li><p> The value of an <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> column must always be a -non-NULL integer. The PRIMARY KEY columns of a <a href="withoutrowid.html">WITHOUT ROWID</a> -table are also required to be non-NULL. +non-NULL integer because the INTEGER PRIMARY KEY is an alias for +the <a href="lang_createtable.html#rowid">ROWID</a>. If you try to insert a NULL into an INTEGER PRIMARY +KEY column, SQLite automatically convert the NULL into a unique integer. -</p><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 +</p></li><li><p> +The <a href="withoutrowid.html">WITHOUT ROWID</a> and <a href="stricttables.html">STRICT</a> features was added after this bug was +discovered, and so WITHOUT ROWID and STRICT tables work correctly: +They disallow NULLs in the PRIMARY KEY. +</p></li></ul> + +<h1 id="aggregate_queries_can_contain_non_aggregate_result_columns_that_are_not_in_the_group_by_clause"><span>6. </span>Aggregate Queries Can Contain Non-Aggregate Result Columns That Are Not In The GROUP BY Clause</h1> <p> @@ -390,7 +401,7 @@ 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> +<h1 id="sqlite_does_not_do_full_unicode_case_folding_by_default"><span>7. </span>SQLite Does Not Do Full Unicode Case Folding By Default</h1> <p> SQLite does not know about the upper-case/lower-case distinction @@ -398,13 +409,13 @@ 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 +<li><p> Though stable now, when SQLite was first designed, the rules for unicode case folding were still in flux. That means that the behavior might have changed with each new unicode release, disrupting applications and corrupting indexes in the process. -</li><li> The tables necessary to do full and proper unicode case folding are +</p></li><li><p> The tables necessary to do full and proper unicode case folding are larger than the whole SQLite library. -</li></ol> +</p></li></ol> <p> Full unicode case folding is supported in SQLite if it is compiled with the <a href="compile.html#enable_icu">-DSQLITE_ENABLE_ICU</a> option and linked against the @@ -558,7 +569,7 @@ In other words, a FROM clause like this: ... FROM a, b RIGHT JOIN c, d ... </p></blockquote> -<p>This FROM clause should be parsed as follows: +<p>Should be parsed as follows: </p><div class="imgcontainer"> <div style="max-width:153px;"><svg xmlns='http://www.w3.org/2000/svg' class="pikchr" viewBox="0 0 153.328 245.544"> @@ -634,5 +645,5 @@ the problem can be easily overcome using parentheses in the FROM clause: </p><blockquote><p> ... FROM a, (b RIGHT JOIN c), d ... </p></blockquote> -<p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/quirks.in?m=e04aef4f57">2024-05-22 18:42:01</a> UTC </small></i></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=4b8c32b617">2024-08-10 18:17:30</a> UTC </small></i></p> |