Documentation Source Text
Check-in [825b2c64489a166296fcf290d1d1ab2434dbfc56]
Not logged in

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

Overview
SHA1 Hash:825b2c64489a166296fcf290d1d1ab2434dbfc56
Date: 2013-11-27 19:20:58
User: drh
Comment:Add a tentative news item for the 3.8.2 release. Updates to CAST documentation. Fix typos.
Tags And Properties
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/compile.in

199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
...
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
COMPILE_OPTION {SQLITE_MINIMUM_FILE_DESCRIPTOR=<i>N</i>} {
  The unix [VFS] will never use a file descriptor less than <i>N</i>.  The
  default value of <i>N</i> is 3.
  <p>
  Avoiding the use of low-numbered file descriptors is a defense against
  accidental database corruption.  If a database file was opened using
  file descriptor 2, for example, and then an assert() failed and invoked
  write(2,...), that would likely cause database corruption by overwritting
  part of the database file with the assertion error message.  Using only
  higher-valued file descriptors avoids this potential problem.  The 
  protection against
  using low-numbered file descriptors can be disabled by setting this
  compile-time option to 0.
}

................................................................................
  histogram data to help it make better index choices.  The downside of
  this compile-time option is that it violates the
  [query planner stability guarantee] making it more difficult to ensure
  consistent performance in mass-produced applications.
  <p>
  SQLITE_ENABLE_STAT4 is an enhancement of [SQLITE_ENABLE_STAT3].  STAT3
  only recorded histogram data for the left-most column of each index
  whereas the STAT4 enhancement records histograph data from all columns
  of each index.
  The [SQLITE_ENABLE_STAT3] compile-time option is a no-op and is ignored
  if the SQLITE_ENABLE_STAT4 compile-time option is used.
}

COMPILE_OPTION {SQLITE_ENABLE_TREE_EXPLAIN} {
  This option adds support for the [SQLITE_TESTCTRL_EXPLAIN_STMT] test-control







|







 







|







199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
...
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
COMPILE_OPTION {SQLITE_MINIMUM_FILE_DESCRIPTOR=<i>N</i>} {
  The unix [VFS] will never use a file descriptor less than <i>N</i>.  The
  default value of <i>N</i> is 3.
  <p>
  Avoiding the use of low-numbered file descriptors is a defense against
  accidental database corruption.  If a database file was opened using
  file descriptor 2, for example, and then an assert() failed and invoked
  write(2,...), that would likely cause database corruption by overwriting
  part of the database file with the assertion error message.  Using only
  higher-valued file descriptors avoids this potential problem.  The 
  protection against
  using low-numbered file descriptors can be disabled by setting this
  compile-time option to 0.
}

................................................................................
  histogram data to help it make better index choices.  The downside of
  this compile-time option is that it violates the
  [query planner stability guarantee] making it more difficult to ensure
  consistent performance in mass-produced applications.
  <p>
  SQLITE_ENABLE_STAT4 is an enhancement of [SQLITE_ENABLE_STAT3].  STAT3
  only recorded histogram data for the left-most column of each index
  whereas the STAT4 enhancement records histogram data from all columns
  of each index.
  The [SQLITE_ENABLE_STAT3] compile-time option is a no-op and is ignored
  if the SQLITE_ENABLE_STAT4 compile-time option is used.
}

