Documentation Source Text

Check-in [596c4951a0]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix typos in the quirks document.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 596c4951a0bbc76a489cd58a2645f7134b289bed36f869494bf8e80450fb9655
User & Date: drh 2019-07-11 16:01:24.733
Context
2019-07-11
16:31
More typo fixes in quirks.html (check-in: f6681985cd user: drh tags: trunk)
16:01
Fix typos in the quirks document. (check-in: 596c4951a0 user: drh tags: trunk)
14:15
Add the "quirks.html" document to the list of overview documents in the documentation summary. (check-in: 085c470f26 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/quirks.in.
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
we can document the problem.

<h1>SQLite Is Embedded, Not Client-Server</h1>

<p>
When ever comparing SQLite to other SQL database engines like
SQL Server, PostgreSQL, MySQL, or Oracle, it is important first of all
to realize the SQLite is not intended as a replacement or competitor to
any of those systems.  SQLite is [serverless].  There is no separate
server process that manages the database.  An application interact
with the database engine using function calls, not be sending messages
to a separate process or thread.

<p>
The fact that SQLite is embedded and [serverless] instead of being







|







23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
we can document the problem.

<h1>SQLite Is Embedded, Not Client-Server</h1>

<p>
When ever 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 [serverless].  There is no separate
server process that manages the database.  An application interact
with the database engine using function calls, not be sending messages
to a separate process or thread.

<p>
The fact that SQLite is embedded and [serverless] instead of being
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
But to retain backwards compatibility, if there are columns named TRUE or
FALSE, then the keywords are treated as identifiers referencing those
columns, rather than BOOLEAN literals.

<h2>No Separate DATETIME Datatype</h2>

<p>
SQLite as no DATETIME datatype.
Instead, dates and times can be stored in any of these ways:

<ul>
<li> As a TEXT string in the ISO-8610 format.  Example: '2018-04-02 12:13:46'.
<li> As an INTEGER number of seconds since 1970 (also known as "unix time").
<li> As a REAL value that is the fractional 
     [https://en.wikipedia.org/wiki/Julian_day|Julian day number].







|







120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
But to retain backwards compatibility, if there are columns named TRUE or
FALSE, then the keywords are treated as identifiers referencing those
columns, rather than BOOLEAN literals.

<h2>No Separate DATETIME Datatype</h2>

<p>
SQLite has no DATETIME datatype.
Instead, dates and times can be stored in any of these ways:

<ul>
<li> As a TEXT string in the ISO-8610 format.  Example: '2018-04-02 12:13:46'.
<li> As an INTEGER number of seconds since 1970 (also known as "unix time").
<li> As a REAL value that is the fractional 
     [https://en.wikipedia.org/wiki/Julian_day|Julian day number].
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
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 more more rows in the input table(s).

<p>
SQLite does not impose 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:








|







173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
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>
SQLite does not impose 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:

204
205
206
207
208
209
210
211
212
213
214
215
216
217
218

<li><p>
If a query contains no aggregate functions at all, then a GROUP BY
clause can be added as a substitute of DISTINCT ON clause.  In other words,
output rows are filtered so that only one row is shows 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
the one of the rows is chosen arbitrarily.
</ol>

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







|







204
205
206
207
208
209
210
211
212
213
214
215
216
217
218

<li><p>
If a query contains no aggregate functions at all, then a GROUP BY
clause can be added as a substitute of DISTINCT ON clause.  In other words,
output rows are filtered so that only one row is shows 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.
</ol>

<h1>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
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
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>
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
bad habit of using double-quoted string literals when they
really need to learn to use the correct single-quoted string literal form.
<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







|







248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
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>
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>
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