Bug #69268 LEFT JOIN plus GROUP BY incorrectly returns NULL
Submitted: 17 May 2013 14:51 Modified: 24 Jun 2013 8:29
Reporter: Scott Noyes Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.6 OS:Any
Assigned to: CPU Architecture:Any

[17 May 2013 14:51] Scott Noyes
Description:
A LEFT JOIN where the right-hand table has only a single row and therefore the results should be identical with or without a GROUP BY returns different values between 5.5 and 5.6.

5.5.30:
mysql> SELECT * FROM a LEFT JOIN b ON a.id = b.id GROUP BY a.id;
+----+------+
| id | id   |
+----+------+
|  1 |    1 |
|  2 | NULL |
+----+------+

5.6.11:
mysql> SELECT * FROM a LEFT JOIN b ON a.id = b.id GROUP BY a.id;
+----+------+
| id | id   |
+----+------+
|  1 | NULL |
|  2 | NULL |
+----+------+

How to repeat:
CREATE TABLE a (id INT PRIMARY KEY);
INSERT INTO a VALUES (1),(2);
CREATE TABLE b (id INT PRIMARY KEY);
INSERT INTO b VALUES (1);

SELECT * FROM a LEFT JOIN b ON a.id = b.id; -- correct in 5.5 and 5.6
SELECT * FROM a LEFT JOIN b ON a.id = b.id GROUP BY a.id; -- correct in 5.5, incorrect in 5.6

Suggested fix:
Incorrect results are not seen in 5.6 if either table is created without the primary key, or if the second row is not inserted into table `a`.
[17 May 2013 16:36] Claudio Nanni
Confirmed on: 5.6.10 and 5.6.11

And also with a slightly different case.

 (I) table 'c' has a plain key, not unique and not primary.
(II) table 'c' contains 4 rows, not just one. 

CREATE TABLE a (id INT PRIMARY KEY);
INSERT INTO a VALUES (1),(2),(3),(4),(5);
CREATE TABLE c (id INT, KEY(id));
INSERT INTO c VALUES (1),(1),(3),(3);
SELECT * FROM a LEFT JOIN c ON a.id = c.id GROUP BY a.id;

mysql> SELECT * FROM a LEFT JOIN c ON a.id = c.id GROUP BY a.id;
+----+------+
| id | id   |
+----+------+
|  1 | NULL |
|  2 | NULL |
|  3 | NULL |
|  4 | NULL |
|  5 | NULL |
+----+------+
5 rows in set (0.00 sec)

While on 5.5.30:

mysql> SELECT * FROM a LEFT JOIN c ON a.id = c.id GROUP BY a.id;
+----+------+
| id | id   |
+----+------+
|  1 |    1 |
|  2 | NULL |
|  3 |    3 |
|  4 | NULL |
|  5 | NULL |
+----+------+
5 rows in set (0.00 sec)
[19 May 2013 14:05] Staffan Enberg
Here is another example.

http://sqlfiddle.com/#!9/fe178/1

If you switch MySQL version in the menu to 5.6.x the values becomes incorrect.
[19 May 2013 14:08] Staffan Enberg
My previous post was wrong, that example shows a working setup. If you add a key like this example, it stops working.

http://sqlfiddle.com/#!9/3d454/1

Note that this example works correctly if you select an earlier MySQL version.
[24 Jun 2013 8:29] Roy Lyseng
Duplicate of bug 68897