On 5 Jan 2020, at 5:01pm, Amer Neely <nos...@softouch.on.ca> 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

Reply via email to