MySQL Bugs: #100268: 8.0.20: LEFT JOIN w/ unique index does not work properly w/ non-strict GROUP BY

Bug #100268	8.0.20: LEFT JOIN w/ unique index does not work properly w/ non-strict GROUP BY
Submitted:	20 Jul 2020 17:13	Modified:	20 Jul 2020 18:13
Reporter:	Ephi Blanshey	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	8.0.20	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
8.0.20 introduced a backwards-incompatible change for a LEFT JOIN clause on a table with a unique composite index on it. The bug only exists with GROUP BY in non-strict mode.

How to repeat:
In non-strict mode, ala:

sql-mode="NO_ENGINE_SUBSTITUTION"

create table `left`
(
    id int ,
    constraint left_pk
        primary key (id)
);

insert into `left` values (1), (2);

create table `right`
(
    id int auto_increment,
    user_id int not null,
    left_id int not null,
    payout int(11) not null,
    constraint left_pk
        primary key (id),
    constraint  unique_index unique index (user_id, left_id)
);

insert into `right` (user_id, left_id, payout) values (1, 1, 100);

SELECT
    `left`.`id`,
    `right`.`payout`
FROM
    `left`
        LEFT JOIN `right` ON `left`.`id` = `right`.`left_id`
        AND `right`.`user_id` = 1
GROUP BY
    `left`.`id`;

In non-strict mode on 8.0.20 the result is:

id,payout
1,NULL
2,NULL

If instead of "payout" I use "ANY_VALUE(`right`.`payout`)", I get the correct result.

On version 8.0.19 the original query also returns the correct result:

id,payout
1,100
2,NULL

I understand that strict mode would prevent this from happening, but whilst strict mode is optional, I expect the query to work properly as before. For `left` primary id `1`, there is only a single matching row in the right table, so there is no reason the `payout` value should be null.

Thank you for the bug report. Please upgrade to latest 8.0.21 version:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 8.0.21 MySQL Community Server - GPL

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> CREATE DATABASE uu;
Query OK, 1 row affected (0.01 sec)

mysql> USE uu
Database changed

mysql> SET sql_mode="NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected (0.01 sec)

mysql> create table `left`
    -> (
    ->     id int ,
    ->     constraint left_pk
    ->         primary key (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into `left` values (1), (2);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create table `right`
    -> (
    ->     id int auto_increment,
    ->     user_id int not null,
    ->     left_id int not null,
    ->     payout int(11) not null,
    ->     constraint left_pk
    ->         primary key (id),
    ->     constraint  unique_index unique index (user_id, left_id)
    -> );
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> insert into `right` (user_id, left_id, payout) values (1, 1, 100);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT
    ->     `left`.`id`,
    ->     `right`.`payout`
    -> FROM
    ->     `left`
    ->         LEFT JOIN `right` ON `left`.`id` = `right`.`left_id`
    ->         AND `right`.`user_id` = 1
    -> GROUP BY
    ->     `left`.`id`;
+----+--------+
| id | payout |
+----+--------+
|  1 |    100 |
|  2 |   NULL |
+----+--------+
2 rows in set (0.01 sec)

mysql>

Thank you for letting me know it's fixed in 8.0.21. I did a search here to find this bug before posting this, but I should have tested in 8.0.21 first. Cheers.