diff options
Diffstat (limited to 'www/datatype3.html')
| -rw-r--r-- | www/datatype3.html | 967 |
1 files changed, 967 insertions, 0 deletions
diff --git a/www/datatype3.html b/www/datatype3.html new file mode 100644 index 0000000..e4e7712 --- /dev/null +++ b/www/datatype3.html @@ -0,0 +1,967 @@ +<!DOCTYPE html> +<html><head> +<meta name="viewport" content="width=device-width, initial-scale=1.0"> +<meta http-equiv="content-type" content="text/html; charset=UTF-8"> +<link href="sqlite.css" rel="stylesheet"> +<title>Datatypes In SQLite</title> +<!-- path= --> +</head> +<body> +<div class=nosearch> +<a href="index.html"> +<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0"> +</a> +<div><!-- IE hack to prevent disappearing logo --></div> +<div class="tagline desktoponly"> +Small. Fast. Reliable.<br>Choose any three. +</div> +<div class="menu mainmenu"> +<ul> +<li><a href="index.html">Home</a> +<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> +<li class='wideonly'><a href='about.html'>About</a> +<li class='desktoponly'><a href="docs.html">Documentation</a> +<li class='desktoponly'><a href="download.html">Download</a> +<li class='wideonly'><a href='copyright.html'>License</a> +<li class='desktoponly'><a href="support.html">Support</a> +<li class='desktoponly'><a href="prosupport.html">Purchase</a> +<li class='search' id='search_menubutton'> +<a href="javascript:void(0)" onclick='toggle_search()'>Search</a> +</ul> +</div> +<div class="menu submenu" id="submenu"> +<ul> +<li><a href='about.html'>About</a> +<li><a href='docs.html'>Documentation</a> +<li><a href='download.html'>Download</a> +<li><a href='support.html'>Support</a> +<li><a href='prosupport.html'>Purchase</a> +</ul> +</div> +<div class="searchmenu" id="searchmenu"> +<form method="GET" action="search"> +<select name="s" id="searchtype"> +<option value="d">Search Documentation</option> +<option value="c">Search Changelog</option> +</select> +<input type="text" name="q" id="searchbox" value=""> +<input type="submit" value="Go"> +</form> +</div> +</div> +<script> +function toggle_div(nm) { +var w = document.getElementById(nm); +if( w.style.display=="block" ){ +w.style.display = "none"; +}else{ +w.style.display = "block"; +} +} +function toggle_search() { +var w = document.getElementById("searchmenu"); +if( w.style.display=="block" ){ +w.style.display = "none"; +} else { +w.style.display = "block"; +setTimeout(function(){ +document.getElementById("searchbox").focus() +}, 30); +} +} +function div_off(nm){document.getElementById(nm).style.display="none";} +window.onbeforeunload = function(e){div_off("submenu");} +/* Disable the Search feature if we are not operating from CGI, since */ +/* Search is accomplished using CGI and will not work without it. */ +if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ +document.getElementById("search_menubutton").style.display = "none"; +} +/* Used by the Hide/Show button beside syntax diagrams, to toggle the */ +function hideorshow(btn,obj){ +var x = document.getElementById(obj); +var b = document.getElementById(btn); +if( x.style.display!='none' ){ +x.style.display = 'none'; +b.innerHTML='show'; +}else{ +x.style.display = ''; +b.innerHTML='hide'; +} +return false; +} +var antiRobot = 0; +function antiRobotGo(){ +if( antiRobot!=3 ) return; +antiRobot = 7; +var j = document.getElementById("mtimelink"); +if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); +} +function antiRobotDefense(){ +document.body.onmousedown=function(){ +antiRobot |= 2; +antiRobotGo(); +document.body.onmousedown=null; +} +document.body.onmousemove=function(){ +antiRobot |= 2; +antiRobotGo(); +document.body.onmousemove=null; +} +setTimeout(function(){ +antiRobot |= 1; +antiRobotGo(); +}, 100) +antiRobotGo(); +} +antiRobotDefense(); +</script> +<div class=fancy> +<div class=nosearch> +<div class="fancy_title"> +Datatypes In SQLite +</div> +<div class="fancy_toc"> +<a onclick="toggle_toc()"> +<span class="fancy_toc_mark" id="toc_mk">►</span> +Table Of Contents +</a> +<div id="toc_sub"><div class="fancy-toc1"><a href="#datatypes_in_sqlite">1. Datatypes In SQLite</a></div> +<div class="fancy-toc1"><a href="#storage_classes_and_datatypes">2. Storage Classes and Datatypes</a></div> +<div class="fancy-toc2"><a href="#boolean_datatype">2.1. Boolean Datatype</a></div> +<div class="fancy-toc2"><a href="#date_and_time_datatype">2.2. Date and Time Datatype</a></div> +<div class="fancy-toc1"><a href="#type_affinity">3. Type Affinity</a></div> +<div class="fancy-toc2"><a href="#determination_of_column_affinity">3.1. Determination Of Column Affinity</a></div> +<div class="fancy-toc3"><a href="#affinity_name_examples">3.1.1. Affinity Name Examples</a></div> +<div class="fancy-toc2"><a href="#affinity_of_expressions">3.2. Affinity Of Expressions</a></div> +<div class="fancy-toc2"><a href="#column_affinity_for_views_and_subqueries">3.3. Column Affinity For Views And Subqueries</a></div> +<div class="fancy-toc3"><a href="#column_affinity_for_compound_views">3.3.1. Column Affinity For Compound Views</a></div> +<div class="fancy-toc2"><a href="#column_affinity_behavior_example">3.4. Column Affinity Behavior Example</a></div> +<div class="fancy-toc1"><a href="#comparison_expressions">4. Comparison Expressions</a></div> +<div class="fancy-toc2"><a href="#sort_order">4.1. Sort Order</a></div> +<div class="fancy-toc2"><a href="#type_conversions_prior_to_comparison">4.2. Type Conversions Prior To Comparison</a></div> +<div class="fancy-toc2"><a href="#comparison_example">4.3. Comparison Example</a></div> +<div class="fancy-toc1"><a href="#operators">5. Operators</a></div> +<div class="fancy-toc1"><a href="#sorting_grouping_and_compound_selects">6. Sorting, Grouping and Compound SELECTs</a></div> +<div class="fancy-toc1"><a href="#collating_sequences">7. Collating Sequences</a></div> +<div class="fancy-toc2"><a href="#assigning_collating_sequences_from_sql">7.1. Assigning Collating Sequences from SQL</a></div> +<div class="fancy-toc2"><a href="#collation_sequence_examples">7.2. Collation Sequence Examples</a></div> +</div> +</div> +<script> +function toggle_toc(){ +var sub = document.getElementById("toc_sub") +var mk = document.getElementById("toc_mk") +if( sub.style.display!="block" ){ +sub.style.display = "block"; +mk.innerHTML = "▼"; +} else { +sub.style.display = "none"; +mk.innerHTML = "►"; +} +} +</script> +</div> + + + + + +<h1 id="datatypes_in_sqlite"><span>1. </span>Datatypes In SQLite</h1> +<p>Most SQL database engines (every SQL database engine other than SQLite, +as far as we know) uses static, rigid typing. With static typing, the datatype +of a value is determined by its container - the particular column in +which the value is stored.</p> + +<p>SQLite uses a more general dynamic type system. In SQLite, the datatype +of a value is associated with the value itself, not with its container. +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 statements that work on statically typed databases +work the same way in SQLite. However, the dynamic typing in SQLite allows +it to do things which are not possible in traditional rigidly typed +databases. <a href="flextypegood.html">Flexible typing is a feature</a> of SQLite, not a bug.</p> + +<p>Update: +As of version 3.37.0 (2021-11-27), SQLite provides <a href="stricttables.html">STRICT tables</a> +that do rigid type enforcement, for developers who prefer that kind of thing. + +<a name="storageclasses"></a> + +</p><h1 id="storage_classes_and_datatypes"><span>2. </span>Storage Classes and Datatypes</h1> + +<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><li><p><b>INTEGER</b>. The value is a signed integer, stored in 0, 1, + 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.</p> + + </li><li><p><b>REAL</b>. The value is a floating point value, stored as + an 8-byte IEEE floating point number.</p> + + </li><li><p><b>TEXT</b>. The value is a text string, stored using the + database encoding (UTF-8, UTF-16BE or UTF-16LE).</p> + + </li><li><p><b>BLOB</b>. The value is a blob of data, stored exactly as + it was input.</p> +</li></ul> + +<p>A storage class is more general than a datatype. +The INTEGER storage class, for example, includes 7 different integer +datatypes of different lengths. +<a href="fileformat2.html#record_format">This makes a difference on disk.</a> +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 interchangeably.</p> + +<p>Any column in an SQLite version 3 database, +except an <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> column, may be used to store a value +of any storage class.</p> + +<p>All values in SQL statements, whether they are literals embedded in SQL +statement text or <a href="lang_expr.html#varparam">parameters</a> bound to +<a href="c3ref/stmt.html">precompiled SQL statements</a> +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> + +<a name="boolean"></a> + +<h2 id="boolean_datatype"><span>2.1. </span>Boolean Datatype</h2> + +<p>SQLite does not have a separate Boolean storage class. +Instead, Boolean values are stored as integers 0 (false) and 1 (true).</p> + +<p>SQLite recognizes the keywords "TRUE" and "FALSE", +as of version 3.23.0 (2018-04-02) but those keywords are +really just alternative spellings for the integer literals 1 and 0 +respectively. + +<a name="datetime"></a> + +</p><h2 id="date_and_time_datatype"><span>2.2. </span>Date and Time Datatype</h2> + +<p>SQLite does not have a storage class set aside for storing +dates and/or times. +Instead, the built-in <a href="lang_datefunc.html">Date And Time Functions</a> 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><li><b>REAL</b> as Julian day numbers, the number of days since +noon in Greenwich on November 24, 4714 B.C. according to the +proleptic Gregorian calendar. +</li><li><b>INTEGER</b> as Unix Time, the number of seconds since +1970-01-01 00:00:00 UTC. +</li></ul> + +<p>Applications can choose to store dates and times in any of these +formats and freely convert between formats using the built-in +<a href="lang_datefunc.html">date and time functions</a>.</p> + + +<a name="affinity"></a> + +<h1 id="type_affinity"><span>3. </span>Type Affinity</h1> + +<p> +SQL database engines that use rigid typing will usually try to +automatically convert values to the appropriate datatype. Consider this: + +</p><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><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> + +<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><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 +storage class of the text is converted to INTEGER or REAL (in order of +preference) if the text is a well-formed integer or real literal, respectively. +If the TEXT value is a well-formed integer literal that is too large +to fit in a 64-bit signed integer, it is converted to REAL. +For conversions between TEXT and REAL storage classes, only the first +15 significant decimal digits of the number are preserved. +If the TEXT value is not a well-formed integer or real literal, +then the value is stored as TEXT. +For the purposes of this paragraph, hexadecimal integer +literals are not considered well-formed and are stored as TEXT. +(This is done for historical compatibility with versions of SQLite +prior to <a href="releaselog/3_8_6.html">version 3.8.6</a> 2014-08-15 where hexadecimal integer +literals were first introduced into SQLite.) +If a floating point value that can be represented exactly as an integer +is inserted into a column with NUMERIC affinity, the value is +converted into an integer. +No attempt is made to convert NULL or BLOB values.</p> + +<p>A string might look like a floating-point literal with +a decimal point and/or exponent notation but as long as +the value can be expressed as an integer, the NUMERIC affinity will convert +it into an integer. Hence, the string '3.0e+5' is stored in a +column with NUMERIC affinity as the integer 300000, not as the floating +point value 300000.0.</p> + +<p>A column that uses INTEGER affinity behaves the same as a column +with NUMERIC affinity. The difference between INTEGER and NUMERIC affinity +is only evident in a <a href="lang_expr.html#castexpr">CAST expression</a>: The expression +"CAST(4.0 AS INT)" returns an integer 4, whereas +"CAST(4.0 AS NUMERIC)" leaves the value as a floating-point 4.0.</p> + +<p>A column with REAL affinity behaves like a column with NUMERIC +affinity except that it forces integer values into floating point +representation. (As an internal optimization, small floating point +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> + +<a name="affname"></a> + +<h2 id="determination_of_column_affinity"><span>3.1. </span>Determination Of Column Affinity</h2> + +<p>For tables not declared as <a href="stricttables.html">STRICT</a>, +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><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><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><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><li><p>Otherwise, the affinity is NUMERIC.</p> +</li></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 id="affinity_name_examples"><span>3.1.1. </span>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 +(other than the large global <a href="limits.html#max_length">SQLITE_MAX_LENGTH</a> limit) on the length of +strings, BLOBs or numeric values.</p> + +<blockquote> +<table border="1" cellpadding="5"> +<tr><th>Example Typenames From The<br>CREATE TABLE Statement<br> + or CAST Expression + </th><th>Resulting Affinity + </th><th>Rule Used To Determine Affinity + +</th></tr><tr><td align="center" valign="top"> + INT<br> + INTEGER<br> + TINYINT<br> + SMALLINT<br> + MEDIUMINT<br> + BIGINT<br> + UNSIGNED BIG INT<br> + INT2<br> + INT8 +</td><td align="center">INTEGER +</td><td align="center">1 + +</td></tr><tr><td align="center" valign="top"> + CHARACTER(20)<br> + VARCHAR(255)<br> + VARYING CHARACTER(255)<br> + NCHAR(55)<br> + NATIVE CHARACTER(70)<br> + NVARCHAR(100)<br> + TEXT<br> + CLOB +</td><td align="center">TEXT +</td><td align="center">2 + +</td></tr><tr><td align="center" valign="top"> + BLOB<br> + <i>no datatype specified</i> +</td><td align="center">BLOB +</td><td align="center">3 + +</td></tr><tr><td align="center" valign="top"> + REAL<br> + DOUBLE<br> + DOUBLE PRECISION<br> + FLOAT +</td><td align="center">REAL +</td><td align="center">4 + +</td></tr><tr><td align="center" valign="top"> + NUMERIC<br> + DECIMAL(10,5)<br> + BOOLEAN<br> + DATE<br> + DATETIME +</td><td align="center">NUMERIC +</td><td align="center">5 +</td></tr></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. + +<a name="expraff"></a> + +</p><h2 id="affinity_of_expressions"><span>3.2. </span>Affinity Of Expressions</h2> + +<p>Every table column has a type affinity (one of BLOB, TEXT, INTEGER, +REAL, or NUMERIC) but expressions do not necessarily have an affinity. + +</p><p>Expression affinity is determined by the following rules: + +</p><ul> + <li><p>The right-hand operand of an IN or NOT IN + operator has no affinity if the operand is a list, or has the same + affinity as the affinity of the result set expression if the operand + is a SELECT. + + </p></li><li><p>When an expression is a simple reference to a column of a + real table (not a <a href="lang_createview.html">VIEW</a> or subquery) then the expression + has the same affinity as the table column. + </p><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. + </p></li><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. + </p></li></ul> + + </li><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>". + + </p></li><li><p>A COLLATE operator has the same affinity as its left-hand side operand. + + </p></li><li><p>Otherwise, an expression has no affinity. +</p></li></ul> + +<a name="affview"></a> + +<h2 id="column_affinity_for_views_and_subqueries"><span>3.3. </span>Column Affinity For Views And Subqueries</h2> + +<p>The "columns" of a <a href="lang_createview.html">VIEW</a> or FROM-clause subquery are really +the expressions +in the result set of the <a href="lang_select.html">SELECT</a> 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: + +</p><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 (TEXT), 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. + +<a name="affcompoundview"></a> + +</p><h3 id="column_affinity_for_compound_views"><span>3.3.1. </span>Column Affinity For Compound Views</h3> + +<p>When the <a href="lang_select.html">SELECT</a> statement that implements a <a href="lang_createview.html">VIEW</a> or +FROM-clause subquery is a <a href="lang_select.html#compound">compound SELECT</a> then the affinity of +each 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. The choice +might vary across different versions of SQLite. The choice might +change between one query and the next in the same version of SQLite. +The choice might be different at different times within the same +query. Hence, you can never be sure what affinity will be used +for columns of a compound SELECT that have different affinities in +the constituent subqueries. + +</p><p>Best practice is to avoid mixing affinities in a compound SELECT +if you care about the datatype of the result. Mixing affinities in +a compound SELECT can lead to surprising and unintuitive results. +See, for example, <a href="https://sqlite.org/forum/forumpost/02d7be94d7">forum post 02d7be94d7</a>. + +</p><h2 id="column_affinity_behavior_example"><span>3.4. </span>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> +<pre> +CREATE TABLE t1( + t TEXT, -- text affinity by rule 2 + nu NUMERIC, -- numeric affinity by rule 5 + i INTEGER, -- integer affinity by rule 1 + r REAL, -- real affinity by rule 4 + no BLOB -- no affinity by rule 3 +); + +-- Values stored as TEXT, INTEGER, INTEGER, REAL, TEXT. +INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0', '500.0'); +SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; +text|integer|integer|real|text + +-- Values stored as TEXT, INTEGER, INTEGER, REAL, REAL. +DELETE FROM t1; +INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0, 500.0); +SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; +text|integer|integer|real|real + +-- Values stored as TEXT, INTEGER, INTEGER, REAL, INTEGER. +DELETE FROM t1; +INSERT INTO t1 VALUES(500, 500, 500, 500, 500); +SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; +text|integer|integer|real|integer + +-- BLOBs are always stored as BLOBs regardless of column affinity. +DELETE FROM t1; +INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500', x'0500'); +SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; +blob|blob|blob|blob|blob + +-- NULLs are also unaffected by affinity +DELETE FROM t1; +INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL); +SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; +null|null|null|null|null +</pre> +</blockquote> + +<a name="comparisons"></a> + +<h1 id="comparison_expressions"><span>4. </span>Comparison Expressions</h1> + +<p>SQLite version 3 has the usual set of SQL comparison operators +including "=", "==", "<", "<=", ">", ">=", "!=", "", +"IN", "NOT IN", "BETWEEN", "IS", and "IS NOT", . + +<a name="sortorder"></a> + +</p><h2 id="sort_order"><span>4.1. </span>Sort Order</h2> + +<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><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><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><li><p>When two BLOB values are compared, the result is + determined using memcmp().</p> +</li></ul> + +<a name="compaff"></a> + +<h2 id="type_conversions_prior_to_comparison"><span>4.2. </span>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><p>To "apply affinity" means to convert an operand to a particular storage +class if and only if the conversion does not lose essential information. +Numeric values can always be converted into TEXT. TEXT values +can be converted into numeric values if the text content is a well-formed +integer or real literal, but not a hexadecimal integer literal. +BLOB values are converted into TEXT values by simply interpreting +the binary BLOB content as a text string in the current database +encoding. + +</p><p>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. + +</p></li><li><p>If one operand has TEXT affinity and the other has no affinity, +then TEXT affinity is applied to the other operand. + +</p></li><li><p>Otherwise, no affinity is applied and both operands are compared +as is.</p> +</li></ul> + +<p>The expression "a BETWEEN b AND c" is treated as two separate +binary comparisons "a >= b AND a <= c", even if that means +different affinities are applied to 'a' in each of the comparisons. +Datatype conversions in comparisons of the +form "x IN (SELECT y ...)" are handled as if +the comparison were really "x=y". +The expression "a IN (x, y, z, ...)" is equivalent to "a = +x OR +a = +y OR a = +z OR ...". +In other words, the values to the right of the IN operator (the "x", "y", +and "z" values in this example) are considered to have no affinity, +even if they happen to be column values or CAST expressions. +</p> + +<h2 id="comparison_example"><span>4.3. </span>Comparison Example</h2> + +<blockquote> +<pre> +CREATE TABLE t1( + a TEXT, -- text affinity + b NUMERIC, -- numeric affinity + c BLOB, -- no affinity + d -- no affinity +); + +-- Values will be stored as TEXT, INTEGER, TEXT, and INTEGER respectively +INSERT INTO t1 VALUES('500', '500', '500', 500); +SELECT typeof(a), typeof(b), typeof(c), typeof(d) FROM t1; +text|integer|text|integer + +-- Because column "a" has text affinity, numeric values on the +-- right-hand side of the comparisons are converted to text before +-- the comparison occurs. +SELECT a < 40, a < 60, a < 600 FROM t1; +0|1|1 + +-- Text affinity is applied to the right-hand operands but since +-- they are already TEXT this is a no-op; no conversions occur. +SELECT a < '40', a < '60', a < '600' FROM t1; +0|1|1 + +-- Column "b" has numeric affinity and so numeric affinity is applied +-- to the operands on the right. Since the operands are already numeric, +-- the application of affinity is a no-op; no conversions occur. All +-- values are compared numerically. +SELECT b < 40, b < 60, b < 600 FROM t1; +0|0|1 + +-- Numeric affinity is applied to operands on the right, converting them +-- from text to integers. Then a numeric comparison occurs. +SELECT b < '40', b < '60', b < '600' FROM t1; +0|0|1 + +-- No affinity conversions occur. Right-hand side values all have +-- storage class INTEGER which are always less than the TEXT values +-- on the left. +SELECT c < 40, c < 60, c < 600 FROM t1; +0|0|0 + +-- No affinity conversions occur. Values are compared as TEXT. +SELECT c < '40', c < '60', c < '600' FROM t1; +0|1|1 + +-- No affinity conversions occur. Right-hand side values all have +-- storage class INTEGER which compare numerically with the INTEGER +-- values on the left. +SELECT d < 40, d < 60, d < 600 FROM t1; +0|0|1 + +-- No affinity conversions occur. INTEGER values on the left are +-- always less than TEXT values on the right. +SELECT d < '40', d < '60', d < '600' FROM t1; +1|1|1 +</pre> +</blockquote> + +<p>All of the results in the example are the same if the comparisons are +commuted - if expressions of the form "a<40" are rewritten +as "40>a". + +</p><h1 id="operators"><span>5. </span>Operators</h1> + +<p>Mathematical operators (+, -, *, /, %, <<, >>, +&, and |) interpret both operands as if they were numbers. +STRING or BLOB operands automatically convert into REAL or INTEGER values. +If the STRING or BLOB looks like a real number (if it has a decimal point +or an exponent) or if the value is outside the range that can be represented +as a 64-bit signed integer, then it converts to REAL. Otherwise the operand +converts to INTEGER. +The implied type conversion of mathematical operands is slightly different +from <a href="lang_expr.html#castexpr">CAST to NUMERIC</a> in that string and BLOB values that +look like real numbers but have no fractional part are kept as REAL +instead of being converted into INTEGER as they would be for <a href="lang_expr.html#castexpr">CAST to NUMERIC</a>. +The conversion from STRING or BLOB into REAL or INTEGER is performed +even if it is lossy and irreversible. +Some mathematical operators (%, <<, >>, &, and |) expect +INTEGER operands. For those operators, REAL operands are converted into INTEGER +in the same way as a <a href="lang_expr.html#castexpr">CAST to INTEGER</a>. +The <<, >>, &, and | operators always return an INTEGER (or NULL) +result, but the % operator returns either INTEGER or REAL (or NULL) +depending on the type of its operands. +A NULL operand on a mathematical operator yields a NULL result. +An operand on a mathematical operator that does not look in any way +numeric and is not NULL is converted to 0 or 0.0. +Division by zero gives a result of NULL. +</p> + +<h1 id="sorting_grouping_and_compound_selects"><span>6. </span>Sorting, Grouping and Compound SELECTs</h1> + +<p>When query results are sorted by an ORDER BY clause, values with storage +class NULL come first, followed by INTEGER and REAL values +interspersed in numeric order, followed by TEXT values in collating +sequence order, and finally BLOB values in memcmp() order. No storage +class conversions occur before the sort.</p> + +<p>When grouping values with the GROUP BY clause values with +different storage classes are considered distinct, except for INTEGER +and REAL values which are considered equal if they are numerically +equal. No affinities are applied to any values as the result of a +GROUP by clause.</p> + +<p>The compound SELECT operators UNION, +INTERSECT and EXCEPT perform implicit comparisons between values. +No affinity is applied to comparison operands for the implicit +comparisons associated with UNION, INTERSECT, or EXCEPT - the values +are compared as is.</p> + +<a name="collation"></a> + +<h1 id="collating_sequences"><span>7. </span>Collating Sequences</h1> + +<p>When SQLite compares two strings, it uses a collating sequence or +collating function (two terms for the same thing) to determine which +string is greater or if the two strings are equal. +SQLite has three built-in collating functions: BINARY, NOCASE, and +RTRIM.</p> + +<ul> +<li><b>BINARY</b> - Compares string data using memcmp(), regardless + of text encoding.</li> +<li><b>NOCASE</b> - Similar to binary, except that it uses + <a href="c3ref/stricmp.html">sqlite3_strnicmp()</a> for the comparison. Hence the 26 upper case + characters of ASCII are folded to their lower case equivalents before + the comparison is performed. Note that only ASCII characters + are case folded. SQLite does not attempt to do full + UTF case folding due to the size of the tables required. + Also note that any U+0000 characters in the string are considered + string terminators for comparison purposes. + +</li><li><b>RTRIM</b> - The same as binary, except that trailing space + characters are ignored.</li> +</ul> + +<p>An application can register additional collating functions using +the <a href="c3ref/create_collation.html">sqlite3_create_collation()</a> interface.</p> + +<p>Collating functions only matter when comparing string values. +Numeric values are always compared numerically, and BLOBs are always +compared byte-by-byte using memcmp().</p> + +<h2 id="assigning_collating_sequences_from_sql"><span>7.1. </span>Assigning Collating Sequences from SQL</h2> + +<p> +Every column of every +table has an associated collating function. If no collating function +is explicitly defined, then the collating function defaults to BINARY. +The COLLATE clause of the <a href="lang_createtable.html#tablecoldef">column definition</a> is used +to define alternative collating functions for a column. +</p> + +<a name="colrules"></a> + + +<p> +The rules for determining which collating function to use for a +binary comparison operator (=, <, >, <=, >=, !=, IS, and +IS NOT) are as follows: + +</p><ol> +<li><p>If either operand has an explicit collating function assignment +using the postfix <a href="lang_expr.html#collateop">COLLATE operator</a>, then the explicit collating function +is used for comparison, with precedence to the collating function of the +left operand.</p></li> + +<li><p>If either operand is a column, then the collating function of +that column is used with precedence to the left operand. +For the purposes of the previous sentence, a column name +preceded by one or more unary "+" operators and/or CAST operators +is still considered a column name. +</p></li> + +<li><p>Otherwise, the BINARY collating function is used for comparison. +</p></li> +</ol> + +<p> +An operand of a comparison is considered to have an explicit +collating function assignment (rule 1 above) +if any subexpression of the operand uses +the postfix <a href="lang_expr.html#collateop">COLLATE operator</a>. Thus, if a <a href="lang_expr.html#collateop">COLLATE operator</a> is used +anywhere in a comparison expression, the collating function defined +by that operator is used for string comparison regardless of what +table columns might be a part of that expression. If two or more +<a href="lang_expr.html#collateop">COLLATE operator</a> subexpressions appear anywhere in a comparison, the +left most explicit collating function is used regardless of how deeply the +COLLATE operators are nested in the expression and regardless of +how the expression is parenthesized. +</p> + +<p> +The expression "x BETWEEN y and z" is logically +equivalent to two comparisons "x >= y AND x <= z" and works with +respect to collating functions as if it were two separate comparisons. +The expression "x IN (SELECT y ...)" is handled in the same way as the +expression "x = y" for the purposes of determining the collating sequence. +The collating sequence used for expressions of the form +"x IN (y, z, ...)" is the collating sequence of x. +If an explicit collating sequence is required on an IN operator +it should be applied to the left operand, like this: +"x COLLATE nocase IN (y,z, ...)". +</p> + +<p> +Terms of the ORDER BY clause that is part of a <a href="lang_select.html">SELECT</a> +statement may be assigned a collating sequence using the +<a href="lang_expr.html#collateop">COLLATE operator</a>, in which case the specified collating function is +used for sorting. +Otherwise, if the expression sorted by an ORDER BY clause is +a column, then the collating sequence of the column is used to +determine sort order. If the expression is not a column and has no +COLLATE clause, then the BINARY collating sequence is used. +</p> + +<h2 id="collation_sequence_examples"><span>7.2. </span>Collation Sequence Examples</h2> +<p> +The examples below identify the collating sequences that would be used to +determine the results of text comparisons that may be performed by various +SQL statements. Note that a text comparison may not be required, and no +collating sequence used, in the case of numeric, blob or NULL values. +</p> +<blockquote> +<pre> +CREATE TABLE t1( + x INTEGER PRIMARY KEY, + a, /* collating sequence BINARY */ + b COLLATE BINARY, /* collating sequence BINARY */ + c COLLATE RTRIM, /* collating sequence RTRIM */ + d COLLATE NOCASE /* collating sequence NOCASE */ +); + /* x a b c d */ +INSERT INTO t1 VALUES(1,'abc','abc', 'abc ','abc'); +INSERT INTO t1 VALUES(2,'abc','abc', 'abc', 'ABC'); +INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc'); +INSERT INTO t1 VALUES(4,'abc','abc ','ABC', 'abc'); + +/* Text comparison a=b is performed using the BINARY collating sequence. */ +SELECT x FROM t1 WHERE a = b ORDER BY x; +--result 1 2 3 + +/* Text comparison a=b is performed using the RTRIM collating sequence. */ +SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x; +--result 1 2 3 4 + +/* Text comparison d=a is performed using the NOCASE collating sequence. */ +SELECT x FROM t1 WHERE d = a ORDER BY x; +--result 1 2 3 4 + +/* Text comparison a=d is performed using the BINARY collating sequence. */ +SELECT x FROM t1 WHERE a = d ORDER BY x; +--result 1 4 + +/* Text comparison 'abc'=c is performed using the RTRIM collating sequence. */ +SELECT x FROM t1 WHERE 'abc' = c ORDER BY x; +--result 1 2 3 + +/* Text comparison c='abc' is performed using the RTRIM collating sequence. */ +SELECT x FROM t1 WHERE c = 'abc' ORDER BY x; +--result 1 2 3 + +/* Grouping is performed using the NOCASE collating sequence (Values +** 'abc', 'ABC', and 'Abc' are placed in the same group). */ +SELECT count(*) FROM t1 GROUP BY d ORDER BY 1; +--result 4 + +/* Grouping is performed using the BINARY collating sequence. 'abc' and +** 'ABC' and 'Abc' form different groups */ +SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1; +--result 1 1 2 + +/* Sorting or column c is performed using the RTRIM collating sequence. */ +SELECT x FROM t1 ORDER BY c, x; +--result 4 1 2 3 + +/* Sorting of (c||'') is performed using the BINARY collating sequence. */ +SELECT x FROM t1 ORDER BY (c||''), x; +--result 4 2 3 1 + +/* Sorting of column c is performed using the NOCASE collating sequence. */ +SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x; +--result 2 4 3 1 +</pre> +</blockquote> + + |