On 03/03/2015 03:57 PM, Andy Gibbs wrote: > 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.
I think it's correct, no? Since there is no column "pid" to match against in the sub-query, the "pid" within the sub-query refers to "a.pid" from the outer query. http://en.wikipedia.org/wiki/Correlated_subquery
