Strange results from a query

[David Griffiths]

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?
Here are the table defs:
create table master (col1 int not null);
create table sub (col1 int not null, a char(1) null, b char(1) null, c 
smallint);

insert into master (col1) values (1), (2), (3);
Thanks, David
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]