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
For this case (which was the default prior to version 3.1.0) the result is the same as for case 2 for simple queries and is the same as case 5 for joins. In other words, for a query with only a single table in the FROM clause, the name of the result set column matches the text of the expression that defined the column as in case 2. For a join, the column name is of the form "TABLE.COLUMN" where TABLE and COLUMN are the names of the table and column from which the data comes, as defined in the original CREATE TABLE statement.
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. If the table is aliased by the use of an AS clause in the FROM expression then the alias is used instead of the original table name.