Description:
GROUP BY with the LEFT JOIN doesn't always work
How to repeat:
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, UNIQUE idx (a,b) );
INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4);
CREATE TABLE t2 ( a INT NOT NULL, b INT NOT NULL, c INT );
INSERT INTO t2 VALUES ( 1,10,1), (1,10,2), (1,11,1), (1,11,2), (1,2,1), (1,2,2),
(1,2,3);
SELECT t2.a, t2.b, IF(t1.b IS NULL,'',c) AS c, COUNT(*) AS d FROM t2 LEFT JOIN
t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c;
You'll get
+---+----+------+---+
| a | b | c | d |
+---+----+------+---+
| 1 | 2 | 1 | 1 |
| 1 | 2 | 2 | 1 |
| 1 | 2 | 3 | 1 |
| 1 | 10 | | 1 |
| 1 | 10 | | 1 |
| 1 | 11 | | 1 |
| 1 | 11 | | 1 |
+---+----+------+---+
Last two couples of lines should be in the group IMHO