Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Clarification of affinity, especially in regard to views and subqueries. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
513684f098df805ff0fc67fe981230bd |
User & Date: | drh 2017-01-18 02:41:55.664 |
Context
2017-01-28
| ||
14:45 | Updates to the performance graph spreadsheet. (check-in: 63d1835d75 user: drh tags: trunk) | |
2017-01-18
| ||
02:41 | Clarification of affinity, especially in regard to views and subqueries. (check-in: 513684f098 user: drh tags: trunk) | |
2017-01-12
| ||
20:25 | Proposed enhancement to the definition of column affinity so that it covers columns belonging to subqueries and VIEWs. (check-in: 524ba450fb user: drh tags: trunk) | |
Changes
Changes to pages/datatype3.in.
︙ | ︙ | |||
88 89 90 91 92 93 94 95 96 | formats and freely convert between formats using the built-in [date and time functions].</p> <tcl>hd_fragment affinity affinity {column affinity} {type affinity} {*affinities}</tcl> <h1>Type Affinity</h1> <p> In order to maximize compatibility between SQLite and other database | > > > > > > > > > > > > > > > > | | 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 | formats and freely convert between formats using the built-in [date and time functions].</p> <tcl>hd_fragment affinity affinity {column affinity} {type affinity} {*affinities}</tcl> <h1>Type Affinity</h1> <p> SQL database engines that use rigid typing will usually try to automatically convert values to the appropriate datatype. Consider this: <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> 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> |
︙ | ︙ | |||
184 185 186 187 188 189 190 | </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> | < < < < < < < < < | | 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 | </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>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 |
︙ | ︙ | |||
264 265 266 267 268 269 270 271 272 273 274 275 276 277 | <td align="center">5 </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. <h2>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> | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 | <td align="center">5 </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. <tcl>hd_fragment expraff {expression affinity} {Affinity Of Expressions}</tcl> <h2>Affinity Of Expressions</h2> <p>Every table column has a type affinity (one of BLOB, TEXT, INTEGER, REAL, or NUMERIC) but expressions do no necessarily have an affinity. <p>Expression affinity is determined by the following rules: <ul> <li><p>^The right-hand operand of an IN or NOT IN operator has no affinity if the operand is a list and has the same affinity as the affinity of the result set expression if the operand is a SELECT. <li><p>^When an expression is a simple reference to a column of a real table (not a [VIEW] or subquery) then the expression has the same affinity as the table column. <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.)^ <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.)^ </ul> <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>".)^ <li><p>^Otherwise, an expression has no affinity. </ul> <tcl>hd_fragment affview {rules for determining column affinity in VIEWs}</tcl> <h2>Column Affinity For Views And Subqueries</h2> <p>The "columns" of a [VIEW] or FROM-clause subquery are really the expressions in the result set of the [SELECT] 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: <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 (INTEGER), 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. <p>When the [SELECT] statement that implements a [VIEW] or FROM-clause subquery is a [compound SELECT] then the affinity of each supposed 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. Best practice is to avoid mixing affinities in a compound SELECT. <h2>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> |
︙ | ︙ | |||
338 339 340 341 342 343 344 | 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> | | | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < | 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 | 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 compaff {comparison affinity rules}</tcl> <h2>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>To "apply affinity" means to convert an operand to a particular storage class if and only if the conversion is lossless and reversible. ^(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 |
︙ | ︙ |