Documentation Source Text

Check-in [524ba450fb]
Login

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.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 524ba450fb959a7565a50082bb243373fa1f74fb
User & Date: drh 2017-01-12 20:25:44
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
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/datatype3.in.

184
185
186
187
188
189
190









191
192
193
194
195
196
197
...
350
351
352
353
354
355
356


357
358
359




360
361
362
363
364
365
366
</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
................................................................................
  <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.


  ^(Note that 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>^(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>







>
>
>
>
>
>
>
>
>







 







>
>
|

|
>
>
>
>







184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
...
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
</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
................................................................................
  <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>