SQLite

View Ticket
Login
Ticket Hash: 3b4450072511e6215466eba4237339ef70ca4f10
Title: Inconsistent column names
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2017-12-23 12:34:35
Version Found In: 3.21.0
User Comments:
drh added on 2017-12-22 19:28:56: (text/x-fossil-wiki)
In the SQL below, the result-set column name for the first SELECT is "a" but
the column name in the table created by the CREATE TABLE AS on an identical
SELECT is "x".

<blockquote><verbatim>
CREATE TABLE t1(x INT);
INSERT INTO t1(x) VALUES(1);
.headers on
SELECT a FROM (SELECT x AS a FROM t1);
CREATE TABLE t2 AS
SELECT a FROM (SELECT x AS a FROM t1);
PRAGMA table_info(t2);
</verbatim></blockquote>

This problem first appeared with check-in [ade7ddf1998190b2] which is a
fix for a prior ticket [de3403bf5ae5f72ed].

drh added on 2017-12-22 19:36:42: (text/x-fossil-wiki)
Another example:

<blockquote><verbatim>
.headers on
CREATE TABLE t1(aaa INT);
INSERT INTO t1(aaa) VALUES(1);
SELECT BBb FROM (SELECT aaa AS Bbb FROM t1);
CREATE TABLE t2 AS
SELECT BBb FROM (SELECT aaa AS Bbb FROM t1);
PRAGMA table_info(t2);
</verbatim></blockquote>

For the straight SELECT statement, recent code gives a column name of Bbb and a column name of aaa for the t2 table.  Older code gives the name BBb for both.