On 2/29/16, Jo?o Ramos <joao.sigma at gmail.com> wrote: > 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
Compiling with that exact same version of the source code, it works fine for me: drh at bella:~/sqlite/bld$ cat x1.txt SELECT sqlite_version(); SELECT sqlite_source_id(); 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); drh at bella:~/sqlite/bld$ ./sqlite3 <x1.txt 3.8.10 2015-05-04 19:13:25 850c11866686a7b39d7b163fb60898c11283688e Error: near line 3: no such column: tB.id drh at bella:~/sqlite/bld$ > > > 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* > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
