It's actually even simpler to show this (without CTE defining a rowid):
create table t1(v); insert into t1 values(12345);
create table t2(v); insert into t2 values(54321);
select * from t1 join t2 using(rowid); -- THIS ONE FAILS
select * from t1 join t2 on t1.rowid = t2.rowid; -- THIS ONE WORKS
I understand what you say and it does seem to work as you described but I'm
not sure I can agree this is how it *should* work.
The fact that rowid is an implicit column should not matter, IMO.
If both t1.rowid and t2.rowid (being implicitly defined columns) can be
found, they should (IMO) also be found by USING as these are logically
equivalent.
The only difference of the two forms is that the result of a SELECT * will
include this column once with USING, and twice with ON ... = ...
Thank you for your response.
-----Original Message-----
From: Keith Medcalf
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
