Documentation Source Text

Check-in [75e2505670]
Login

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

Overview
Comment:Clarifications to the datatype3.html document.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 75e25056703fc645ea7a9e5b8237be0a6b59653f
User & Date: drh 2009-12-24 16:02:34
Context
2009-12-24
21:37
Update and clarify the documentation on datatypes and on CAST expressions. check-in: e357f599e3 user: drh tags: trunk
16:02
Clarifications to the datatype3.html document. check-in: 75e2505670 user: drh tags: trunk
00:08
Tweaks to the autoinc.html page. Add a new evidence style: ANALYSIS-OF: check-in: 1247769d7f user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/datatype3.in.

13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
..
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
..
55
56
57
58
59
60
61

62
63
64
65
66

67
68
69
70
71
72
73
74
75
76
...
193
194
195
196
197
198
199
200
201
202
203
204



205
206
207
208
209
210
211
...
216
217
218
219
220
221
222



223
224
225
226
227
228
229
230
231
232
233

234
235
236
237
238
239







240
241
242
243












244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
The dynamic type system of SQLite is backwards
compatible with the more common static type systems of other database engines
in the sense that SQL statement that work on statically typed databases should
would the same way in SQLite.  However, the dynamic typing in SQLite allowed
it to do things which are not possible in traditional rigidly typed
databases.</p>

<h2>1.0 Storage Classes</h2>

<p>Each value stored in an SQLite database (or manipulated by the
database engine) has one of the following storage classes:</p>
<ul>
  <li><p><B>NULL</B>.
  The value is a NULL value.</p>

................................................................................

  <li><p><B>BLOB</B>. The value is a blob of data, stored exactly as
  it was input.</p>
</ul>

<p>Note that a storage class is slightly more general than a datatype.
The INTEGER storage class, for example, includes 6 different integer
datatypes of different lengths.  This makes a different on-disk.  But
as soon as INTEGER values are read off of disk and into memory for processing,
they are converted to the most general datatype (8-byte signed integer).
And so for the most part, "storage class" is indistinguishable from 
"datatype" and the two terms can be used interchangably.</p>

<p>^Any column in an SQLite version 3 database,
except an [INTEGER PRIMARY KEY] column, may be used to store a value 
................................................................................
[prepared statements | pre-compiled SQL statements]
have an implicit storage class.
Under circumstances described below, the
database engine may convert values between numeric storage classes
(INTEGER and REAL) and TEXT during query execution. 
</p>


<h3>1.1 Boolean Datatype</h3>

<p>SQLite does not have a separate Boolean storage class.
^Instead, Boolean values are stored as integers 0 (false) and 1 (true).</p>


<h3>1.2 Date and Time Datatype</h3>

<p>SQLite does not have any storage class set aside for storing
dates and/or times.
^(Instead, the built-in [Date And Time Functions] of SQLite are capable of 
storing dates and times as TEXT, REAL, or INTEGER values:)^</p>

<ul>
<li><b>TEXT</b> as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
<li><b>REAL</b> as Julian day numbers, the number of days since
................................................................................

-- Storage classes for the following row:
-- TEXT, REAL, INTEGER, REAL
INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0);
</PRE>
</blockquote>)^

<a name="comparisons"></a>
<h2>3.0 Comparison Expressions</h2>

<p>SQLite version 3 has the usual set of SQL comparison operators
including "=", "&lt;", "&lt;=", "&gt;=", "!=", "IN", "BETWEEN", and "IS".



<p>^(The results of a comparison depend on the storage classes of the
operands, according to the following rules:)^</p>
<ul>
  <li><p>^A value with storage class NULL is considered less than any
  other value (including another value with storage class NULL).</p>

  <li><p>^An INTEGER or REAL value is less than any TEXT or BLOB value.
................................................................................
  are compared an appropriate collating sequence is used to determine 
  the result.  </p>

  <li><p>^When two BLOB values are compared, the result is
  determined using memcmp().</p>
</ul>




