<sigh> Yeah.. I know that. Missed the PRIMARY KEY. :( Back to lurking. ;)
-----Original Message----- From: sqlite-users [mailto:[email protected]] On Behalf Of Richard Hipp Sent: Thursday, November 17, 2016 9:54 AM To: SQLite mailing list <[email protected]> Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint On 11/17/16, Marc L. Allen <[email protected]> wrote: > I'm not sure that's a valid trick, as bar.foo can be NULL, in which > case the LEFT join still returns it, but an INNER join does not. > Unless sqlite infers a NOT NULL on bar.foo? The bar.foo column is an INTEGER PRIMARY KEY, which can never be NULL. Standard SQL requires an implied NOT NULL on all PRIMARY KEY columns. But due to a coding error, early versions of SQLite did not enforce that, and so we have taken care not to enforce it on all subsequent versions of SQLite to preserve backwards compatibility. WITHOUT ROWID tables were added later, and so NOT NULL is properly enforced on all PRIMARY KEY columns in WITHOUT ROWID tables. > > -----Original Message----- > From: sqlite-users > [mailto:[email protected]] On Behalf Of > Richard Hipp > Sent: Thursday, November 17, 2016 9:32 AM > To: SQLite mailing list <[email protected]> > Subject: Re: [sqlite] Query Planner fails to recognise efficient > strategy when '=' condition gives a strong hint > > On 11/17/16, Marc L. Allen <[email protected]> wrote: >> Maybe I'm missing something, but... >> >> ORDER BY id >> >> Is ordering by the ID the right-hand side of a LEFT join. As such, >> it depends on how NULL factors into an ORDER BY. If NULL comes >> first, it has to find enough records where the LEFT join fails. >> >> Yeah.. I'm probably missing something. > > No, you are correct. Since the ID column is on the RHS of a LEFT > JOIN, it cannot use an index for sorting. > > The foreign key constraint could, in theory, be used by the query > planner to simplify the LEFT JOIN into an ordinary INNER JOIN. But > the query planner in SQLite does not currently know that trick. > > So, one solution is to remove the LEFT keyword from the query in the > application.... > -- > D. Richard Hipp > [email protected] > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
