> On Saturday, 4 January, 2020 18:31, Amer Neely <nos...@softouch.on.ca> > wrote: > >> I'm fairly new to SQLite, but have been using MySQL / mariadb in a local >> and web-based environment for several years. So far I'm happy and >> impressed with SQLite, but I recently noticed some odd behaviour with >> one of my queries. >> Using the command-line in a shell (Mac High Sierra) I get a particular >> result from a query. The exact same query in a Perl script gives me a >> different result. To my mind it is a simple query, getting the 5 latest >> additions to my music library. >> Command-line: >> <code>select artists.artist, artists.artistid, cds.title, cds.artistid, >> cds.cdid, genres.genre, genres.artistid from artists, genres inner join >> cds using (artistid) group by artists.artistid order by cds.id desc >> limit 5;</code> >> gives me the correct result. However, in a Perl script it gives me a >> different result. How is that possible? Could it be a Perl::DBI issue? >> Many thanks for anyone able to shed some light on this. > > Your select does not constrain artists so the result is non-deterministic > in that the result will depend on how the query planner decides to > execute the query. That is, you have not specified any join constraints > on artists. > > SELECT * FROM A, B JOIN C USING (D); > > means > > SELECT * > FROM A, B, C > WHERE B.D == C.D; > > if you thought it meant > > SELECT * > FROM A, B, C > WHERE A.D == B.D > AND B.D == C.D; > > then that is likely the reason for the discrepancy. > > Thank you for your time and consideration. I have managed to 'solve' this problem by constraining on artists.artistid by grouping on that. But the question still remains, why the different results?
-- Amer Neely _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
