Documentation Source Text

Check-in [9840457b31]
Login

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

Overview
Comment:Clarification of the automatic conversion rules for inserting TEXT into a NUMERIC column.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 9840457b31a319ae22ea111b962f6fbf97cd10c722843ebd0262546eaa65cbfd
User & Date: drh 2019-10-14 00:20:55
Context
2019-10-14
00:26
Further clarification of automatic affinity conversion rules. check-in: 3a3335a61e user: drh tags: trunk
00:20
Clarification of the automatic conversion rules for inserting TEXT into a NUMERIC column. check-in: 9840457b31 user: drh tags: trunk
2019-10-11
03:26
Version 3.30.1 check-in: bb0f52c956 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/datatype3.in.

   136    136   <p>^A column with TEXT affinity stores all data using storage classes
   137    137   NULL, TEXT or BLOB. ^If numerical data is inserted into a column with
   138    138   TEXT affinity it is converted into text form before being stored.</p>
   139    139   
   140    140   <p>^A column with NUMERIC affinity may contain values using all five
   141    141   storage classes.  ^When text data is inserted into a NUMERIC column, the
   142    142   storage class of the text is converted to INTEGER or REAL (in order of
   143         -preference) if such conversion is lossless and reversible.
   144         -^For conversions between TEXT and REAL storage classes, SQLite considers
   145         -the conversion to be lossless and reversible if the first 15 significant
   146         -decimal digits of the number are preserved.
   147         -^If the lossless conversion of TEXT to INTEGER or REAL is not possible then
   148         -the value is stored using the TEXT storage class. ^No
   149         -attempt is made to convert NULL or BLOB values.</p>
          143  +preference) if the text is a well-formed integer or real literal, respectively.
          144  +^If the TEXT value is a well-formed integer literal that is too large
          145  +to fit in a 64-bit signed integer, it is converted to REAL.
          146  +^For conversions between TEXT and REAL storage classes, only the first
          147  +15 significant decimal digits of the number are preserved.
          148  +^If the TEXT value is not a well-formed integer or real literal,
          149  +then the value is stored as TEXT.
          150  +^(For the purposes of this paragraph, hexadecimal integer
          151  +literals are not considered well-formed and are stored as TEXT.
          152  +(This is done for historical compatibility with versions of SQLite
          153  +prior to [version 3.8.6] [dateof:3.8.6] where hexadecimal integer
          154  +literals were first introduced into SQLite.))^
          155  +^No attempt is made to convert NULL or BLOB values.</p>
   150    156   
   151    157   <p>^A string might look like a floating-point literal with
   152    158   a decimal point and/or exponent notation but as long as
   153    159   the value can be expressed as an integer, the NUMERIC affinity will convert
   154    160   it into an integer. ^(Hence, the string '3.0e+5' is stored in a
   155    161   column with NUMERIC affinity as the integer 300000, not as the floating
   156    162   point value 300000.0.)^</p>