Re: [sqlite] SQLite command-line result is different from Perl DBI::Sqlite result (Keith Medcalf)
On 5 Jan 2020, at 5:01pm, Amer Neely wrote: > But the question still remains, why the different results? The optimizer gets improved from time to time in SQLite versions. SQLite solved the problem faster by breaking down your query differently: deciding whether to do one scan or use two indexes, which table to search first, etc.. Your query was appears to violate the SQL standard, by failing to define the JOIN. It should probably have resulted in a syntax error, but it didn't. And it just happened that one version of SQLite interpreted the query one way, another interpreted it another way. I advise you change your software to use one of the queries DRH supplied: > SELECT * FROM a, (b JOIN c USING(id)); > > SELECT * FROM (a,b) JOIN c USING (id); This will remove the ambiguity, meaning that however SQLite interprets the query in the future, or even if you switch to a different SQL engine, you get the result you wanted. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite command-line result is different from Perl DBI::Sqlite result (Keith Medcalf)
> On Saturday, 4 January, 2020 18:31, Amer Neely > 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: >> 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; >> 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
Re: [sqlite] SQLite command-line result is different from Perl DBI::Sqlite result (Igor Korot)
-- > What is your version of Perl and the SQLite module? This is perl 5, version 18, subversion 2 (v5.18.2) built for darwin-thread-multi-2level (with 2 registered patches, see perl -V for more detail) Copyright 1987-2013, Larry Wall SQLite version 3.30.1 2019-10-10 20:19:45 Enter ".help" for usage hints. -- Amer Neely ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 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: >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; >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
Re: [sqlite] SQLite command-line result is different from Perl DBI::Sqlite result
Hi, On Sat, Jan 4, 2020 at 7:31 PM Amer Neely wrote: > > Hello all, > 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: > 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; > 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. What is your version of Perl and the SQLite module? Thank you. > -- > Amer Neely > ___ > 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
[sqlite] SQLite command-line result is different from Perl DBI::Sqlite result
Hello all, 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: 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; 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. -- Amer Neely ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
