Hi,
The following is a reduced test-case of a problem I have observed:
CREATE TABLE a(tid,pid);
CREATE TABLE b(tid,pname);
CREATE TEMP TABLE pidmap(newpid INTEGER,oldpid INTEGER);
CREATE TEMP TABLE pnamemap(pid INTEGER,pname TEXT COLLATE BINARY);
(Please note that the tables are usually populated with data.)
SELECT a.ROWID,b.ROWID FROM a
INNER JOIN b ON a.tid=b.tid
AND a.pid=(SELECT pid FROM pidmap WHERE pname=b.pname);
The same problem occurs with a similar query:
SELECT a.ROWID,b.ROWID FROM a
INNER JOIN b ON a.tid=b.tid
WHERE a.pid=(SELECT pid FROM pidmap WHERE pname=b.pname);
The problem comes from an unintentional mistake: using the table
pidmap in the sub-query, rather than pnamemap.
Running the sub-query on its own gives (as expected):
SELECT pid FROM pidmap;
Error: no such column: pid
This also results in an error:
SELECT a.ROWID,b.ROWID FROM a
INNER JOIN b ON a.tid=b.tid
AND a.pid=(SELECT xyz FROM pidmap WHERE pname=b.pname);
Error: no such column: xyz
And, of course, correcting the original query works as expected:
SELECT a.ROWID,b.ROWID FROM a
INNER JOIN b ON a.tid=b.tid
AND a.pid=(SELECT pid FROM pnamemap WHERE pname=b.pname);
Seems to me that sqlite is resolving the column name from the incorrect
scope in the sub-query?
I have observed this in v3.7.10, but have also tested it in v3.8.8.3.
Andy