<p>^SQLite may attempt to convert values between the numeric storage
classes (INTEGER and REAL) and TEXT before performing a comparison.
^Whether or not any conversions are attempted before the comparison takes
place depends on the affinity of the operands.
^(Affinity is assigned to expression operands by the the following rules:)^

<ul>
  <li><p>^An expression that is a simple reference to a column value
  has the same affinity as the column.
  ^(Note that if X and Y.Z 
  are column names, then +X and +Y.Z are considered expressions.)^</p>


  <li><p>^(An expression of the form "CAST(&lt;expr&gt; TO &lt;type&gt;)"
  is assigned an affinity as if it were a reference to a column declared
  with type &lt;type&gt;)^
</ul>








<p>^In a binary comparison, if either operand has INTEGER or REAL
or NUMERIC affinity and the other operand does not, then NUMERIC
affinity is applied to the other operand before the comparison takes
place.












^(The expression "a BETWEEN b AND c" is treated as two separate
binary comparisons "a &gt;= b AND a &lt;= c", even if that means
different affinities are applied to 'a' in each of the comparisons.)^
^(Expressions of the form "a IN (SELECT b ....)" are handled is if
the expression where really "a=b".)^
^For example if "b" is a column value
and "a" is an expression, then the affinity of "b" is applied to "a"
before any comparisons take place.
^(The expression "a IN (x, y, z)" as equivalent to "a = +x OR
a = +y OR a = +z".)^  ^The values to the right of the IN operator (the "x", "y",
and "z" values in this example) are considered to be expressions, even if they
happen to be column values.  ^If the value to the left of the IN operator is
a column, then the affinity of that column is used.  ^If the left operand
of IN is an expression then no storage class conversions occur.
</p>

<h3>3.1 Comparison Example</h3>

^(<blockquote>
<PRE>
CREATE TABLE t1(
    a TEXT,
    b NUMERIC,
    c BLOB







|







 







|







 







>





>


|







 







|




>
>
>







 







>
>
>
|
|








|
>






>
>
>
>
>
>
>
|
|
|
<
>
>
>
>
>
>
>
>
>
>
>
>
|




|










|







13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
..
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
..
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
...
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
...
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258

259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
The dynamic type system of SQLite is backwards
compatible with the more common static type systems of other database engines
in the sense that SQL statement that work on statically typed databases should
would the same way in SQLite.  However, the dynamic typing in SQLite allowed
it to do things which are not possible in traditional rigidly typed
databases.</p>

<h2>1.0 Storage Classes and Datatypes</h2>

<p>Each value stored in an SQLite database (or manipulated by the
database engine) has one of the following storage classes:</p>
<ul>
  <li><p><B>NULL</B>.
  The value is a NULL value.</p>

................................................................................

  <li><p><B>BLOB</B>. The value is a blob of data, stored exactly as
  it was input.</p>
</ul>

<p>Note that a storage class is slightly more general than a datatype.
The INTEGER storage class, for example, includes 6 different integer
datatypes of different lengths.  This makes a difference on-disk.  But
as soon as INTEGER values are read off of disk and into memory for processing,
they are converted to the most general datatype (8-byte signed integer).
And so for the most part, "storage class" is indistinguishable from 
"datatype" and the two terms can be used interchangably.</p>

<p>^Any column in an SQLite version 3 database,
except an [INTEGER PRIMARY KEY] column, may be used to store a value 
................................................................................
[prepared statements | pre-compiled SQL statements]
have an implicit storage class.
Under circumstances described below, the
database engine may convert values between numeric storage classes
(INTEGER and REAL) and TEXT during query execution. 
</p>

<tcl>hd_fragment boolean {boolean datatype}</tcl>
<h3>1.1 Boolean Datatype</h3>

<p>SQLite does not have a separate Boolean storage class.
^Instead, Boolean values are stored as integers 0 (false) and 1 (true).</p>

<tcl>hd_fragment datetime {date and time datatype}</tcl>
<h3>1.2 Date and Time Datatype</h3>

<p>SQLite does not have a storage class set aside for storing
dates and/or times.
^(Instead, the built-in [Date And Time Functions] of SQLite are capable of 
storing dates and times as TEXT, REAL, or INTEGER values:)^</p>

<ul>
<li><b>TEXT</b> as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
<li><b>REAL</b> as Julian day numbers, the number of days since
................................................................................

-- Storage classes for the following row:
-- TEXT, REAL, INTEGER, REAL
INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0);
</PRE>
</blockquote>)^

