On 11/17/16, Marc L. Allen <mlal...@outsitenetworks.com> 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:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Richard Hipp > Sent: Thursday, November 17, 2016 9:32 AM > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy > when '=' condition gives a strong hint > > On 11/17/16, Marc L. Allen <mlal...@outsitenetworks.com> 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 > d...@sqlite.org > _______________________________________________ > 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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
