On Sunday, 17 March, 2019 15:00, Yannick Duchêne <yannick_duch...@yahoo.fr> wrote:
>Aside, what also surprised me just a moment ago and I never >noticed before, is that although I can refer to ROWID (not aliased) >anywhere in a query, it seems to not work properly in `using(rowid)`, if >the ROWID is automatically generated in one of the table (ex. an >ordinary table with no “without rowid”) and it is not in the other table (ex. a column named ROWID in a VIEW). >Here an example: > > create table test (a int, b int); > > insert into test (a, b) values (1, 2); > insert into test (a, b) values (3, 4); > insert into test (a, b) values (5, 6); > insert into test (a, b) values (7, 8); > > create view testview (rowid, sum) as select rowid, a + b from test; > select rowid from test; -- OK, the column exists. Well, no, the column does not exist. Read the "create table" statement, there is no column named rowid (nor is there a named "integer primary key" for which "rowid" might be an alias. You are retrieving the "rowid", not a column named "rowid". > select rowid from testview; -- OK, the column exists. The name now exists because the view assigns the column name "rowid" to the ephemeral "rowid" in the underlying table. > select testview.sum from testview, test using (rowid) where test.a <= 3; > -- Fails. > > -- SQLite complains: “cannot join using column rowid - column not present > in both tables”. That is correct. "rowid" is not a column name in the table test. It is, however, a column name in the view. sqlite> create view test2 as select rowid, a, b from test; sqlite> select testview.sum from testview, test2 USING (rowid) where test2.a <= 3; 3 7 Similarly, rowid is a column name is the view test2. a JOIN b USING (name) requires that name be a column name in a and b. Just because you can ask for a "rowid" does not mean that there is a column called "rowid" in the table ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
