Unable to reproduce. Are you using an old version of SQLite3 from before the handling of values clause column names was fixed months ago?
sqlite> select * from (values(12345)) join (values(54321)) using(column1); sqlite> select * from (values(12345)) join (values(12345)) using(column1); 12345 --- 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 13:38 >To: SQLite mailing list >Subject: Re: [sqlite] Is this error expected? > >Now the problem seems to affect implicit column definitions, I tried >the >following: > >select * from (values(12345)) join (values(54321)) using(column1); > >Does not produce any output (incorrectly IMO), but it also does not >give any >error like the rowid case (inconsistent -- if we accept the error is >the >correct approach). > >-----Original Message----- >From: Tony Papadimitriou > >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 > >_______________________________________________ >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