COMPILE_OPTION {SQLITE_ENABLE_TREE_EXPLAIN} {
  This option adds support for the [SQLITE_TESTCTRL_EXPLAIN_STMT] test-control

Changes to pages/lang.in

805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
....
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944

1945

1946
1947
1948
1949
1950


1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
....
1992
1993
1994
1995
1996
1997
1998
1999

2000
2001
2002
2003
2004





2005
2006
2007
2008
2009
2010
2011

<tcl>hd_fragment primkeyconst {PRIMARY KEY} {PRIMARY KEY constraint}</tcl>
<p>^Each table in SQLite may have at most one <b>PRIMARY KEY</b>. ^If the
  keywords PRIMARY KEY are added to a column definition, then the primary key
  for the table consists of that single column. ^Or, if a PRIMARY KEY clause 
  is specified as a [table-constraint], then the primary key of the table
  consists of the list of columns specified as part of the PRIMARY KEY clause.
  ^An error is rasied if more than one PRIMARY KEY clause appears in a
  CREATE TABLE statement.  ^The PRIMARY KEY is optional for ordinary tables
  but is required for [WITHOUT ROWID] tables.

<p>If a table has a single column primary key and the declared type of that
  column is "INTEGER" and the table is not a [WITHOUT ROWID] table,
  then the column is known as an [INTEGER PRIMARY KEY].
  See below for a description of the special properties and behaviors
................................................................................
<p>^A [SELECT] statement used as either a scalar subquery or as the 
right-hand operand of an IN, NOT IN or EXISTS expression may contain 
references to columns in the outer query. Such a subquery is known as
a correlated subquery. ^A correlated subquery is reevaluated each time
its result is required. ^An uncorrelated subquery is evaluated only once
and the result reused as necessary.

<tcl>hd_fragment castexpr {CAST expression}</tcl>
<h3>CAST expressions</h3>


<p>A CAST expression is used to convert the value of &lt;expr&gt; to 

a different [storage class] in a similar way to the conversion that takes
place when a [column affinity] is applied to a value. ^Application of a CAST
expression is different to application of a column affinity, as
with a CAST expression the storage class conversion is forced even 
if it is lossy and irrreversible.



<p>^If the value of &lt;expr&gt; is NULL, then the result of the CAST
expression is also NULL. ^Otherwise, the storage class of the result value
is determined by applying the [rules for determining column affinity] to
the &lt;type-name&gt; specified as part of the CAST expression.

<table border=1>
<tr>
  <th> Affinity of &lt;type-name&gt;
  <th> Conversion Processing
<tr>
  <td> NONE 
................................................................................
       <p>^When casting a TEXT value to INTEGER, the longest possible prefix of
        the value that can be interpreted as an integer number is extracted from
        the TEXT value and the remainder ignored. ^Any leading spaces in the
        TEXT value when converting from TEXT to INTEGER are ignored. ^If there
        is no prefix that can be interpreted as an integer number, the result
        of the conversion is 0.

      <p>^A cast of a REAL value into an INTEGER will truncate the fractional

      part of the REAL.  ^If a REAL is greater than the greatest possible signed
      integer (+9223372036854775807) then the result is the greatest possible
      signed integer and if the REAL is less than the least possible signed
      integer (-9223372036854775808) then the result is the least possible
      signed integer.






<tr>
  <td> NUMERIC
  <td> ^Casting a TEXT or BLOB value into NUMERIC first does a forced
   conversion into REAL but then further converts the result into INTEGER if
   and only if the conversion from REAL to INTEGER is lossless and reversible.
   This is the only context in SQLite where the NUMERIC and INTEGER [affinities]







|







 







|


>
|
>
|
|
<
<
|
>
>


|

|







 







|
>
|




>
>
>
>
>







805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
....
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949


1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
....
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019

<tcl>hd_fragment primkeyconst {PRIMARY KEY} {PRIMARY KEY constraint}</tcl>
<p>^Each table in SQLite may have at most one <b>PRIMARY KEY</b>. ^If the
  keywords PRIMARY KEY are added to a column definition, then the primary key
  for the table consists of that single column. ^Or, if a PRIMARY KEY clause 
  is specified as a [table-constraint], then the primary key of the table
  consists of the list of columns specified as part of the PRIMARY KEY clause.
  ^An error is raised if more than one PRIMARY KEY clause appears in a
  CREATE TABLE statement.  ^The PRIMARY KEY is optional for ordinary tables
  but is required for [WITHOUT ROWID] tables.

<p>If a table has a single column primary key and the declared type of that
  column is "INTEGER" and the table is not a [WITHOUT ROWID] table,
  then the column is known as an [INTEGER PRIMARY KEY].
  See below for a description of the special properties and behaviors
................................................................................
<p>^A [SELECT] statement used as either a scalar subquery or as the 
right-hand operand of an IN, NOT IN or EXISTS expression may contain 
references to columns in the outer query. Such a subquery is known as
a correlated subquery. ^A correlated subquery is reevaluated each time
its result is required. ^An uncorrelated subquery is evaluated only once
and the result reused as necessary.

<tcl>hd_fragment castexpr {CAST expression} {CAST} {cast} {CAST operator}</tcl>
<h3>CAST expressions</h3>

<p>A CAST expression of the form "CAST(&lt;expr&gt; TO &lt;type-name&gt;)"
is used to convert the value of &lt;expr&gt; to 
a different [storage class] specified by &lt;type-name&gt;.
^A CAST conversion is similar to the conversion that takes
place when a [column affinity] is applied to a value except that with


the CAST operator the conversion always takes place even if the conversion
lossy and irreversible, whereas column affinity only changes the data type
of a value if the change is lossless and reversible.

<p>^If the value of &lt;expr&gt; is NULL, then the result of the CAST
expression is also NULL. ^Otherwise, the storage class of the result
is determined by applying the [rules for determining column affinity] to
the &lt;type-name&gt;.

<table border=1>
<tr>
  <th> Affinity of &lt;type-name&gt;
  <th> Conversion Processing
<tr>
  <td> NONE 
................................................................................
       <p>^When casting a TEXT value to INTEGER, the longest possible prefix of
        the value that can be interpreted as an integer number is extracted from
        the TEXT value and the remainder ignored. ^Any leading spaces in the
        TEXT value when converting from TEXT to INTEGER are ignored. ^If there
        is no prefix that can be interpreted as an integer number, the result
        of the conversion is 0.

      <p>^A cast of a REAL value into an INTEGER results in the integer
      between the REAL value and zero that is closest to the REAL value.
      ^If a REAL is greater than the greatest possible signed
      integer (+9223372036854775807) then the result is the greatest possible
      signed integer and if the REAL is less than the least possible signed
      integer (-9223372036854775808) then the result is the least possible
      signed integer.

      <p>Prior to SQLite version 3.8.2, casting a REAL value greater than
      +9223372036854775807.0 into an integer resulted in the most negative
      integer, -9223372036854775808.  This behavior was meant to emulate the
      behavior of x86/x64 hardware when doing the equivalent cast.

<tr>
  <td> NUMERIC
  <td> ^Casting a TEXT or BLOB value into NUMERIC first does a forced
   conversion into REAL but then further converts the result into INTEGER if
   and only if the conversion from REAL to INTEGER is lossless and reversible.
   This is the only context in SQLite where the NUMERIC and INTEGER [affinities]

Changes to pages/news.in

14
15
16
17
18
19
20


































21
22
23
24
25
26
27
...
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
  hd_puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  regsub -all {[Tt]icket #(\d+)} $txt \
      {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
  hd_resolve "<blockquote>$txt</blockquote>"
  hd_puts "<hr width=\"50%\">"
}



































newsitem {2013-10-17} {Release 3.8.1} {
  <p>SQLite [version 3.8.1] is a regularly scheduled maintenance release.
  Upgrading from the previous release is optional, though you should upgrade
  if you are using [partial indices] as there was a 
  [http://www.sqlite.org/src/info/a5c8ed66ca|bug] related to partial
  indices in the previous release that could result in an incorrect answer
................................................................................
  In addition to the ORDER BY fix, several other patches to fix obscure
  (and mostly harmless) bugs and to fix spelling errors in source code
  comments are also included in this release.
}

newsitem {2013-03-18} {Release 3.7.16} {
  SQLite [version 3.7.16] is a regularly scheduled release of SQLite.
  This release contains several langauge enhancements and improvements
  to the query optimizer.  A list of the major enhancements and optimizations
  can be see on the [version 3.7.16 | change log].

  There was one important bug fix
  (see [http://www.sqlite.org/src/info/fc7bd6358f | Ticket fc7bd6358f])
  that addresses an incorrect query result that could have occurred in
  a three-way join where the join constraints compared INTEGER columns







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|







14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
...
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
  hd_puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  regsub -all {[Tt]icket #(\d+)} $txt \
      {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
  hd_resolve "<blockquote>$txt</blockquote>"
  hd_puts "<hr width=\"50%\">"
}

newsitem {2013-12-10} {Release 3.8.2} {
  <p>SQLite [version 3.8.2] is a regularly scheduled maintenance release.
  Upgrading from the previous release is optional.

  <p>Version 3.8.2 adds support for [WITHOUT ROWID] tables.  This is a
  significant extension to SQLite.  Database files that contain WITHOUT ROWID
  tables are not readable or writable by prior versions of SQLite, however
  databases that do not use WITHOUT ROWID tables are fully backwards 
  and forwards compatible.

  <p>The 3.8.2 release contains a potentially incompatible change.  In
  all prior versions of SQLite, a [cast] from a very large positive 
  floating point number into an integer resulted in the most negative integer.
  In other words, CAST(+99.9e99 to INT) would yield -9223372036854775808.
  This behavior came about because it is what x86/x64 hardware does 
  for the equivalent cast in the C language.  But the behavior is
  bizarre.  And so it has been changed effective with this release so that
  a cast from a floating point number into an integer returns the integer
  between the floating point value and zero that is closest to the floating
  point value.  Hence, CAST(+99.9e99 to INT) now returns +9223372036854775807.
  Since routines like [sqlite3_column_int64()] do an implicit cast if the
  value being accessed is really a floating point number, they are also
  affected by this change.

  <p>Besides the two changes mentioned above, the 3.8.2 release also 
  includes a number of performance enhancements.  The
  [skip-scan optimization] is now available for databases that have been
  processed by [ANALYZE].  Constant SQL functions are now factored out of
  inner loops, which can result in a significant speedup for query that
  contain WHERE clause terms like "date>datetime('now','-2 days')".  And
  various high-runner internal routines have been refactored for reduced
  CPU load.
}

newsitem {2013-10-17} {Release 3.8.1} {
  <p>SQLite [version 3.8.1] is a regularly scheduled maintenance release.
  Upgrading from the previous release is optional, though you should upgrade
  if you are using [partial indices] as there was a 
  [http://www.sqlite.org/src/info/a5c8ed66ca|bug] related to partial
  indices in the previous release that could result in an incorrect answer
................................................................................
  In addition to the ORDER BY fix, several other patches to fix obscure
  (and mostly harmless) bugs and to fix spelling errors in source code
  comments are also included in this release.
}

newsitem {2013-03-18} {Release 3.7.16} {
  SQLite [version 3.7.16] is a regularly scheduled release of SQLite.
  This release contains several language enhancements and improvements
  to the query optimizer.  A list of the major enhancements and optimizations
  can be see on the [version 3.7.16 | change log].

  There was one important bug fix
  (see [http://www.sqlite.org/src/info/fc7bd6358f | Ticket fc7bd6358f])
  that addresses an incorrect query result that could have occurred in
  a three-way join where the join constraints compared INTEGER columns

Changes to pages/sqlite.in

151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
<p>Ordinary SQL statements are very much free-form, can be
spread across multiple lines, and can have whitespace and
comments anywhere.  But dot-commands are not like that.
The dot-commands are more restrictive:

<ul>
<li>A dot-command must begin with the "." at the left margin
    with no preceeding whitespace.
<li>The dot-command must be entirely contained on a single input line.
<li>A dot-command cannot occur in the middle of an ordinary SQL
    statement.  In other words, a dot-command cannot occur at a
    continuation prompt.
<li>Dot-commands do not recognize comments.
</ul>








|







151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
<p>Ordinary SQL statements are very much free-form, can be
spread across multiple lines, and can have whitespace and
comments anywhere.  But dot-commands are not like that.
The dot-commands are more restrictive:

<ul>
<li>A dot-command must begin with the "." at the left margin
    with no preceding whitespace.
<li>The dot-command must be entirely contained on a single input line.
<li>A dot-command cannot occur in the middle of an ordinary SQL
    statement.  In other words, a dot-command cannot occur at a
    continuation prompt.
<li>Dot-commands do not recognize comments.
</ul>