Description:
When performing nested RIGHT JOIN operations involving an empty subquery and join conditions that depend on NULL values, MySQL produces incorrect duplicated rows for a subset of the result.
In this query, the subquery subq_0 returns no rows due to a WHERE FALSE condition. According to SQL standard semantics, the subsequent RIGHT JOIN operations should preserve rows from the right table (users) and NULL-extend the left side, resulting in exactly one output row per row in users.
However, MySQL incorrectly expands the join for certain rows, producing multiple duplicated rows with different values coming from the intermediate subquery subq_1. This indicates that the join condition involving NULL (subq_0.c0 = ref_2.id) is not correctly enforced during join evaluation.
As a result, rows from subq_1 are incorrectly combined with some rows from users, leading to partial Cartesian expansion.
Other systems such as PostgreSQL, DuckDB, and SQLite return the correct result without duplication.
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 comments (
id INT,
post_id INT,
user_id INT,
content VARCHAR(1000),
is_spam INT,
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 comments VALUES
(1, 1, 2, 'Nice post', 0, '2022-01-20 10:00:00'),
(2, 1, 3, 'Spam here', 1, '2022-01-21 11:00:00'),
(3, 2, 1, 'Thanks', 0, '2022-01-22 12:00:00'),
(4, 4, 5, NULL, 0, '2022-01-23 13:00:00');
```
Trigger SQL:
```SQL
SELECT
ref_2.username,
subq_1.c2,
ref_2.id
FROM
(SELECT 1 AS c0 FROM comments WHERE FALSE) AS subq_0
RIGHT JOIN
(SELECT created_at AS c2 FROM users) AS subq_1
ON TRUE
RIGHT JOIN users AS ref_2
ON subq_0.c0 = ref_2.id;
```
Actual Result(MySQL 8.0.43):
```RESULT
+----------+---------------------+------+
| username | c2 | id |
+----------+---------------------+------+
| alice | 2022-01-05 14:00:00 | 1 |
| alice | 2022-01-04 13:00:00 | 1 |
| alice | 2022-01-03 12:00:00 | 1 |
| alice | 2022-01-02 11:00:00 | 1 |
| alice | 2022-01-01 10:00:00 | 1 |
| bob | NULL | 2 |
| carol | NULL | 3 |
| dave | NULL | 4 |
| NULL | NULL | 5 |
+----------+---------------------+------+
9 rows in set (0.00 sec)
```
Expected Result (PostgreSQL / SQL standard):
```RESULT
username | c2 | id
----------+----+----
alice | | 1
bob | | 2
carol | | 3
dave | | 4
| | 5
(5 rows)
```
Description: When performing nested RIGHT JOIN operations involving an empty subquery and join conditions that depend on NULL values, MySQL produces incorrect duplicated rows for a subset of the result. In this query, the subquery subq_0 returns no rows due to a WHERE FALSE condition. According to SQL standard semantics, the subsequent RIGHT JOIN operations should preserve rows from the right table (users) and NULL-extend the left side, resulting in exactly one output row per row in users. However, MySQL incorrectly expands the join for certain rows, producing multiple duplicated rows with different values coming from the intermediate subquery subq_1. This indicates that the join condition involving NULL (subq_0.c0 = ref_2.id) is not correctly enforced during join evaluation. As a result, rows from subq_1 are incorrectly combined with some rows from users, leading to partial Cartesian expansion. Other systems such as PostgreSQL, DuckDB, and SQLite return the correct result without duplication. 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 comments ( id INT, post_id INT, user_id INT, content VARCHAR(1000), is_spam INT, 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 comments VALUES (1, 1, 2, 'Nice post', 0, '2022-01-20 10:00:00'), (2, 1, 3, 'Spam here', 1, '2022-01-21 11:00:00'), (3, 2, 1, 'Thanks', 0, '2022-01-22 12:00:00'), (4, 4, 5, NULL, 0, '2022-01-23 13:00:00'); ``` Trigger SQL: ```SQL SELECT ref_2.username, subq_1.c2, ref_2.id FROM (SELECT 1 AS c0 FROM comments WHERE FALSE) AS subq_0 RIGHT JOIN (SELECT created_at AS c2 FROM users) AS subq_1 ON TRUE RIGHT JOIN users AS ref_2 ON subq_0.c0 = ref_2.id; ``` Actual Result(MySQL 8.0.43): ```RESULT +----------+---------------------+------+ | username | c2 | id | +----------+---------------------+------+ | alice | 2022-01-05 14:00:00 | 1 | | alice | 2022-01-04 13:00:00 | 1 | | alice | 2022-01-03 12:00:00 | 1 | | alice | 2022-01-02 11:00:00 | 1 | | alice | 2022-01-01 10:00:00 | 1 | | bob | NULL | 2 | | carol | NULL | 3 | | dave | NULL | 4 | | NULL | NULL | 5 | +----------+---------------------+------+ 9 rows in set (0.00 sec) ``` Expected Result (PostgreSQL / SQL standard): ```RESULT username | c2 | id ----------+----+---- alice | | 1 bob | | 2 carol | | 3 dave | | 4 | | 5 (5 rows) ```