Description:
MySQL incorrectly returns an empty result set for a query containing a RIGHT JOIN whose ON condition is always false.
According to RIGHT JOIN semantics, all rows from the right-hand table should still be preserved, with columns from the left-hand side filled with NULL.
For the test case below, the expected result contains 5 rows of NULL, while MySQL returns:
Empty set
The same query returns the expected 5 rows on MariaDB and PostgreSQL.
The issue is still reproducible after disabling both:
SET SESSION optimizer_switch='derived_condition_pushdown=off';
SET SESSION optimizer_switch='derived_merge=off';
Why five NULL rows are expected
The important part is the EXISTS predicate:
EXISTS (
SELECT 1
FROM posts AS ref_3
WHERE 60 <> ref_3.id
AND ref_3.id IS NULL
)
This predicate cannot be TRUE.
If ref_3.id is non-NULL:
ref_3.id IS NULL = FALSE
If ref_3.id is NULL:
60 <> NULL = UNKNOWN
Therefore:
60 <> ref_3.id
AND ref_3.id IS NULL
can never evaluate to TRUE.
The subquery consequently returns zero rows, so:
EXISTS (...) = FALSE
The join is therefore equivalent, for this data, to:
...
RIGHT JOIN posts AS ref_2
ON FALSE
Because this is a RIGHT JOIN, all rows of ref_2 must still be preserved.
posts contains 5 rows, so the join produces 5 unmatched right-side rows. For each such row, all columns from the left side are NULL-extended:
ref_0.age = NULL
ref_0.status = NULL
ref_1.id = NULL
Therefore the derived table conceptually contains:
c0 c1 c2
NULL NULL 1
NULL NULL 2
NULL NULL 3
NULL NULL 4
NULL NULL 5
The outer predicate:
WHERE subq_0.c1 IS NULL
is true for all five rows.
Finally, only c0 is projected:
SELECT subq_0.c0
so the correct result is exactly:
NULL
NULL
NULL
NULL
NULL
Additional observation
The problem appears related to the handling or optimization of NULL-extended rows produced by the RIGHT OUTER JOIN, possibly in combination with the derived-table predicate:
subq_0.c1 IS NULL
The optimizer may be incorrectly reasoning about:
ref_0.status IS NULL
using the original users rows while failing to account for the fact that ref_0.status can become NULL because of outer-join null extension.
However, the exact optimizer component responsible is currently unknown.
Disabling:
derived_condition_pushdown
derived_merge
does not change the incorrect result, so the issue does not appear to depend solely on either of these two optimizations.
How to repeat:
```SQL
-- schema
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
);
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);
--Execute
SELECT
subq_0.c0
FROM (
SELECT
ref_0.age AS c0,
ref_0.status AS c1,
CASE
WHEN ref_1.id IS NULL THEN ref_2.id
ELSE ref_1.user_id
END AS c2
FROM users AS ref_0
INNER JOIN posts AS ref_1
ON ref_1.rating IS NOT NULL
RIGHT JOIN posts AS ref_2
ON EXISTS (
SELECT 1
FROM posts AS ref_3
WHERE 60 <> ref_3.id
AND ref_3.id IS NULL
)
) AS subq_0
WHERE subq_0.c1 IS NULL;
```SQL
Actual result on MySQL
Empty set
Expected result
+------+
| c0 |
+------+
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+------+
5 rows in set
MariaDB and PostgreSQL produce the expected five rows.
Description: MySQL incorrectly returns an empty result set for a query containing a RIGHT JOIN whose ON condition is always false. According to RIGHT JOIN semantics, all rows from the right-hand table should still be preserved, with columns from the left-hand side filled with NULL. For the test case below, the expected result contains 5 rows of NULL, while MySQL returns: Empty set The same query returns the expected 5 rows on MariaDB and PostgreSQL. The issue is still reproducible after disabling both: SET SESSION optimizer_switch='derived_condition_pushdown=off'; SET SESSION optimizer_switch='derived_merge=off'; Why five NULL rows are expected The important part is the EXISTS predicate: EXISTS ( SELECT 1 FROM posts AS ref_3 WHERE 60 <> ref_3.id AND ref_3.id IS NULL ) This predicate cannot be TRUE. If ref_3.id is non-NULL: ref_3.id IS NULL = FALSE If ref_3.id is NULL: 60 <> NULL = UNKNOWN Therefore: 60 <> ref_3.id AND ref_3.id IS NULL can never evaluate to TRUE. The subquery consequently returns zero rows, so: EXISTS (...) = FALSE The join is therefore equivalent, for this data, to: ... RIGHT JOIN posts AS ref_2 ON FALSE Because this is a RIGHT JOIN, all rows of ref_2 must still be preserved. posts contains 5 rows, so the join produces 5 unmatched right-side rows. For each such row, all columns from the left side are NULL-extended: ref_0.age = NULL ref_0.status = NULL ref_1.id = NULL Therefore the derived table conceptually contains: c0 c1 c2 NULL NULL 1 NULL NULL 2 NULL NULL 3 NULL NULL 4 NULL NULL 5 The outer predicate: WHERE subq_0.c1 IS NULL is true for all five rows. Finally, only c0 is projected: SELECT subq_0.c0 so the correct result is exactly: NULL NULL NULL NULL NULL Additional observation The problem appears related to the handling or optimization of NULL-extended rows produced by the RIGHT OUTER JOIN, possibly in combination with the derived-table predicate: subq_0.c1 IS NULL The optimizer may be incorrectly reasoning about: ref_0.status IS NULL using the original users rows while failing to account for the fact that ref_0.status can become NULL because of outer-join null extension. However, the exact optimizer component responsible is currently unknown. Disabling: derived_condition_pushdown derived_merge does not change the incorrect result, so the issue does not appear to depend solely on either of these two optimizations. How to repeat: ```SQL -- schema 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 ); 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); --Execute SELECT subq_0.c0 FROM ( SELECT ref_0.age AS c0, ref_0.status AS c1, CASE WHEN ref_1.id IS NULL THEN ref_2.id ELSE ref_1.user_id END AS c2 FROM users AS ref_0 INNER JOIN posts AS ref_1 ON ref_1.rating IS NOT NULL RIGHT JOIN posts AS ref_2 ON EXISTS ( SELECT 1 FROM posts AS ref_3 WHERE 60 <> ref_3.id AND ref_3.id IS NULL ) ) AS subq_0 WHERE subq_0.c1 IS NULL; ```SQL Actual result on MySQL Empty set Expected result +------+ | c0 | +------+ | NULL | | NULL | | NULL | | NULL | | NULL | +------+ 5 rows in set MariaDB and PostgreSQL produce the expected five rows.