Maybe this has been fixed then? This is what I'm getting: select sqlite_version(); -- 3.8.10
select sqlite_source_id(); -- 2015-05-04 19:13:25 850c11866686a7b39d7b163fb60898c11283688e WITH tA(id, name) AS ( SELECT 1, "a" UNION ALL SELECT 2, "b" ), tB(name) AS ( SELECT "a" UNION ALL SELECT "b" ) SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name); -- 1 -- 2 On Fri, Feb 26, 2016 at 6:19 PM, Dan Kennedy <danielk1977 at gmail.com> wrote: > On 02/27/2016 12:49 AM, Jo?o Ramos wrote: > >> I'm using SQLite 3.8.10.2 and the following query illustrates the problem: >> >> WITH >> tA(id, name) AS >> ( >> SELECT 1, "a" UNION ALL SELECT 2, "b" >> ), >> tB(name) AS >> ( >> SELECT "a" UNION ALL SELECT "b" >> ) >> SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name); >> >> >> There is no _id_ column in the tB table, yet the statement doesn't produce >> any error and in fact will return the ids of table tA. This doesn't seem >> correct to me. >> >> >> > Cannot reproduce this problem here: > > dan at darkstar:~/work/sqlite/bld$ ./sqlite3 > SQLite version 3.8.10.2 2015-05-20 18:17:19 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> WITH tA(id, name) AS ( > ...> SELECT 1, "a" UNION ALL SELECT 2, "b" > ...> ), > ...> tB(name) AS ( > ...> SELECT "a" UNION ALL SELECT "b" > ...> ) > ...> SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name); > Error: no such column: tB.id > > > If you were using "oid", "rowid" or "_rowid_" instead of "id", then it > might look like it was returning the values from table tA. All views and > CTEs in SQLite have such columns for historical reasons, but the contents > of them is both undefined and unstable. Sometimes it's a sequence of > integers starting at 1. > > Dan. > > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- *Jo?o Ramos*
