Documentation Source Text

Check-in [9ba9e670e1]
Login

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

Overview
Comment:Clarify the comparison affinity documentation. Change the name of NONE affinity to BLOB. Update the Makefile so that it works with Ubuntu 14.04LTS.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9ba9e670e1559988c843ceeae2422634ab98cccd
User & Date: drh 2015-06-02 17:24:30.795
Context
2015-06-02
19:27
Add syntax for multi-column matches to fts5.in. (check-in: ec116c4004 user: dan tags: trunk)
17:24
Clarify the comparison affinity documentation. Change the name of NONE affinity to BLOB. Update the Makefile so that it works with Ubuntu 14.04LTS. (check-in: 9ba9e670e1 user: drh tags: trunk)
2015-05-30
20:51
Fix a typo in the SQLITE_BUSY_SNAPSHOT documentation. (check-in: fe158a6ae2 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to main.mk.
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
	rm -rf doc/matrix/images
	cp -r doc/images doc/matrix
	./tclsh $(DOC)/matrix.tcl

# Build the fts3 database used by the search script
#
parsehtml.so: $(DOC)/search/parsehtml.c
	gcc -g -shared -fPIC -DUSE_TCL_STUBS $(TCLINC) -I. -I$(SRC)/ext/fts3 $(DOC)/search/parsehtml.c $(TCLSTUBFLAGS) -o parsehtml.so

searchdb: parsehtml.so tclsh
	./tclsh $(DOC)/search/buildsearchdb.tcl
	cp $(DOC)/search/search.tcl doc/search
	chmod +x doc/search

always:	







|







139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
	rm -rf doc/matrix/images
	cp -r doc/images doc/matrix
	./tclsh $(DOC)/matrix.tcl

# Build the fts3 database used by the search script
#
parsehtml.so: $(DOC)/search/parsehtml.c
	gcc -g -shared -fPIC $(TCLINC) -I. -I$(SRC)/ext/fts3 $(DOC)/search/parsehtml.c $(TCLSTUBFLAGS) -o parsehtml.so

searchdb: parsehtml.so tclsh
	./tclsh $(DOC)/search/buildsearchdb.tcl
	cp $(DOC)/search/search.tcl doc/search
	chmod +x doc/search

always:	
Changes to pages/datatype3.in.
105
106
107
108
109
110
111
112
113




114
115
116
117
118
119
120
<p>^(Each column in an SQLite 3 database is assigned one of the
following type affinities:</p>
<ul>
	<li>TEXT</LI>
	<li>NUMERIC</LI>
	<li>INTEGER</LI>
        <li>REAL</li>
	<li>NONE</LI>
</ul>)^





<p>^A column with TEXT affinity stores all data using storage classes
NULL, TEXT or BLOB. ^If numerical data is inserted into a column with
TEXT affinity it is converted into text form before being stored.</p>

<p>^A column with NUMERIC affinity may contain values using all five
storage classes.  ^When text data is inserted into a NUMERIC column, the







|

>
>
>
>







105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
<p>^(Each column in an SQLite 3 database is assigned one of the
following type affinities:</p>
<ul>
	<li>TEXT</LI>
	<li>NUMERIC</LI>
	<li>INTEGER</LI>
        <li>REAL</li>
	<li>BLOB</LI>
</ul>)^

<p>(Historical note:  The "BLOB" type affinity used to be called "NONE".
But that term was easy to confuse with "no affinity" and so it was
renamed.)

<p>^A column with TEXT affinity stores all data using storage classes
NULL, TEXT or BLOB. ^If numerical data is inserted into a column with
TEXT affinity it is converted into text form before being stored.</p>

<p>^A column with NUMERIC affinity may contain values using all five
storage classes.  ^When text data is inserted into a NUMERIC column, the
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
values with no fractional component and stored in columns with REAL
affinity are written to disk as integers in order to take up less 
space and are automatically converted back into floating point as
the value is read out.
^This optimization is completely invisible at the SQL level and can only
be detected by examining the raw bits of the database file.)</p>

<p>^A column with affinity NONE does not prefer one storage class over
another and no attempt is made to coerce data from one storage class into
another.</p>

