Bug #90245 | Wrong result when using GROUP BY and const value in a subquery | ||
---|---|---|---|
Submitted: | 28 Mar 2018 15:23 | Modified: | 29 Mar 2018 2:38 |
Reporter: | Michał Konarski | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
Version: | 5.7/8.0 | OS: | MacOS (10.13.3) |
Assigned to: | CPU Architecture: | x86 | |
Tags: | grouping, subquery |
[28 Mar 2018 15:23]
Michał Konarski
[29 Mar 2018 2:32]
MySQL Verification Team
C:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --prompt="mysql 5.7 > " Enter password: ****** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.23-log Source distribution 2018-MAR-08 Copyright (c) 2000, 2018, 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 5.7 > CREATE DATABASE IF NOT EXISTS `join_example`; Query OK, 1 row affected (0.01 sec) mysql 5.7 > USE join_example; Database changed mysql 5.7 > mysql 5.7 > DROP TABLE IF EXISTS `table_a`; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql 5.7 > CREATE TABLE `table_a` ( -> `id` int(11) NOT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.05 sec) mysql 5.7 > mysql 5.7 > DROP TABLE IF EXISTS `table_b`; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql 5.7 > CREATE TABLE `table_b` ( -> `id` int(11) NOT NULL, -> `table_a_id` int(11) DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.04 sec) mysql 5.7 > mysql 5.7 > INSERT INTO `join_example`.`table_a`(`id`) VALUES (1); Query OK, 1 row affected (0.01 sec) mysql 5.7 > INSERT INTO `join_example`.`table_b`(`id`,`table_a_id`) VALUES (1, 1); Query OK, 1 row affected (0.01 sec) mysql 5.7 > mysql 5.7 > SELECT -> max(table_a.id), s.source_name -> FROM -> table_a -> LEFT JOIN -> (SELECT -> 'sample_const' AS source_name, table_a_id -> FROM -> table_b) s ON table_a.id = s.table_a_id -> GROUP BY s.source_name; +-----------------+--------------+ | max(table_a.id) | source_name | +-----------------+--------------+ | 1 | | +-----------------+--------------+ 1 row in set (0.01 sec) ----------------------------------------------------------------------------------- Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.41-log Source distribution 2018-MAR-08 Copyright (c) 2000, 2018, 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 5.6 > CREATE DATABASE IF NOT EXISTS `join_example`; Query OK, 1 row affected (0.01 sec) mysql 5.6 > USE join_example; Database changed mysql 5.6 > mysql 5.6 > DROP TABLE IF EXISTS `table_a`; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql 5.6 > CREATE TABLE `table_a` ( -> `id` int(11) NOT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.05 sec) mysql 5.6 > mysql 5.6 > DROP TABLE IF EXISTS `table_b`; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql 5.6 > CREATE TABLE `table_b` ( -> `id` int(11) NOT NULL, -> `table_a_id` int(11) DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.03 sec) mysql 5.6 > mysql 5.6 > INSERT INTO `join_example`.`table_a`(`id`) VALUES (1); Query OK, 1 row affected (0.00 sec) mysql 5.6 > INSERT INTO `join_example`.`table_b`(`id`,`table_a_id`) VALUES (1, 1); Query OK, 1 row affected (0.00 sec) mysql 5.6 > mysql 5.6 > SELECT -> max(table_a.id), s.source_name -> FROM -> table_a -> LEFT JOIN -> (SELECT -> 'sample_const' AS source_name, table_a_id -> FROM -> table_b) s ON table_a.id = s.table_a_id -> GROUP BY s.source_name; +-----------------+--------------+ | max(table_a.id) | source_name | +-----------------+--------------+ | 1 | sample_const | +-----------------+--------------+ 1 row in set (0.00 sec) -----------------------------------------------------------------------------
[29 Mar 2018 2:38]
MySQL Verification Team
Thank you for the bug report. mysql 8.0 > mysql 8.0 > DROP TABLE IF EXISTS `table_a`; max(table_a.id), s.source_name FROM table_a LEFT JOIN (SELECT Query OK, 0 rows affected, 1 warning (0,08 sec) mysql 8.0 > CREATE TABLE `table_a` ( -> `id` int(11) NOT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 'sample_const' AS source_name, table_a_id FROM table_b) s ON table_a.id = s.table_a_id GROUP BY s.source_name;Query OK, 0 rows affected (0,52 sec) mysql 8.0 > mysql 8.0 > DROP TABLE IF EXISTS `table_b`; Query OK, 0 rows affected, 1 warning (0,11 sec) mysql 8.0 > CREATE TABLE `table_b` ( -> `id` int(11) NOT NULL, -> `table_a_id` int(11) DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0,56 sec) mysql 8.0 > mysql 8.0 > INSERT INTO `join_example`.`table_a`(`id`) VALUES (1); Query OK, 1 row affected (0,12 sec) mysql 8.0 > INSERT INTO `join_example`.`table_b`(`id`,`table_a_id`) VALUES (1, 1); Query OK, 1 row affected (0,12 sec) mysql 8.0 > mysql 8.0 > SELECT -> max(table_a.id), s.source_name -> FROM -> table_a -> LEFT JOIN -> (SELECT -> 'sample_const' AS source_name, table_a_id -> FROM -> table_b) s ON table_a.id = s.table_a_id -> GROUP BY s.source_name; +-----------------+-------------+ | max(table_a.id) | source_name | +-----------------+-------------+ | 1 | | +-----------------+-------------+ 1 row in set (0,00 sec) mysql 8.0 > show variables like "%version%"; +-------------------------+---------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------+ | innodb_version | 8.0.12 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | TLSv1,TLSv1.1,TLSv1.2 | | version | 8.0.12 | | version_comment | Source distribution 2018-MAR-25 | | version_compile_machine | x86_64 | | version_compile_os | Linux | | version_compile_zlib | 1.2.11 | +-------------------------+---------------------------------+ 9 rows in set (0,01 sec)