Documentation Source Text

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 
of any storage class.</p>

<p>All values in SQL statements, whether as literals embedded in SQL
statement text or [parameters] bound to 
[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.
  ^When an INTEGER or REAL is compared to another INTEGER or REAL, a
  numerical comparison is performed.</p>

  <li><p>^A TEXT value is less than a BLOB value.  ^When two TEXT values
  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