Documentation Source Text

Check-in [0b44acac20]
Login

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

Overview
Comment:Update datatype3.html to mention that CAST expressions have an affinity.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 0b44acac2087059539713e0f28fa2544ca840ec7
User & Date: dan 2009-06-08 04:45:21.000
Context
2009-06-08
12:57
Fix typos in the privatebranch document. (check-in: dea8397e9a user: drh tags: trunk)
04:45
Update datatype3.html to mention that CAST expressions have an affinity. (check-in: 0b44acac20 user: dan tags: trunk)
00:34
Updates to the private branch document. (check-in: 78188e762a user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/datatype3.in.
︙︙
209
210
211
212
213
214
215
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


	<LI><P>When two BLOB values are compared, the result is always
	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.
For binary comparisons, this is done in the cases enumerated below.



The term "expression" used in the bullet points below means any

SQL scalar expression or literal other than a column value.  Note that

if X and Y.Z are a column names, then +X and +Y.Z are considered
expressions.</P>


<UL>
	<LI><P>When a column value is compared to the result of an
	expression, the affinity of the column is applied to the result of
	the expression before the comparison takes place.</P>







	<LI><P>When two column values are compared, if one column has
	INTEGER or REAL or NUMERIC affinity and the other does not, 
        then NUMERIC affinity is applied to any values with storage
        class TEXT extracted from the non-NUMERIC column.</P>

	<LI><P>When the results of two expressions are compared, no




        conversions occur.  The results are compared as is.  If a string
        is compared to a number, the number will always be less than the
        string.</P>
</UL>

<P>
In SQLite, the expression "a BETWEEN b AND c" is equivalent to "a &gt;= b
AND a &lt;= c", even if this means that different affinities are applied to
'a' in each of the comparisons required to evaluate the expression.
</P>








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

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








209
210
211
212
213
214
215
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


	<LI><P>When two BLOB values are compared, the result is always
	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 nominal affinity assigned to the expressions on
either side of the binary operator. Affinities are assigned to expressions
in the following cases:
<UL>
	<LI><P>An expression that is a simple reference to a column value
	has the same affinity as the column it refers to. 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>Conversions are applied before the comparison as described below.
In the following bullet points, the two operands are
refered to as expression A and expression B. Expressions A and B may
appear as either the left or right operands - the following statements
are true when considering both "A &lt;op&gt;B" and "B &lt;op&gt;A".
<UL>
	<LI><P>When two expressions are compared, if expression A has
	INTEGER or REAL or NUMERIC affinity and expression B does not, 
        then NUMERIC affinity is applied to the value of expression B

	before the comparison takes place.
	<LI><P>When two expressions are compared, if expression A has
	been assigned an affinity and expression B has not, then the
	affinity of expression A is applied to the value of expression B
	before the comparison takes place.
	<LI><P>Otherwise, if neither of the above applies, no conversions
	occur. The results are compared as is. If a string is compared to a
	number, the number will always be less than the string.

</UL>

<P>
In SQLite, the expression "a BETWEEN b AND c" is equivalent to "a &gt;= b
AND a &lt;= c", even if this means that different affinities are applied to
'a' in each of the comparisons required to evaluate the expression.
</P>
︙︙