Wiki page
[checkin/718ead555b09892f3cbaddb1775ede4acfa4a2986d69f15da1270c433e5dd578] by
drh
2019-01-24 19:43:05.
D 2019-01-24T19:43:05.827
L checkin/718ead555b09892f3cbaddb1775ede4acfa4a2986d69f15da1270c433e5dd578
U drh
W 3061
Ticket [de3403bf5ae] describes a three-step algorithm for determining
column names, here repeated and expanded:
1. If there is an AS clause, then always use the right-hand side of the
AS as the column name. No exceptions.
2. If the expression is a reference to a column of a table in the FROM clause,
the use that column as the name of the result set column. If the
connection is configured with PRAGMA full_column_names=ON setting, then
the result-set column name is "TABLE.COLUMN". Otherwise the result-set
column name is just "COLUMN". Skip this step if the connection is
configured with PRAGMA short_column_names=OFF.
3. Use the text of the expression as it appears in the SELECT statement as
the column name. So if the expression is "a+b" then the result-set column
name will be "a+b".
The default column-name configuration is
* PRAGMA short_column_names=ON;
* PRAGMA full_column_names=OFF;
When computing the names of columns is a subquery in the FROM clause, the
default column-name configuration is always used, regardless of how the
connection is otherwise configured.
The above is how column names where computed beginning with check-in
[be0e24a0293f31b8] until now. This check-in introduces one very simple
change:
* The configured column-name setup is also used to compute the
names of columns in FROM-clause subqueries as long as the
full_column_names setting is OFF. The default column-name configuration
is only used if full_column_names is ON.
Formerly, the default column-name configuration would be used to compute
the names of FROM-clause subqueries regardless of the application-defined
setting.
Steps (1) and (3) above are always used when computing column names. This
check-in and the PRAGMAs only affect step (2). There are three possibilities
for step (2):
<ol type="A">
<li> Use a name of the form "COLUMN"
<li> Use a name of the form "TABLE.COLUMN"
<li> Skip step (2) entirely.
</ol>
The following table shows how the PRAGMAs affect step (2):
<table border="1">
<tr>
<th>full_column_names
<th>short_column_names
<th>Step (2) for result-set columns
<th>Step (2) for the column names of FROM-clause subqueries
</tr>
</tr>
<td align="center">ON
<td align="center">Don't care
<td align="center">B
<td align="center">A
</tr>
<tr>
<td align="center">OFF
<td align="center">ON
<td align="center">A
<td align="center">A
</tr>
<tr>
<td align="center">OFF
<td align="center">OFF
<td align="center">C
<td align="center" bgcolor="lightblue">C
</tr>
</table>
This check-in changes the bahavior of the square in the lower right
(the light blue blox)
from "A" to "C". Since the default column-name configuration shown in
in middle row is used by 99.9% of all applications, this should should
have minimal impact. Indeed, all rows other than the middle row are
deprecated, so applications should not be using any rows other than the
middle row anyhow.
Z e066851b73b13edceb6726e420afe805