MySQL Bugs: #99587: Invalid data returned when using LEFT OUTER JOIN and GROUP BY

Bug #99587	Invalid data returned when using LEFT OUTER JOIN and GROUP BY
Submitted:	15 May 2020 11:38	Modified:	18 May 2020 4:45
Reporter:	Florian Reinhart	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	8.0.20	OS:	Debian
Assigned to:		CPU Architecture:	x86

Description:
8.0.20 introduced a regression. When joining two tables and using a group by statement wrong data is returned.

I verified the issue with the official 8.0.20 Docker image. I am unable to reproduce the issue with the 8.0.19 Docker image.

How to repeat:
# Create database
# ------------------------------------------------------------

DROP DATABASE IF EXISTS `test`;

CREATE DATABASE test;

USE test;

# Create tables
# ------------------------------------------------------------

CREATE TABLE `Department` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `Person` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `department` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

# Insert data
# ------------------------------------------------------------

INSERT INTO `Department` (`id`, `name`)
VALUES
	(1, 'Department A'),
	(2, 'Department B');

INSERT INTO `Person` (`id`, `name`, `department`)
VALUES
	(1, 'Person A', 1),
	(2, 'Person B', 2),
	(3, 'Person C', NULL),
	(4, 'Person D', 1);

# Run queries
# ------------------------------------------------------------

SELECT P.id, P.name, P.department, D.name AS departmentName
FROM Person P LEFT OUTER JOIN Department D ON P.department = D.id;

# Result:
# 1	Person A	1	Department A
# 2	Person B	2	Department B
# 3	Person C	NULL	NULL
# 4	Person D	1	Department A

SELECT P.id, P.name, P.department, D.name AS departmentName
FROM Person P LEFT OUTER JOIN Department D ON P.department = D.id GROUP BY P.id;

# Result:
# 1	Person A	1	Department A
# 2	Person B	2	NULL
# 3	Person C	NULL	Department B
# 4	Person D	1	Department A

Hi Mr. Reinhart,

Thank you for your bug report.

I have run your test case and managed to repeat the same results:

id	name	department	departmentName
1	Person A	1	Department A
2	Person B	2	Department B
3	Person C	NULL	NULL
4	Person D	1	Department A

id	name	department	departmentName
1	Person A	1	Department A
2	Person B	2	NULL
3	Person C	NULL	Department B
4	Person D	1	Department A

Hence, this is a regression bug in 8.0.20, which is why it deserves the severity that you reported.

Verified as reported.

Duplicate of

Bug#99398 Data in wrong row on left join

which has been fixed in the upcoming 8.0.21 release.

Thank you, Erlend.