<tcl>hd_fragment affname {rules for determining column affinity}</tcl>
<h3>2.1 Determination Of Column Affinity</h3>

<p>^(The affinity of a column is determined by the declared type
of the column, according to the following rules in the order shown:)^</p>

<ol>
  <li><p>^If the declared type contains the string "INT" then it
  is assigned INTEGER affinity.</p>

  <li><p>^If the declared type of the column contains any of the strings
  "CHAR", "CLOB", or "TEXT" then that
  column has TEXT affinity.  ^Notice that the type VARCHAR contains the
  string "CHAR" and is thus assigned TEXT affinity.</p>

  <li><p>^If the declared type for a column
  contains the string "BLOB" or if
  no type is specified then the column has affinity NONE.</p>

  <li><p>^If the declared type for a column
  contains any of the strings "REAL", "FLOA",
  or "DOUB" then the column has REAL affinity.</p>

  <li><p>^Otherwise, the affinity is NUMERIC.</p>
</ol>







|




















|







148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
values with no fractional component and stored in columns with REAL
affinity are written to disk as integers in order to take up less 
space and are automatically converted back into floating point as
the value is read out.
^This optimization is completely invisible at the SQL level and can only
be detected by examining the raw bits of the database file.)</p>

<p>^A column with affinity BLOB does not prefer one storage class over
another and no attempt is made to coerce data from one storage class into
another.</p>

<tcl>hd_fragment affname {rules for determining column affinity}</tcl>
<h3>2.1 Determination Of Column Affinity</h3>

<p>^(The affinity of a column is determined by the declared type
of the column, according to the following rules in the order shown:)^</p>

<ol>
  <li><p>^If the declared type contains the string "INT" then it
  is assigned INTEGER affinity.</p>

  <li><p>^If the declared type of the column contains any of the strings
  "CHAR", "CLOB", or "TEXT" then that
  column has TEXT affinity.  ^Notice that the type VARCHAR contains the
  string "CHAR" and is thus assigned TEXT affinity.</p>

  <li><p>^If the declared type for a column
  contains the string "BLOB" or if
  no type is specified then the column has affinity BLOB.</p>

  <li><p>^If the declared type for a column
  contains any of the strings "REAL", "FLOA",
  or "DOUB" then the column has REAL affinity.</p>

  <li><p>^Otherwise, the affinity is NUMERIC.</p>
</ol>
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
  CLOB
<td align="center">TEXT
<td align="center">2

<tr><td align="center" valign="top">
  BLOB<br>
  <i>no datatype specified</i>
<td align="center">NONE
<td align="center">3

<tr><td align="center" valign="top">
  REAL<br>
  DOUBLE<br>
  DOUBLE PRECISION<br>
  FLOAT







|







229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
  CLOB
<td align="center">TEXT
<td align="center">2

<tr><td align="center" valign="top">
  BLOB<br>
  <i>no datatype specified</i>
<td align="center">BLOB
<td align="center">3

<tr><td align="center" valign="top">
  REAL<br>
  DOUBLE<br>
  DOUBLE PRECISION<br>
  FLOAT
330
331
332
333
334
335
336
337




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

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




Operand affinity is determined by the following rules:

<ul>
  <li><p>^The affinity of the right-hand operand of an IN or NOT IN
  operator is NONE if the operand is a list and is the same 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.
  ^(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(<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 NONE affinity.
</ul>

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

<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
and the other operand has TEXT or NONE affinity
then NUMERIC affinity is applied to other operand.

<li><p>^If one operand has TEXT affinity and the other has NONE affinity,
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>^(The expression "a BETWEEN b AND c" is treated as two separate







|
>
>
>
>
|



|
|
>











|












|


|







334
335
336
337
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

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

<p>Note that every table column as 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 affinity of 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.
  ^(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(<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>
<h3>3.3 Type Conversions Prior To Comparison</h3>

<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
and the other operand has TEXT or BLOB or no affinity
then NUMERIC affinity is applied to other operand.

<li><p>^If one operand has TEXT affinity and the other has no affinity,
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>^(The expression "a BETWEEN b AND c" is treated as two separate