Thanks, Stephen - that's interesting to know.
David
Stephen E. Bacher wrote:
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]
