MySQL Bugs: #69268: LEFT JOIN plus GROUP BY incorrectly returns NULL

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

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`.

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)

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.

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.

Duplicate of bug 68897