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.
