Is this error expected?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Is this error expected?

Tony Papadimitriou
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is this error expected?

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-
>[hidden email]] 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is this error expected?

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-
>[hidden email]] 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is this error expected?

Tony Papadimitriou
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-
>[hidden email]] 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is this error expected?

Tony Papadimitriou
Strike the last, my bad.  It works correctly.  I forgot to define the id
part.  So, just inconsistent.

select * from (values(1,12345)) join (values(1,54321)) using(column1);

-----Original Message-----
From: Tony Papadimitriou
Sent: Sunday, November 12, 2017 10:37 PM
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-
>[hidden email]] 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is this error expected?

Keith Medcalf
In reply to this post by Tony Papadimitriou

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-
>[hidden email]] 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-
>>[hidden email]] 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
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users