FYI, I ran the same scenario under MySQL 3.23.58 and it produced the results you wanted/expected:
+------+---------+---------+ | col1 | A-count | B-count | +------+---------+---------+ | 1 | 4 | 0 | | 2 | 0 | 6 | +------+---------+---------+ So could it be a bug in 4.0.18? - seb --- David Griffiths <[EMAIL PROTECTED]> wrote: >One of our developers came to me yesterday with strange results from a >query. I've created a simple version of the example. I've pasted the >table definitions at the bottom if someone really needs to see them. >This is on mysql 4.0.18. > >insert into master (col1) values (1), (2); > >insert into sub (col1, a, b, c) values >(1, 'a', null, '2'), >(1, 'a', null, '2'), >(2, null, 'b', '3'), >(2, null, 'b', '3'); > > >mysql> select m.col1, > -> sum(s1.c) as 'A-count', > -> sum(s2.c) as 'B-count' > -> FROM master m > -> left join sub s1 on (m.col1 = s1.col1 AND s1.a is not null) > -> left join sub s2 on (m.col1 = s2.col1 and s2.b is not null) > -> group by m.col1; >+------+---------+---------+ >| col1 | A-count | B-count | >+------+---------+---------+ >| 1 | 8 | NULL | >| 2 | NULL | 12 | >+------+---------+---------+ >2 rows in set (0.00 sec) > >In case it's not obvious, the count for the column marked "A" should be >4, not 8. And for "B", it should be 6, not 12. The database seems to be >iterating through the table twice. > >If one of the outer-joins is removed, the results are correct. I would >hazard a guess that if a third column existed in master/sub, and a third >left-join was added, "A" would go to 12, and "B" would go to 16. Each >outer join seems to spawn a new iteration through the data. > >My question is "why", and what would be the strategy to avoid this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
