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:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7/8.0 OS:Mac OS X (10.13.3)
Assigned to: CPU Architecture:x86
Tags: grouping, subquery

[28 Mar 2018 15:23] Michał Konarski
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.
[29 Mar 2018 2:32] Miguel Solorzano
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] Miguel Solorzano
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)