Documentation Source Text

Check-in [513684f098]
Login

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

Overview
Comment:Clarification of affinity, especially in regard to views and subqueries.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 513684f098df805ff0fc67fe981230bdc3459929
User & Date: drh 2017-01-18 02:41:55
Context
2017-01-28
14:45
Updates to the performance graph spreadsheet. check-in: 63d1835d75 user: drh tags: trunk
2017-01-18
02:41
Clarification of affinity, especially in regard to views and subqueries. check-in: 513684f098 user: drh tags: trunk
2017-01-12
20:25
Proposed enhancement to the definition of column affinity so that it covers columns belonging to subqueries and VIEWs. check-in: 524ba450fb user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/datatype3.in.

88
89
90
91
92
93
94
95














96


97
98
99
100
101
102
103
104
...
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
...
264
265
266
267
268
269
270





































































271
272
273
274
275
276
277
...
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
formats and freely convert between formats using the built-in
[date and time functions].</p>


<tcl>hd_fragment affinity affinity {column affinity} {type affinity} {*affinities}</tcl>
<h1>Type Affinity</h1>

<p>














In order to maximize compatibility between SQLite and other database


engines, SQLite supports the concept of "type affinity" on columns.
The type affinity of a column is the recommended type for data stored
in that column.  The important idea here is that the type is recommended, not
required.  Any column can still store any type of data.
It is just that some columns, given the choice, will prefer to use
one storage class over another.  The preferred storage class for
a column is called its "affinity".
</p>
................................................................................
</ol>

<p>^Note that the order of the rules for determining column affinity
is important.  ^A column whose declared type is "CHARINT" will match
both rules 1 and 2 but the first rule takes precedence and so the 
column affinity will be INTEGER.</p>

<h3>Determination Of Column Affinity For Views And Subqueries</h3>

<p>^The affinity for a column of a view or subquery is the affinity
   of the corresponding result set entry for the [SELECT] statement
   that implements the view or subquery.
^When the [SELECT] statement that implements a view or subqueries
is a [compound SELECT] then the affinity is determined by the
left-most query in the compound.

<h2>Affinity Name Examples</h2>

<p>The following table shows how many common datatype names from
more traditional SQL implementations are converted into affinities by the five rules of the
previous section.  This table shows only a small subset of the
datatype names that SQLite will accept.  Note that ^(numeric arguments
in parentheses that following the type name (ex: "VARCHAR(255)") are
ignored)^ by SQLite - SQLite does not impose any length restrictions
................................................................................
<td align="center">5
</table>)^
</blockquote>

<p>^Note that a declared type of "FLOATING POINT" would give INTEGER
affinity, not REAL affinity, due to the "INT" at the end of "POINT".
^And the declared type of "STRING" has an affinity of NUMERIC, not TEXT.






































































<h2>Column Affinity Behavior Example</h2>

<p>The following SQL demonstrates how SQLite uses column affinity
to do type conversions when values are inserted into a table.</p>

^(<blockquote>
................................................................................
  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>
<h2>Affinity Of Comparison Operands</h2>

<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 type affinity of the operands.

<p>Every table column has a type affinity (one of BLOB, TEXT, INTEGER,
REAL, or NUMERIC) but expressions do no necessarily have an affinity.

<p>Operand affinity is determined by the following rules:

<ul>
  <li><p>^The right-hand operand of an IN or NOT IN
  operator has no affinity if the operand is a list and has the same
  affinity as the affinity of the result set expression if the operand
  is a SELECT.

  <li><p>^An expression that is a simple reference to a column value
  has the same affinity as the column.
  <ul>
  <li><p>
  ^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>
  ^This rule applies regardless of whether the column belongs to a table,
  subquery, or [VIEW].
  </ul>

  <li><p>^(An expression of the form "CAST(<i>expr</i> AS <i>type</i>)"
  has an affinity that is the same as a column with a declared
  type of "<i>type</i>".)^

  <li><p>^Otherwise, an expression has no affinity.
</ul>

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

<p>To "apply affinity" means to convert an operand to a particular storage
class if and only if the conversion is lossless and reversible.
^(Affinity is applied to operands of a comparison operator prior to
the comparison according to the following rules in the order shown:)^</p>

<ul>
<li><p>^If one operand has INTEGER, REAL or NUMERIC affinity








>
>
>
>
>
>
>
>
>
>
>
>
>
>

>
>
|







 







<
<
<
<
<
<
<
<
<
|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|
|






<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
...
200
201
202
203
204
205
206









207
208
209
210
211
212
213
214
...
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
...
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428

































429
430
431
432
433
434
435
formats and freely convert between formats using the built-in
[date and time functions].</p>


<tcl>hd_fragment affinity affinity {column affinity} {type affinity} {*affinities}</tcl>
<h1>Type Affinity</h1>

<p>
SQL database engines that use rigid typing will usually try to
automatically convert values to the appropriate datatype.  Consider this:

<blockquote><pre>
CREATE TABLE t1(a INT, b VARCHAR(10));
INSERT INTO t1(a,b) VALUES('123',456);
</pre></blockquote>

<p>
Rigidly-typed database will convert the string '123' into an
integer 123 and the integer 456 into a string '456' prior to
doing the insert.

<p>
In order to maximize compatibility between SQLite and other database
engines, and so that the example above will work on SQLite as it does
on other SQL database engines,
SQLite supports the concept of "type affinity" on columns.
The type affinity of a column is the recommended type for data stored
in that column.  The important idea here is that the type is recommended, not
required.  Any column can still store any type of data.
It is just that some columns, given the choice, will prefer to use
one storage class over another.  The preferred storage class for
a column is called its "affinity".
</p>
................................................................................
</ol>

<p>^Note that the order of the rules for determining column affinity
is important.  ^A column whose declared type is "CHARINT" will match
both rules 1 and 2 but the first rule takes precedence and so the 
column affinity will be INTEGER.</p>










<h3>Affinity Name Examples</h3>

<p>The following table shows how many common datatype names from
more traditional SQL implementations are converted into affinities by the five rules of the
previous section.  This table shows only a small subset of the
datatype names that SQLite will accept.  Note that ^(numeric arguments
in parentheses that following the type name (ex: "VARCHAR(255)") are
ignored)^ by SQLite - SQLite does not impose any length restrictions
................................................................................
<td align="center">5
</table>)^
</blockquote>

