On Mon, 2005-09-19 at 15:19 -0400, D. Richard Hipp wrote: > On Mon, 2005-09-19 at 19:36 +0200, Alain Bertrand wrote: > > hi all, > > > > I am porting a program from mysql to sqlite. > > The following statement doesn't work correctly with sqlite though it does > > with mysql. > > SELECT COUNT(*) AS nb FROM ttd_photos LEFT JOIN ttd_trees ON > > ttd_photos.kind=1 AND ttd_photos.refId=ttd_trees.treeId LEFT JOIN ttd_pots > > ON ttd_photos.kind=2 AND ttd_photos.refId=ttd_pots.potId > > > > I believe the query above should always return 0...
I've changed my mind. I think instead that there is a bug in SQLite that caused LEFT JOINs to be computed incorrectly if one of the terms in the ON clause restricts only the left table in the join. Check-in [2725] at http://www.sqlite.org/cvstrac/chngview?cn=2725 contains a fix for this problem for version 3.x. The problem has existed in SQLite forever (because it originates from a conceptual misunderstanding by the code author :-)) so version 2.8.16 is still broken. Because the problem is obscure, I am not inclined to fix it in the 2.8.x series... -- D. Richard Hipp <[EMAIL PROTECTED]>
