Documentation Source Text

Check-in [07b7749da8]
Login

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

Overview
Comment:Update the documentation on the affinity of the columns of VIEWs and subqueries that are generated from expressions to match the actual implementation of 10 years standing - namely such columns have BLOB affinity, not no affinity. Ticket https://www.sqlite.org/src/info/d52a29a9e6bc55c5
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 07b7749da88d54e555f0bec50f888cdeeab3062a2d338ee6963afbcba5115a5b
User & Date: drh 2019-08-05 12:54:08
Context
2019-08-05
20:39
Back out the previous change. We are instead going to modify the code to match the documentation. check-in: e16ada80d6 user: drh tags: trunk
12:55
Update the documentation on the affinity of the columns of VIEWs and subqueries that are generated from expressions to match the actual implementation of 10 years standing - namely such columns have BLOB affinity, not no affinity. Ticket https://www.sqlite.org/src/info/d52a29a9e6bc55c5 check-in: 16a418966a user: drh tags: branch-3.29
12:54
Update the documentation on the affinity of the columns of VIEWs and subqueries that are generated from expressions to match the actual implementation of 10 years standing - namely such columns have BLOB affinity, not no affinity. Ticket https://www.sqlite.org/src/info/d52a29a9e6bc55c5 check-in: 07b7749da8 user: drh tags: trunk
2019-08-02
17:54
In the checklist application, improve the submenu and simplify the login processing. (Originally checked in on the wrong branch.) check-in: ee656f1e92 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/datatype3.in.

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

  <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 (TEXT), 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.  







|
<
|
>
|
<











|







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

  <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 have the same affinity

as the expressions in the result set of the [SELECT] statement that implements the VIEW
or subquery.  Except, column of a VIEW or subquery that are generated by expressions
that have no affinity are assigned an affinity of BLOB.

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 (TEXT), 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, so that v1.y and v1.z columns have an affinity of BLOB.

<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.