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 NULL (or 0 prior
to check-in [2677]). Here's why:
Joins group from left to right. The first join to be evaluated
is:
photos LEFT JOIN trees ON photos.kind=1 AND photos.refid=trees.id
This join results (logically) in a table where every row has a
column named "photos.kind" with a value of 1. This logical table
is then joined as follows:
<PREVIOUS> LEFT JOIN pots ON photos.kind=2 AND photos.refid=pots.id
In this second join, the photos.kind=2 condition can never be met
because every row in the result of the previous join has photos.kind==1.
Hence, the result set contains no rows. A COUNT() of a empty result
set gives NULL.
--
D. Richard Hipp <[EMAIL PROTECTED]>
