Hrm. Inconsistent/incorrect results. Consider: create table a(id integer primary key, a); insert into a values (1,1), (2,1), (3,1); create table b(id integer primary key, b); insert into b values (1,2), (3,2), (4,2); create table c(id integer primary key, c); insert into c values (1,3), (4,3), (5,3);
select * from a, b, c using (id); -- very strange result id a id b c ---------- ---------- ---------- ---------- ---------- 1 1 1 2 3 1 1 3 2 3 1 1 4 2 3 select * from a, b using (id), c using (id); -- correct result id a b c ---------- ---------- ---------- ---------- 1 1 2 3 The first query should be processed as: select * from a, b, c where b.id == c.id; id a id b id c ---------- ---------- ---------- ---------- ---------- ---------- 1 1 1 2 1 3 2 1 1 2 1 3 3 1 1 2 1 3 1 1 4 2 4 3 2 1 4 2 4 3 3 1 4 2 4 3 but with the c.id (third id column omitted). Or it should be processed as the second query if the "using (id)" constraint applies to everywhere an "id" field is found, not just the LHS and RHS tables of the immediately proceeding join. also select * from a natural join b natural join c; -- returns no rows despite the column "id" existing commonly in all tables This is with the current development release (and as far as I can tell, all prior versions). -- 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 <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of Keith Medcalf >Sent: Saturday, 4 January, 2020 19:32 >To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >Subject: Re: [sqlite] SQLite command-line result is different from Perl >DBI::Sqlite result > > > > > >-- >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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
