Bug #14006 GROUP BY doesn't work
Submitted: 13 Oct 2005 14:46 Modified: 19 Oct 2005 7:42
Reporter: Alexey Botchkov Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0 OS:Linux (Linux)
Assigned to: Alexey Botchkov CPU Architecture:Any

[13 Oct 2005 14:46] Alexey Botchkov
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