Description:
The source query and its explicitly materialized rewrite are equivalent: the
materialized relation contains exactly the rows produced by the `EXISTS`
subquery. That relation is empty, so both forms must produce eight
NULL-complemented rows from the `RIGHT JOIN`.
MySQL instead returns sixteen matched rows for the source query. Materializing
the filtered `EXISTS` input with CTAS restores the correct result. Disabling
either `semijoin` or optimizer `materialization` also fixes the source query.
# Expected result
Both forms should return `8 | 8 | NULL`.
# Actual result
```text
source query: 16 | 0 | 36
materialized query: 8 | 8 | NULL
```
How to repeat:
DROP DATABASE IF EXISTS mysql_exists_semijoin_repro;
CREATE DATABASE mysql_exists_semijoin_repro;
USE mysql_exists_semijoin_repro;
CREATE TABLE a (x INT, s LONGTEXT);
CREATE TABLE b (y TINYINT);
CREATE TABLE r (z INT, v VARCHAR(10));
CREATE TABLE e (w INT PRIMARY KEY);
INSERT INTO a VALUES (1, 'a'), (2, 'b');
INSERT INTO b VALUES (36);
INSERT INTO r VALUES
(1, 'x'), (2, 'x'), (3, 'x'), (4, 'x'),
(5, 'x'), (6, 'x'), (7, 'x'), (8, 'x');
INSERT INTO e VALUES (1), (2), (3);
-- Source query: incorrectly returns 16, 0, 36.
SELECT COUNT(*) AS row_count,
SUM(b.y IS NULL) AS null_b_rows,
MIN(b.y) AS min_b
FROM a
CROSS JOIN b ON EXISTS (
SELECT 1
FROM e
WHERE NOT e.w / NULLIF(e.w, 0) >= 0
)
RIGHT JOIN r ON a.s <> r.v;
-- Materialize the empty input relation.
CREATE TABLE vect_cut_e AS
SELECT w
FROM e
WHERE NOT w / NULLIF(w, 0) >= 0;
SELECT COUNT(*) AS cut_rows FROM vect_cut_e;
-- Materialized rewrite: correctly returns 8, 8, NULL.
SELECT COUNT(*) AS row_count,
SUM(b.y IS NULL) AS null_b_rows,
MIN(b.y) AS min_b
FROM a
CROSS JOIN b ON EXISTS (SELECT 1 FROM vect_cut_e)
RIGHT JOIN r ON a.s <> r.v;
Description: The source query and its explicitly materialized rewrite are equivalent: the materialized relation contains exactly the rows produced by the `EXISTS` subquery. That relation is empty, so both forms must produce eight NULL-complemented rows from the `RIGHT JOIN`. MySQL instead returns sixteen matched rows for the source query. Materializing the filtered `EXISTS` input with CTAS restores the correct result. Disabling either `semijoin` or optimizer `materialization` also fixes the source query. # Expected result Both forms should return `8 | 8 | NULL`. # Actual result ```text source query: 16 | 0 | 36 materialized query: 8 | 8 | NULL ``` How to repeat: DROP DATABASE IF EXISTS mysql_exists_semijoin_repro; CREATE DATABASE mysql_exists_semijoin_repro; USE mysql_exists_semijoin_repro; CREATE TABLE a (x INT, s LONGTEXT); CREATE TABLE b (y TINYINT); CREATE TABLE r (z INT, v VARCHAR(10)); CREATE TABLE e (w INT PRIMARY KEY); INSERT INTO a VALUES (1, 'a'), (2, 'b'); INSERT INTO b VALUES (36); INSERT INTO r VALUES (1, 'x'), (2, 'x'), (3, 'x'), (4, 'x'), (5, 'x'), (6, 'x'), (7, 'x'), (8, 'x'); INSERT INTO e VALUES (1), (2), (3); -- Source query: incorrectly returns 16, 0, 36. SELECT COUNT(*) AS row_count, SUM(b.y IS NULL) AS null_b_rows, MIN(b.y) AS min_b FROM a CROSS JOIN b ON EXISTS ( SELECT 1 FROM e WHERE NOT e.w / NULLIF(e.w, 0) >= 0 ) RIGHT JOIN r ON a.s <> r.v; -- Materialize the empty input relation. CREATE TABLE vect_cut_e AS SELECT w FROM e WHERE NOT w / NULLIF(w, 0) >= 0; SELECT COUNT(*) AS cut_rows FROM vect_cut_e; -- Materialized rewrite: correctly returns 8, 8, NULL. SELECT COUNT(*) AS row_count, SUM(b.y IS NULL) AS null_b_rows, MIN(b.y) AS min_b FROM a CROSS JOIN b ON EXISTS (SELECT 1 FROM vect_cut_e) RIGHT JOIN r ON a.s <> r.v;