MySQL Bugs: #120159: RIGHT JOIN + Complex Subqueries Returns Extra Rows

Bug #120159	RIGHT JOIN + Complex Subqueries Returns Extra Rows
Submitted:	26 Mar 11:41	Modified:	26 Mar 12:11
Reporter:	Jasper Andrew	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.43 (MySQL Community Server - GPL)	OS:	Any (Docker container)
Assigned to:		CPU Architecture:	Any (x86_64)
Tags:	join, null, reproducible, SQL-standard, subquery

Description:
When performing a RIGHT JOIN involving multiple nested subqueries and joining tables with conditions that always evaluate to TRUE, MySQL produces extra rows compared to PostgreSQL, DuckDB, and SQLite.

In this query, subq_0.c5 is a constant column (55) from a subquery and ref_3.user_id comes from the posts table. The WHERE clause compares subq_1.c22 <> subq_1.c24. According to the SQL standard, the join should generate no rows where the comparison is effectively UNKNOWN or where the joined subquery produces no matching rows.

MySQL incorrectly expands the join, producing 25 rows instead of 0 rows.

How to repeat:
Schema:
```SQL
CREATE TABLE users (
    id           INT,
    username     VARCHAR(100),
    email        VARCHAR(255),
    age          INT,
    status       VARCHAR(20),
    created_at   TIMESTAMP NULL,
    score        DOUBLE
);

CREATE TABLE posts (
    id          INT,
    user_id     INT,
    title       VARCHAR(255),
    content     VARCHAR(1000),
    views       INT,
    likes       INT,
    created_at  TIMESTAMP NULL,
    rating      DOUBLE
);

CREATE TABLE orders (
    id          INT,
    user_id     INT,
    amount      DOUBLE,
    status      VARCHAR(20),
    created_at  TIMESTAMP NULL
);

INSERT INTO users VALUES
(1, 'alice', 'alice@test.com', 20, 'active',  '2022-01-01 10:00:00', 88.5),
(2, 'bob',   'bob@test.com',   30, 'active',  '2022-01-02 11:00:00', 92.3),
(3, 'carol', NULL,             NULL, 'banned','2022-01-03 12:00:00', NULL),
(4, 'dave',  'dave@test.com',  45, 'active',  '2022-01-04 13:00:00', 65.2),
(5, NULL,    'null@test.com',  18, 'inactive','2022-01-05 14:00:00', 70.0);

INSERT INTO posts VALUES
(1, 1, 'Hello World', 'First post', 100, 10, '2022-01-10 10:00:00', 4.5),
(2, 1, 'Another Post', NULL,        150, 20, '2022-01-11 11:00:00', 3.0),
(3, 2, 'Bob Post',     'Content',   NULL,  5, '2022-01-12 12:00:00', NULL),
(4, 3, NULL,           'Empty',     50,   2, '2022-01-13 13:00:00', 5.0),
(5, 4, 'Last Post',    'Last',      300,  30,'2022-01-14 14:00:00', 4.9);

INSERT INTO orders VALUES
(1, 1, 100.00, 'paid',    '2022-02-01 09:00:00'),
(2, 1, 200.50, 'shipped', '2022-02-02 10:00:00'),
(3, 2, NULL,   'failed',  '2022-02-03 11:00:00'),
(4, 3, 50.00,  'paid',    '2022-02-04 12:00:00'),
(5, 5, 999.99, 'paid',    '2022-02-05 13:00:00');

```

Trigger SQL:
```SQL
SELECT  
    subq_1.c10 AS c0, 
    subq_1.c20 AS c3, 
    subq_1.c4 AS c4
FROM 
(
    SELECT  
        ref_3.created_at AS c4, 
        ref_1.id AS c10, 
        52 AS c20, 
        subq_0.c5 AS c22, 
        ref_3.user_id AS c24
    FROM 
    (
        SELECT  
            ref_0.created_at AS c2, 
            55 AS c5
        FROM orders AS ref_0
        WHERE TRUE
    ) AS subq_0
    RIGHT JOIN posts AS ref_1
        INNER JOIN users AS ref_2
            ON TRUE
        RIGHT JOIN posts AS ref_3
            ON TRUE
        ON subq_0.c2 = ref_2.created_at
    WHERE TRUE
) AS subq_1
WHERE subq_1.c22 <> subq_1.c24;
```

Actual Result(MySQL 8.0.43):
```RESULT
+------+----+---------------------+
| c0   | c3 | c4                  |
+------+----+---------------------+
|    1 | 52 | 2022-01-10 10:00:00 |
|    2 | 52 | 2022-01-10 10:00:00 |
|    3 | 52 | 2022-01-10 10:00:00 |
|    4 | 52 | 2022-01-10 10:00:00 |
|    5 | 52 | 2022-01-10 10:00:00 |
|    1 | 52 | 2022-01-10 10:00:00 |
|    2 | 52 | 2022-01-10 10:00:00 |
|    3 | 52 | 2022-01-10 10:00:00 |
|    4 | 52 | 2022-01-10 10:00:00 |
|    5 | 52 | 2022-01-10 10:00:00 |
|    1 | 52 | 2022-01-10 10:00:00 |
|    2 | 52 | 2022-01-10 10:00:00 |
|    3 | 52 | 2022-01-10 10:00:00 |
|    4 | 52 | 2022-01-10 10:00:00 |
|    5 | 52 | 2022-01-10 10:00:00 |
|    1 | 52 | 2022-01-10 10:00:00 |
|    2 | 52 | 2022-01-10 10:00:00 |
|    3 | 52 | 2022-01-10 10:00:00 |
|    4 | 52 | 2022-01-10 10:00:00 |
|    5 | 52 | 2022-01-10 10:00:00 |
|    1 | 52 | 2022-01-10 10:00:00 |
|    2 | 52 | 2022-01-10 10:00:00 |
|    3 | 52 | 2022-01-10 10:00:00 |
|    4 | 52 | 2022-01-10 10:00:00 |
|    5 | 52 | 2022-01-10 10:00:00 |
+------+----+---------------------+
25 rows in set (0.01 sec)
```

Expected Result (PostgreSQL / SQL standard):
```RESULT
+----+----+----+
| c0 | c3 | c4 |
+====+====+====+
+----+----+----+
0 tuples
```

Hi Jasper Andrew,

Thank you for the test case. Verified as described.

Bug#120161 is marked as a duplicate of this bug.