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: | |
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 |
[11 Jun 2020 8:50]
Alexandre Hocquard
[11 Jun 2020 10:45]
MySQL Verification Team
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 >