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

Reply via email to