MySQL Bugs: #99846: Non-deterministic query using GROUP BY

Bug #99846	Non-deterministic query using GROUP BY
Submitted:	11 Jun 2020 8:50	Modified:	11 Jun 2020 10:45
Reporter:	Alexandre Hocquard	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.20	OS:	Debian
Assigned to:		CPU Architecture:	x86
Tags:	GROUP_BY, Mixing, non-deterministic

Description:
Hello,

With Mysql 8.0.20, there is an unexpected behavior with GROUP BY.
It mixes the values between the tuples.

Worst, the query is not returning the same when executed two times in a row.

How to repeat:
-- Launch Mysql (with docker here):

docker run --name mysql-bug -e MYSQL_ROOT_PASSWORD=root -d mysql:8.0.20

docker exec -i mysql-bug mysql -uroot -proot mysql

-- Create the schema:

DROP TABLE IF EXISTS child;
DROP table IF EXISTS parent;

CREATE TABLE `parent` (
  `id` int NOT NULL AUTO_INCREMENT,
  `code` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_code_parent` (`code`)
);

CREATE TABLE `child` (
  `id` int NOT NULL AUTO_INCREMENT,
  `code` varchar(255) NOT NULL,
  `parent_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_code_child` (`code`),
  KEY `parent_key` (`parent_id`),
  CONSTRAINT `fk_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE
);

-- insert the data

INSERT INTO parent (id, code) VALUES (1, 'parent_code_1');
INSERT INTO child (id, code, parent_id) VALUES (1, 'child_code_1', null), (2, 'child_code_2', 1);

-- Execute the query
-- parent_code for child_code_1 should be NULL

SELECT child.id, child.code, parent.code AS parent_code
FROM child
LEFT JOIN parent ON parent.id = child.parent_id
GROUP BY child.id;

+----+--------------+---------------+
| id | code         | parent_code   |
+----+--------------+---------------+
|  1 | child_code_1 | parent_code_1 |
|  2 | child_code_2 | parent_code_1 |
+----+--------------+---------------+

-- launch a new client to have a new connection

docker exec -i mysql-bug mysql -uroot -proot mysql

-- request with this new connection
-- empty string parent_code instead of NULL

SELECT child.id, child.code, parent.code AS parent_code
FROM child
LEFT JOIN parent ON parent.id = child.parent_id
GROUP BY child.id;

+----+--------------+---------------+
| id | code         | parent_code   |
+----+--------------+---------------+
|  1 | child_code_1 |               |
|  2 | child_code_2 | parent_code_1 |
+----+--------------+---------------+

-- request a second time with this new connection

+----+--------------+---------------+
| id | code         | parent_code   |
+----+--------------+---------------+
|  1 | child_code_1 | parent_code_1 |
|  2 | child_code_2 | parent_code_1 |
+----+--------------+---------------+

Suggested fix:
If it can give a hint, when dropping the unique key constraint in table "parent" on the column "code', it works as expected.

Thank you for the bug report. This was already fixed on most recent source build server.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21 Source distribution BUILT: 2020-MAY-31

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 8.0 > CREATE DATABASE ppp;
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > USE ppp
Database changed
mysql 8.0 >
mysql 8.0 > CREATE TABLE `parent` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   `code` varchar(255) NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `uniq_code_parent` (`code`)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql 8.0 > CREATE TABLE `child` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   `code` varchar(255) NOT NULL,
    ->   `parent_id` int DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `uniq_code_child` (`code`),
    ->   KEY `parent_key` (`parent_id`),
    ->   CONSTRAINT `fk_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql 8.0 > INSERT INTO parent (id, code) VALUES (1, 'parent_code_1');
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > INSERT INTO child (id, code, parent_id) VALUES (1, 'child_code_1', null), (2, 'child_code_2', 1);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 8.0 > SELECT child.id, child.code, parent.code AS parent_code
    -> FROM child
    -> LEFT JOIN parent ON parent.id = child.parent_id
    -> GROUP BY child.id;
+----+--------------+---------------+
| id | code         | parent_code   |
+----+--------------+---------------+
|  1 | child_code_1 | NULL          |
|  2 | child_code_2 | parent_code_1 |
+----+--------------+---------------+
2 rows in set (0.00 sec)

mysql 8.0 >

2ND CONNECTION:

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.21 Source distribution BUILT: 2020-MAY-31

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 8.0 > USE ppp
Database changed
mysql 8.0 > SELECT child.id, child.code, parent.code AS parent_code
    -> FROM child
    -> LEFT JOIN parent ON parent.id = child.parent_id
    -> GROUP BY child.id;
+----+--------------+---------------+
| id | code         | parent_code   |
+----+--------------+---------------+
|  1 | child_code_1 | NULL          |
|  2 | child_code_2 | parent_code_1 |
+----+--------------+---------------+
2 rows in set (0.00 sec)

mysql 8.0 > SELECT child.id, child.code, parent.code AS parent_code
    -> FROM child
    -> LEFT JOIN parent ON parent.id = child.parent_id
    -> GROUP BY child.id;
+----+--------------+---------------+
| id | code         | parent_code   |
+----+--------------+---------------+
|  1 | child_code_1 | NULL          |
|  2 | child_code_2 | parent_code_1 |
+----+--------------+---------------+
2 rows in set (0.00 sec)

mysql 8.0 >