Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Proposed enhancement to the definition of column affinity so that it covers columns belonging to subqueries and VIEWs. |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
524ba450fb959a7565a50082bb243373 |
| User & Date: | drh 2017-01-12 20:25:44.964 |
Context
|
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) | |
| 20:14 | Begin adding changes for the 3.17.0 release. (check-in: 180288b238 user: drh tags: trunk) | |
Changes
Changes to pages/datatype3.in.
| ︙ | ︙ | |||
184 185 186 187 188 189 190 191 192 193 194 195 196 197 | </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> <h2>Affinity Name Examples</h2> <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 | > > > > > > > > > | 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 | </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>Determination Of Column Affinity For Views And Subqueries</h3> <p>^The affinity for a column of a view or subquery is the affinity of the corresponding result set entry for the [SELECT] statement that implements the view or subquery. ^When the [SELECT] statement that implements a view or subqueries is a [compound SELECT] then the affinity is determined by the left-most query in the compound. <h2>Affinity Name Examples</h2> <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 |
| ︙ | ︙ | |||
350 351 352 353 354 355 356 | <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>^An expression that is a simple reference to a column value has the same affinity as the column. | > > | | > > > > | 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 | <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>^An expression that is a simple reference to a column value has the same affinity as the column. <ul> <li><p> ^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> ^This rule applies regardless of whether the column belongs to a table, subquery, or [VIEW]. </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> |
| ︙ | ︙ |