| 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 | ||
[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)
Description: Query returns a result column with only \0 characters when using LEFT JOIN, GROUP BY and const value in a subquery. This problem can be reproduced on mysql 5.7.21. Version 5.7.20 is not affected. I've found this bug on MacOS, but the same thing happens in Linux docker containers. I'm using mysql console client. How to repeat: CREATE DATABASE IF NOT EXISTS `join_example`; USE join_example; DROP TABLE IF EXISTS `table_a`; CREATE TABLE `table_a` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; DROP TABLE IF EXISTS `table_b`; CREATE TABLE `table_b` ( `id` int(11) NOT NULL, `table_a_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `join_example`.`table_a`(`id`) VALUES (1); INSERT INTO `join_example`.`table_b`(`id`,`table_a_id`) VALUES (1, 1); 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; This query should return: +-----------------+--------------+ | max(table_a.id) | source_name | +-----------------+--------------+ | 1 | sample_const | +-----------------+--------------+ Instead it returns: +-----------------+--------------+ | max(table_a.id) | source_name | +-----------------+--------------+ | 1 | | +-----------------+--------------+ source_name value is a sequence of \0 characters with a length matching the length of 'sample_const' string. The result is correct when I remove GROUP BY or replace LEFT JOIN with a simple JOIN.