This information is obsolete. You are looking at the
CVSTrac source management system display
for SQLite that was replaced by
Fossil on 2009-08-11. The
information shown here has not been updated since that cut-over.
These pages are retained for historical
reference only.

This page describes how SQLite determines the names of columns
in the results set of a select. All the rules on this page except
for case 1 (the "AS" rule) are subject to change from one release of
SQLite to the next. The rules described here apply to SQLite
version 3.6.0. Prior versions of SQLite used different rules.
If an application depends on the column name, then it should use
an AS clause to set the column name explicitly.

There are two pragmas which control how column names are chosen:

PRAGMA short_column_names;
PRAGMA full_column_names;

Either pragma can be set to "true" or "false" or "on" or "off" or
"0" or "1". For example:

PRAGMA short_column_names=ON;
PRAGMA full_column_names=OFF;

If you omit the "=VALUE" part then the pragma returns the current
setting. The column names generated depend on the value of both
pragmas at the time the query statement was prepared. The default
settings for these pragmas are as shown above, short=ON and full=OFF.
If you want to change these settings, you will have to do so separately
for each database connection. The changes are not persistent. They
revert to their default value with each new connection.

Case 1: Result set expressions contain an "AS" clause

Whenever there is an AS clause after the expression that defines
a column of the result set, the string that follows the AS keyword
becomes the name of the column in the result set. The AS clause
overrides all other behavior. If an AS clause is present, it does
not matter what the settings of the short_column_name and
full_column_name pragmas are - the name of the column is always the
string that follows the AS keyword.

Case 2: Non-trivial result set expressions

A non-trivial result set expression is one that contains something
more than the name of a column from a table in the FROM clause.
Any expression that involves the use of a function or a mathematical
operator is considered non-trival. However, a table column name
that is enclosed in parentheses is still considered trivial.

The name of any non-trivial result set expression is a copy of the
text of that expression as it appeared in the SELECT statement.

Case 3: short_column_names=ON

If cases 1 and 2 do not apply and short_column_names=ON
then full_column_names is ignored. The
name of the result set column is the name of the corresponding
table column as it appears in the original CREATE TABLE statement.
So, for example, if you have the following CREATE TABLE:

CREATE TABLE example1(
abc INTEGER PRIMARY KEY,
Xyz text
);

And you do the following query:

SELECT rowid, xyz FROM example1;

The names of your result set columns will be "abc" and "Xyz",
not "rowid" and "xyz". The names of the columns
in the CREATE TABLE statement are used, not the names in the
SELECT statement.

Case 4: short_column_names=OFF and full_column_names=OFF

If cases 1 and 2 do not apply and short_column_names=OFF and
full_column_names=OFF then
the column name is original expression text as in case 2.
If the column has no expression text (because it is autmatically
generated using the "*" and "TABLE.*" wildcard)
then the short column names are used as in case 3.

Case 5: short_column_names=OFF and full_column_names=ON

If cases 1 and 2 do not apply and short_column_names=OFF and
full_column_names=ON then the result set column name is
constructed as "TABLE.COLUMN" where TABLE is the name of the
table from which the data is taken and COLUMN is the name of the
column within TABLE from which the data was taken.

Case 6: Column Names for VIEWs

In the absence of AS clauses,
the column names for VIEWs are computed as if
short_column_names=ON. This is true regardless
of the actual pragma settings.