<p>^Note that a declared type of "FLOATING POINT" would give INTEGER
affinity, not REAL affinity, due to the "INT" at the end of "POINT".
^And the declared type of "STRING" has an affinity of NUMERIC, not TEXT.

<tcl>hd_fragment expraff {expression affinity} {Affinity Of Expressions}</tcl>
<h2>Affinity Of Expressions</h2>

<p>Every table column has a type affinity (one of BLOB, TEXT, INTEGER,
REAL, or NUMERIC) but expressions do no necessarily have an affinity.

<p>Expression affinity is determined by the following rules:

<ul>
  <li><p>^The right-hand operand of an IN or NOT IN
  operator has no affinity if the operand is a list and has the same
  affinity as the affinity of the result set expression if the operand
  is a SELECT.

  <li><p>^When an expression is a simple reference to a column of a
  real table (not a [VIEW] or subquery) then the expression
  has the same affinity as the table column.
  <ul>
  <li><p>^(Parentheses around the column name are ignored.  Hence if
  X and Y.Z are column names, then (X) and (Y.Z) are also considered
  column names and have the affinity of the corresponding columns.)^
  <li><p>
  ^(Any operators applied to column names, including the no-op 
   unary "+" operator, convert the column name into an expression which
   always has no affinity.  Hence even if X and Y.Z are column names, the
   expressions +X and +Y.Z are not column names and have no affinity.)^
  </ul>

  <li><p>^(An expression of the form "CAST(<i>expr</i> AS <i>type</i>)"
  has an affinity that is the same as a column with a declared
  type of "<i>type</i>".)^

  <li><p>^Otherwise, an expression has no affinity.
</ul>

<tcl>hd_fragment affview {rules for determining column affinity in VIEWs}</tcl>
<h2>Column Affinity For Views And Subqueries</h2>

<p>The "columns" of a [VIEW] or FROM-clause subquery are really
the expressions
in the result set of the [SELECT] statement that implements the VIEW
or subquery.  Thus, the affinity for columns of a VIEW or subquery
are determined by the expression affinity rules above.
Consider an example:

<blockquote><pre>
CREATE TABLE t1(a INT, b TEXT, c REAL);
CREATE VIEW v1(x,y,z) AS SELECT b, a+c, 42 FROM t1 WHERE b!=11;
</pre></blockquote>

<p>The affinity of the v1.x column will be the same as the affinity
of t1.b (INTEGER), since v1.x maps directly into t1.b.  But
columns v1.y and v1.z both have no affinity, since those columns
map into expression a+c and 42, and expressions always have no
affinity.

<p>When the [SELECT] statement that implements a [VIEW] or 
FROM-clause subquery is a [compound SELECT] then the affinity of 
each supposed column of the VIEW or subquery will
be the affinity of the corresponding result column for
one of the individual SELECT statements that make up 
the compound.  
However, it is indeterminate which of the SELECT statements will 
be used to determine affinity.
Different constituent SELECT statements might be used to determine 
affinity at different times during query evaluation.
Best practice is to avoid mixing affinities in a compound SELECT.


<h2>Column Affinity Behavior Example</h2>

<p>The following SQL demonstrates how SQLite uses column affinity
to do type conversions when values are inserted into a table.</p>

^(<blockquote>
................................................................................
  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 compaff {comparison affinity rules}</tcl>
<h2>Type Conversions Prior To Comparison</h2>

<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 type affinity of the operands.


































<p>To "apply affinity" means to convert an operand to a particular storage
class if and only if the conversion is lossless and reversible.
^(Affinity is applied to operands of a comparison operator prior to
the comparison according to the following rules in the order shown:)^</p>

<ul>
<li><p>^If one operand has INTEGER, REAL or NUMERIC affinity