When you are executing the query:
SELECT *
FROM item_info
JOIN (select count(1)
from users
group by item_id)
USING (item_id)
where item_id = ?;
You are telling the SQL Database Engine (whatever it may happen to be, in this
case SQLite3) that you want to take the table produced by:
select * from item_info;
and join it (an equijoin) against the table produced by running the query:
select count(1) from users group by item_id;
based on the equality of the column item_id in both tables. If you run the two
queries you will see that the second table DOES NOT produce a column called
item_id. Therefore you cannot join those tables and instead you get an error
message telling you that the column item_id does not exist in both tables.
FROM tables are siblings not correlates.
---
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-
>[email protected]] On Behalf Of Jonathan Moules
>Sent: Wednesday, 2 January, 2019 15:21
>To: [email protected]
>Subject: Re: [sqlite] Optimisation opportunity on subquery?
>
>Hi Simon,
>
>Thanks for that - I'm always trying to improve my SQL. I think I see
>what you mean now.
>
>Assuming my original query:
>
> SELECT
> *
> FROM
> item_info
> JOIN (select count(1) from users group by item_id)
> USING (item_id)
> where item_id = ?;
>
>There are three uses of "item_id".
>
>* The first is the "group by", which unless my SQL is even worse than
>I
>imagine, can only be referencing the users table.
>
>* The second is "USING" - which is referencing both.
>
>* The third is the one I guess you mean is ambiguous? My thinking was
>that because item_id is going through the USING it wasn't ambiguous
>as
>they're the same thing; though that's apparently not how the query
>planner sees it, and hence your reference to ambiguity. That right?
>
>So I tried using aliases (I'm assuming that removes the ambiguity),
>but
>the query times remained at about 0.5s for both versions (whether
>i.item_id or u.item_id):
>
> SELECT
> *
> FROM
> item_info i
> JOIN (select count(1) from users group by item_id) u
> USING (item_id)
> where u.item_id = ?;
>
>Thanks again for clarifying, but after checking, it doesn't seem like
>it
>was an ambiguity thing.
>Cheers,
>Jonathan
>
>On 2019-01-02 22:04, Simon Slavin wrote:
>> On 2 Jan 2019, at 9:50pm, Jonathan Moules <jonathan-
>[email protected]> wrote:
>>
>>> Sorry, but which column is ambiguous? The users.item_id is a
>foreign key to the item_info.item_id - that's why it's a "REFERENCES"
>- why would I want to change it to be something else? Isn't the
>convention for FK's to have the same name across tables? That's what
>"USING" is for right? (or NATURAL, but I prefer to be explicit.)
>Happy to be corrected.
>> It may be that our careers developed with different ideas about how
>to use SQL. You had a JOIN, both tables had a column "item_id", and
>a reference inside the JOIN to "item_id" would be ambiguous. Since
>the SQL standard does not make it clear which table would be used, it
>would be possible for different implementations of SQL to think you
>meant different tables.
>>
>> I understand that, in your example, the values would be the same.
>But that doesn't explain to you what the optimizer thinks you're
>trying to do. The simplest way to tell the optimizer what you need
>would be to rename one of the columns. You could try both tables,
>see which solution was faster, and use that one.
>>
>> However, I see other posters have gained better clarity for your
>problem.
>>
>> Simon.
>> _______________________________________________
>> sqlite-users mailing list
>> [email protected]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
