SQLite

The name of the output column on the SELECT statement below as reported by the
sqlite3_column_name() interface changed in between versions 3.18.0 and 3.19.0.

<blockquote><verbatim>
CREATE TABLE x(a,b);
INSERT INTO x VALUES(1,2);
SELECT x.a FROM x LEFT JOIN (SELECT * FROM x) AS y;
</verbatim></blockquote>

SQLite version 3.18.0 and earlier reports
the column name as "a".
But in SQLite 3.19.0 and 3.20.0, the
column name is reported as "x.a".

The SQLite documentation clearly states that, if there is no AS clause,
the names of output columns are indeterminate, arbitrary, and subject 
to change.  The column names are intended for display purposes only.
And yet, many applications depend on particular column names.
This name shift resulted at least one application error.

The problem was introduced by check-in [41c27bc0ff1d3135], which added a new
optimization for doing query flattening on the right operand of a LEFT JOIN.

<b>Analysis:</b>

Without an AS clause, if the output column is a direct reference to one
of the input table columns, then the output column name will be the same
as the input column name.  Except, if query flattening occurs, the output
column name becomes an exact copy of the input SQL text.

The exception explains the name shift.  Prior to version 3.19.0, the query
flattener would not operate on a LEFT JOIN.  Hence the output column name
was derived from the input column name and was "a".  But once the LEFT JOIN
query flattener optimization was added by check-in [41c27bc0ff1d3135], the
column naming exception mentioned in the previous paragraph kicked in and
caused the column to be named as it was typed in the input SQL:  "x.a".

Column name shift due to LEFT JOIN query flattening