bug: nested left join return wrong result

[davidshiz]

Have you read the Contributing Guidelines on issues?

• [X] I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• [X] I confirm there is no existing issue for this

Describe the problem

mysql> SELECT
    ->     t5.a
    -> FROM
    ->     t5
    ->     LEFT JOIN (
    ->         (t6, t7)
    ->         LEFT JOIN t8 ON t7.b = t8.b
    ->         AND t6.b < 10
    ->     ) ON t6.b >= 3
    ->     AND t5.b = t7.b;
+------+
| a    |
+------+
|    2 |
|    2 |
|    2 |
|    2 |
|    2 |
|    2 |
|    2 |
|    2 |
|    2 |
|    2 |
|    2 |
|    2 |
|    3 |
|    3 |
|    3 |
|    3 |
|    3 |
|    3 |
|    3 |
|    3 |
|    3 |
|    3 |
|    3 |
|    3 |
+------+
24 rows in set (0.01 sec)

Expected behavior

mysql> SELECT
    ->     t5.a
    -> FROM
    ->     t5
    ->     LEFT JOIN (
    ->         (t6, t7)
    ->         LEFT JOIN t8 ON t7.b = t8.b
    ->         AND t6.b < 10
    ->     ) ON t6.b >= 3
    ->     AND t5.b = t7.b;
+------+
| a    |
+------+
|    3 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

How To Reproduce

CREATE TABLE t5 (a int, b int, c int);
CREATE TABLE t6 (a int, b int, c int);
CREATE TABLE t7 (a int, b int, c int);
CREATE TABLE t8 (a int, b int, c int);
INSERT INTO t5 VALUES (3,1,0), (2,2,0), (3,3,0);
INSERT INTO t6 VALUES (3,2,0), (6,2,0), (6,1,0);
INSERT INTO t7 VALUES (1,1,0), (2,2,0);
INSERT INTO t8 VALUES (0,2,0), (1,2,0);
SELECT
    t5.a
FROM
    t5
    LEFT JOIN (
        (t6, t7)
        LEFT JOIN t8 ON t7.b = t8.b
        AND t6.b < 10
    ) ON t6.b >= 3
    AND t5.b = t7.b;

Environment

root@ub01:~# /stonedb57/install/bin/mysqld --version
/stonedb57/install/bin/mysqld  Ver 5.7.36-StoneDB-v1.0.3 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: 1ba566389
        Last commit time: Date:   Fri May 5 16:38:50 2023 +0800
        Build time: Date: Fri May  5 20:17:38 CST 2023

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

• [ ] Yes, I will!

[davidshiz]

After removing the last statement AND t5.b = t7.b, result set is correct.

SELECT
    t5.a
FROM
    t5
    LEFT JOIN (
        (t6, t7)
        LEFT JOIN t8 ON t7.b = t8.b
        AND t6.b < 10
    ) ON t6.b >= 3;