>-----Ursprüngliche Nachricht----- >Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im >Auftrag von Keith Medcalf >On Wednesday, 2 January, 2019 16:58, Jonathan Moules ><jonathan-li...@lightpear.com<mailto:jonathan-li...@lightpear.com>> wrote:
> >>Gah, sorry. Another typo. I really should be more awake when I post to >>this list. The non-simplified code does have the item_id on the >>subquery (otherwise it simply wouldn't execute at all of course). So: >> >>SELECT * >> FROM item_info >> JOIN ( >> select >> count(1) as num, >> item_id >> from users >> group by item_id) >> USING (item_id) >> where item_id = ?; > >Now perhaps we are getting somewhere. So now what exactly is your complaint? > He first asked for (1) "compute all the totals and then select the one matching the item I provide" (table JOIN covering index full scan) He later asked for (2) "compute the total for item#1 and then select it if it matches item#2" (table JOIN partial index scan), which is faster and returns the correct value only if item#1 == item#2 at run time. He wanted (3) "select the item I provide and then compute a total for it" (select correlated subquery) His complaint is that SQLite rightly declines to transform (1) into (3), instead of doing what he asked for and not what he wanted. ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH<http://www.scigames.at> | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