<tcl>hd_fragment comparisons {comparison expressions}</tcl>
<h2>3.0 Comparison Expressions</h2>

<p>SQLite version 3 has the usual set of SQL comparison operators
including "=", "&lt;", "&lt;=", "&gt;=", "!=", "IN", "BETWEEN", and "IS".

<h3>3.1 Sort Order</h3>

<p>^(The results of a comparison depend on the storage classes of the
operands, according to the following rules:)^</p>
<ul>
  <li><p>^A value with storage class NULL is considered less than any
  other value (including another value with storage class NULL).</p>

  <li><p>^An INTEGER or REAL value is less than any TEXT or BLOB value.
................................................................................
  are compared an appropriate collating sequence is used to determine 
  the result.  </p>

  <li><p>^When two BLOB values are compared, the result is
  determined using memcmp().</p>
</ul>

<tcl>hd_fragment expraff {expression affinity}</tcl>
<h3>3.2 Affinity Of Comparison Operands</h3>

<p>^SQLite may attempt to convert values between the storage classes
INTEGER, REAL, and/or TEXT before performing a comparison.
^Whether or not any conversions are attempted before the comparison takes
place depends on the affinity of the operands.
^(Affinity is assigned to expression operands by the the following rules:)^

<ul>
  <li><p>^An expression that is a simple reference to a column value
  has the same affinity as the column.
  ^(Note that if X and Y.Z 
  are column names, then +X and +Y.Z are considered expressions for the
  purpose of determining affinity.)^</p>

  <li><p>^(An expression of the form "CAST(&lt;expr&gt; TO &lt;type&gt;)"
  is assigned an affinity as if it were a reference to a column declared
  with type &lt;type&gt;)^
</ul>

<tcl>hd_fragment compaff {comparison affinity rules}</tcl>
<h3>3.3 Type Conversions Prior To Comparison</h3>

<p>Storage class conversions of operands prior to a binary comparison
operation occur according to the following rules in the order shown:</p>

<ul>
<li><p>^If either operand has INTEGER or REAL or NUMERIC affinity and the
other operand does not, then NUMERIC
affinity is applied to the other operand.


<li><p>^If either operand has TEXT affinity and the other does not
then TEXT affinity is applied to the other operand.

<li><p>^Otherwise, no affinity is applied and both operands are compared
as is.</p>
</ul>

<p>In the comparison affinity rules above, to "apply affinity" means to
perform the storage class conversion if and only if the conversion is
reversible and lossless.</p>

<p>^(The expression "a BETWEEN b AND c" is treated as two separate
binary comparisons "a &gt;= b AND a &lt;= c", even if that means
different affinities are applied to 'a' in each of the comparisons.)^
^(Expressions of the form "a IN (SELECT b ....)" are handled is if
the expression where really "a=b".)^
^For example, if "b" is a column value
and "a" is an expression, then the affinity of "b" is applied to "a"
before any comparisons take place.
^(The expression "a IN (x, y, z)" as equivalent to "a = +x OR
a = +y OR a = +z".)^  ^The values to the right of the IN operator (the "x", "y",
and "z" values in this example) are considered to be expressions, even if they
happen to be column values.  ^If the value to the left of the IN operator is
a column, then the affinity of that column is used.  ^If the left operand
of IN is an expression then no storage class conversions occur.
</p>

<h3>3.4 Comparison Example</h3>

^(<blockquote>
<PRE>
CREATE TABLE t1(
    a TEXT,
    b NUMERIC,
    c BLOB