In both mysql 4.0.20 and 4.1.4a-gamma, I get
mysql> CREATE TABLE master (col1 INT NOT NULL); Query OK, 0 rows affected (0.09 sec)
mysql> CREATE TABLE sub (col1 INT NOT NULL, a CHAR(1) NULL, b CHAR(1) NULL, c SMALLINT);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO MASTER (col1) VALUES (1), (2); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO sub (col1, a, b, c) VALUES
-> (1, 'a', null, '2'),
-> (1, 'a', null, '2'),
-> (2, null, 'b', '3'),
-> (2, null, 'b', '3');
Query OK, 4 rows affected (0.07 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql> 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 | 4 | NULL |
| 2 | NULL | 6 |
+------+---------+---------+
2 rows in set (0.07 sec)which is the correct result, just as Stephen did with 3.23.58. If I then
INSERT INTO MASTER (col1) VALUES (1), (2);
a second time, I then get
+------+---------+---------+ | col1 | A-count | B-count | +------+---------+---------+ | 1 | 8 | NULL | | 2 | NULL | 12 | +------+---------+---------+ 2 rows in set (0.07 sec)
I think you should double-check your data. In your original email, you listed two inserts to table master, then said you'd "removed some data to make it clearer" in your follow-up. I suspect your master table has 2 copies of each row.
Michael
David Griffiths wrote:
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]
