--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
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.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
