Description:
Removing a redundant RIGHT JOIN ... ON FALSE clause from the query leads to incorrect and inconsistent results in MySQL. Since the join condition is always false, the clause is semantically irrelevant and should not affect query semantics.
However, MySQL returns different results before and after removing this join, while PostgreSQL and DuckDB consistently preserve query equivalence and produce identical results. This indicates that MySQL incorrectly handles the elimination of redundant joins, potentially due to optimizer or execution plan inconsistencies.
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
);
CREATE TABLE comments (
id INT,
post_id INT,
user_id INT,
content VARCHAR(1000),
is_spam INT,
created_at TIMESTAMP NULL
);
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 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');
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');
-- QUERY
WITH subq_1 AS (
SELECT
ref_0.id AS c5,
subq_0.c0 AS c6
FROM orders AS ref_0
RIGHT JOIN orders AS ref_1
RIGHT JOIN orders AS ref_2
ON (
(ref_2.id <> ref_2.id)
OR (
(SELECT STDDEV_SAMP(id) FROM orders) <> 35.87
)
)
ON FALSE,
LATERAL (
SELECT
(SELECT user_id FROM comments LIMIT 1 OFFSET 3) AS c0
FROM comments AS ref_3
WHERE TRUE
) AS subq_0
WHERE TRUE
)
SELECT
(
SELECT COUNT(*)
FROM subq_1
WHERE NULLIF(
COALESCE(17.33, 91.87),
CASE
WHEN RPAD(LPAD('fz71k', c6, 'bwdpd'), c5, ' ') >= 'wr1x2b'
THEN 71.48
ELSE ABS(
CASE
WHEN 85.86 = (SELECT VAR_SAMP(id) FROM posts)
THEN CASE
WHEN FALSE AND FALSE THEN 71.42
ELSE (SELECT STDDEV_POP(id) FROM posts)
END
ELSE 75.77
END
)
END
) <> 68.7
)
+
(
SELECT COUNT(*)
FROM subq_1
WHERE NOT (
NULLIF(
COALESCE(17.33, 91.87),
CASE
WHEN RPAD(LPAD('fz71k', c6, 'bwdpd'), c5, ' ') >= 'wr1x2b'
THEN 71.48
ELSE ABS(
CASE
WHEN 85.86 = (SELECT VAR_SAMP(id) FROM posts)
THEN CASE
WHEN FALSE AND FALSE THEN 71.42
ELSE (SELECT STDDEV_POP(id) FROM posts)
END
ELSE 75.77
END
)
END
) <> 68.7
)
)
+
(
SELECT COUNT(*)
FROM subq_1
WHERE (
NULLIF(
COALESCE(17.33, 91.87),
CASE
WHEN RPAD(LPAD('fz71k', c6, 'bwdpd'), c5, ' ') >= 'wr1x2b'
THEN 71.48
ELSE ABS(
CASE
WHEN 85.86 = (SELECT VAR_SAMP(id) FROM posts)
THEN CASE
WHEN FALSE AND FALSE THEN 71.42
ELSE (SELECT STDDEV_POP(id) FROM posts)
END
ELSE 75.77
END
)
END
) <> 68.7
) IS NULL
);
-- result: {100} correct!
-- After removing the RIGHT JOIN orders AS ref_1 ... ON FALSE, the result is {200}
```
Description: Removing a redundant RIGHT JOIN ... ON FALSE clause from the query leads to incorrect and inconsistent results in MySQL. Since the join condition is always false, the clause is semantically irrelevant and should not affect query semantics. However, MySQL returns different results before and after removing this join, while PostgreSQL and DuckDB consistently preserve query equivalence and produce identical results. This indicates that MySQL incorrectly handles the elimination of redundant joins, potentially due to optimizer or execution plan inconsistencies. 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 ); CREATE TABLE comments ( id INT, post_id INT, user_id INT, content VARCHAR(1000), is_spam INT, created_at TIMESTAMP NULL ); 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 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'); 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'); -- QUERY WITH subq_1 AS ( SELECT ref_0.id AS c5, subq_0.c0 AS c6 FROM orders AS ref_0 RIGHT JOIN orders AS ref_1 RIGHT JOIN orders AS ref_2 ON ( (ref_2.id <> ref_2.id) OR ( (SELECT STDDEV_SAMP(id) FROM orders) <> 35.87 ) ) ON FALSE, LATERAL ( SELECT (SELECT user_id FROM comments LIMIT 1 OFFSET 3) AS c0 FROM comments AS ref_3 WHERE TRUE ) AS subq_0 WHERE TRUE ) SELECT ( SELECT COUNT(*) FROM subq_1 WHERE NULLIF( COALESCE(17.33, 91.87), CASE WHEN RPAD(LPAD('fz71k', c6, 'bwdpd'), c5, ' ') >= 'wr1x2b' THEN 71.48 ELSE ABS( CASE WHEN 85.86 = (SELECT VAR_SAMP(id) FROM posts) THEN CASE WHEN FALSE AND FALSE THEN 71.42 ELSE (SELECT STDDEV_POP(id) FROM posts) END ELSE 75.77 END ) END ) <> 68.7 ) + ( SELECT COUNT(*) FROM subq_1 WHERE NOT ( NULLIF( COALESCE(17.33, 91.87), CASE WHEN RPAD(LPAD('fz71k', c6, 'bwdpd'), c5, ' ') >= 'wr1x2b' THEN 71.48 ELSE ABS( CASE WHEN 85.86 = (SELECT VAR_SAMP(id) FROM posts) THEN CASE WHEN FALSE AND FALSE THEN 71.42 ELSE (SELECT STDDEV_POP(id) FROM posts) END ELSE 75.77 END ) END ) <> 68.7 ) ) + ( SELECT COUNT(*) FROM subq_1 WHERE ( NULLIF( COALESCE(17.33, 91.87), CASE WHEN RPAD(LPAD('fz71k', c6, 'bwdpd'), c5, ' ') >= 'wr1x2b' THEN 71.48 ELSE ABS( CASE WHEN 85.86 = (SELECT VAR_SAMP(id) FROM posts) THEN CASE WHEN FALSE AND FALSE THEN 71.42 ELSE (SELECT STDDEV_POP(id) FROM posts) END ELSE 75.77 END ) END ) <> 68.7 ) IS NULL ); -- result: {100} correct! -- After removing the RIGHT JOIN orders AS ref_1 ... ON FALSE, the result is {200} ```