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

Reply via email to