bug: LEFT JOIN clause did not return the correct result set

[davidshiz]

Have you read the Contributing Guidelines on issues?

• I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• I confirm there is no existing issue for this

Describe the problem

The wrong result is as follows:

mysql> select * from t1 left join t2 on (t1.a = t2.a) left join t3 on (t2.a = t3.a) 
left join t4 on (t4.a = t3.a) left join t5 on (t5.a = t2.a and t5.a = t4.a);
+------+------+------+------+------+------+------+------+------+------+
| a    | b    | a    | b    | a    | b    | a    | b    | a    | b    |
+------+------+------+------+------+------+------+------+------+------+
|    1 |    3 |    1 |    2 |    1 |    2 |    1 |    3 |    1 |    2 |
|    1 |    3 |    1 |    2 |    1 |    2 |    1 |    3 |    3 |    4 |
|    2 |    3 |    2 |    4 |    2 |    3 | NULL | NULL |    1 |    2 |
|    2 |    3 |    2 |    4 |    2 |    3 | NULL | NULL |    3 |    4 |
|    3 |    4 | NULL | NULL | NULL | NULL | NULL | NULL |    1 |    2 |
|    3 |    4 | NULL | NULL | NULL | NULL | NULL | NULL |    3 |    4 |
+------+------+------+------+------+------+------+------+------+------+
6 rows in set (0.00 sec)

Expected behavior

mysql> select * from t1 left join t2 on (t1.a = t2.a) left join t3 on (t2.a = t3.a) left join t4 on
 (t4.a = t3.a) left join t5 on (t5.a = t2.a and t5.a = t4.a);
+------+------+------+------+------+------+------+------+------+------+
| a    | b    | a    | b    | a    | b    | a    | b    | a    | b    |
+------+------+------+------+------+------+------+------+------+------+
|    1 |    3 |    1 |    2 |    1 |    2 |    1 |    3 |    1 |    2 |
|    2 |    3 |    2 |    4 |    2 |    3 | NULL | NULL | NULL | NULL |
|    3 |    4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+------+------+------+------+------+------+------+------+------+------+
3 rows in set (0.00 sec)

How To Reproduce

create table t1 (a int, b int);
create table t2 (a int, b int);
create table t3 (a int, b int);
create table t4 (a int, b int);
create table t5 (a int, b int);
insert into t1 values (1, 3), (2, 3), (3, 4);
insert into t2 values (1, 2), (2, 4), (4, 5);
insert into t3 values (1, 2), (2, 3), (3, 4), (4, 5);
insert into t4 values (1, 3);
insert into t5 values (1, 2), (3, 4);

select * from t1 left join t2 on (t1.a = t2.a) left join t3 on (t2.a = t3.a) left join t4 on (t4.a = t3.a) left join t5 on (t5.a = t2.a and t5.a = t4.a);

Environment

root@localhost:/# /stonedb57/install/bin/mysqld --version
/stonedb57/install/bin/mysqld  Ver 5.7.36-StoneDB for Linux on x86_64 (build-)
build information as follow:
        Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev
        Branch name: stonedb-5.7-dev
        Last commit ID: d0c2e01b6
        Last commit time: Date:   Wed Nov 2 19:58:00 2022 +0800
        Build time: Date: Sun 06 Nov 2022 08:50:06 AM UTC
root@ub01:~# cat /etc/os-release
NAME="Ubuntu"
VERSION="20.04.5 LTS (Focal Fossa)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 20.04.5 LTS"
VERSION_ID="20.04"

Are you interested in submitting a PR to solve the problem?

• Yes, I will!

[RingsC]

Same as #887 .

[adofsauron]

The query results of the following SQL statements are correct

select * from t1 left join t2 on (t1.a = t2.a) left join t3 on (t2.a = t3.a) left join t4 on (t4.a = t3.a) left join t5 on (t5.a = t4.a);
 
select * from t1 left join t2 on (t1.a = t2.a) left join t3 on (t2.a = t3.a and t3.a = t1.a);
 
select * from t1 left join t2 on (t1.a = t2.a) left join t3 on (t2.a = t3.a) left join t4 on (t4.a = t3.a and t1.a = t4.a);
 
select * from t1 left join t2 on (t1.a = t2.a) left join t3 on (t2.a = t3.a) left join t4 on (t4.a = t3.a and t2.a = t4.a);

[adofsauron]

ACK

[adofsauron]

A left join of one table to multiple tables

    SELECT
        *
    FROM
        t1
    LEFT JOIN t2 ON
        (t1.a = t2.a)
    LEFT JOIN t3 ON
        (t1.a = t3.a)
    LEFT JOIN t4 ON
        (t1.a = t4.a)
    LEFT JOIN t5 ON
        (t1.a = t5.a);