Not really. Table TAB does not contain a column named rowid. tab.rowid refers to the non-column representing the row number of a row in the table.
If you declared table TAB to actually have a column called rowid then it would work just fine, even if that column rowid still contained the row number of the row in the table. sqlite> create table tab(rowid integer primary key, val); sqlite> insert into tab(val) values(1); sqlite> with t(rowid,val) as ( ...> select rowid,val from tab ...> union ...> select rowid,tab.val ...> from tab join t using (rowid) ...> ) ...> select * from t; 1|1 However, in this case your (below) query will fail since you now have a column called "rowid" in each table, and you did not specify which one you wanted to select ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Tony Papadimitriou >Sent: Sunday, 12 November, 2017 11:21 >To: General Discussion of SQLite Database >Subject: [sqlite] Is this error expected? > >An example to reproduce a problem I noticed: > >------------------------------------------------ >create table tab(val); >insert into tab values(1); > >with t(rowid,val) as ( > select rowid,val from tab > union > select rowid,tab.val > from tab join t on t.rowid = tab.rowid > --from tab join t using(rowid) > ) >select * from t; >------------------------------------------------ > >If the 1st FROM is replaced by the 2nd commented out FROM ... USING >SQLite3 throws this error: > >Error: near line 4: cannot join using column rowid - column not >present in both tables > >Aren’t these two FROM clauses practically equivalent? > >(SQLite version 3.21.0 2017-10-24 18:55:49) >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
