SQLite

Check-in [4692a85edb]
Login

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

Overview
Comment:Clarifications to the datatype3.html document. (CVS 3657)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 4692a85edbe27d512f1830b8fb3e2b05b92a0cb5
User & Date: drh 2007-02-23 14:20:38.000
Context
2007-02-23
23:13
Disable the OR optimization if it would conflict with column affinity coercions. Ticket #2249. Additional cleanup and testing of the OR optimization. (CVS 3658) (check-in: 908daaa9ab user: drh tags: trunk)
14:20
Clarifications to the datatype3.html document. (CVS 3657) (check-in: 4692a85edb user: drh tags: trunk)
03:00
Clarify the documentation on how comparisons occur in an IN operator. Fix the comparison operators when both sides of an IN operator are expressions (ticket #2248). Changes to main.mk for adding FTS2 into the standard build also got mixed in with this check-in by mistake. (CVS 3656) (check-in: da81725ca1 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to www/datatype3.tcl.
1
2
3
4
5
6
7
8
set rcsid {$Id: datatype3.tcl,v 1.15 2007/02/23 03:00:46 drh Exp $}
source common.tcl
header {Datatypes In SQLite Version 3}
puts {
<h2>Datatypes In SQLite Version 3</h2>

<h3>1. Storage Classes</h3>

|







1
2
3
4
5
6
7
8
set rcsid {$Id: datatype3.tcl,v 1.16 2007/02/23 14:20:38 drh Exp $}
source common.tcl
header {Datatypes In SQLite Version 3}
puts {
<h2>Datatypes In SQLite Version 3</h2>

<h3>1. Storage Classes</h3>

163
164
165
166
167
168
169

170
171
172
173
174
175
176

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


<h3>3. Comparison Expressions</h3>

<P>Like SQLite version 2, version 3
features the binary comparison operators '=',
'&lt;', '&lt;=', '&gt;=' and '!=', an operation to test for set
membership, 'IN', and the ternary comparison operator 'BETWEEN'.</P>
<P>The results of a comparison depend on the storage classes of the







>







163
164
165
166
167
168
169
170
171
172
173
174
175
176
177

-- 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">
<h3>3. Comparison Expressions</h3>

<P>Like SQLite version 2, version 3
features the binary comparison operators '=',
'&lt;', '&lt;=', '&gt;=' and '!=', an operation to test for set
membership, 'IN', and the ternary comparison operator 'BETWEEN'.</P>
<P>The results of a comparison depend on the storage classes of the
192
193
194
195
196
197
198
199


200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
	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.</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 NUMERIC affinity and the other does not, the 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>








|
>
>






|
|
|







193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
	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>