Behavior of Select expressions containing select Consider the following setup:

CREATE TABLE NAMES(Id integer PRIMARY KEY, Name text);

INSERT INTO NAMES VALUES(1,'Tom');
INSERT INTO NAMES VALUES(2,'Lucy');
INSERT INTO NAMES VALUES(3,'Frank');

CREATE TABLE fields(fid integer, fname text);
INSERT into fields values(1, "Id");
insert into fields values(2, "Name");

SELECT name, (SELECT "Name") FROM NAMES;


The last statement will output

Tom|Tom
Lucy|Lucy
Frank|Frank


However, although select fname from fields where fid=2 yields ("Name") (as a single-cell table), replacing the last statement in the above query by

SELECT name, (select fname from fields where fid=2) FROM NAMES;


gives 

Tom|Name
Lucy|Name
Frank|Name


Why? Is this intended?

Note: This has been tested using SQLite v3.20
In SQL, 'single quotes' are used for string values, while "double quotes" are used for table/column names.

SQLite allows all types of quotes for compatibility with other databases, but it uses the correct interpretation first, if possible.

So in the subquery (SELECT "Name"), it will try to use a column named Name, and there is one. In other words, the first query is interpreted as:

SELECT NAMES.Name, (SELECT NAMES.Name) FROM NAMES;


(The subquery just refers to the value in the current row of the outer query; this is a rather pointless instance of a correlated subquery.)



SQLite is designed as an embedded database that is accessed from a 'real'
 programming language, so it has no built-in mechanism for dynamic SQL. You have to do the column name lookup separately:

cursor = db.execute("select fname from fields where fid = ?", [2])
fname = cursor.fetchone()[0]
cursor = db.execute("SELECT name, {} FROM NAMES".format(fname))
for name, othercolumn in cursor:
    ...