Description:
MySQL returns different results when the relation used by an `IN` predicate is
materialized into a temporary table. The original query returns no rows, while
the materialized residual returns one row.
The relation contains a `UNION` below a `RIGHT JOIN` and a `LEFT JOIN` whose
`EXISTS` condition is false. SQL therefore requires the `LEFT JOIN` to
NULL-complement the right-side columns. During materialization, MySQL pushes
the semijoin predicate into the temporary table and stores a non-NULL value
instead. The subsequent `IN` lookup then matches a row that the original join
rejects.
This is a MySQL optimizer logic bug involving predicate pushdown, semijoin
materialization, `UNION`, and outer-join NULL handling.
# Expected result
The original and materialized statements should return the same result:
```text
exists_rows: 0
original_rows: 0
materialized_rows: 0
```
# Actual result
On MySQL 9.7.1, the self-contained reproducer returns:
```text
exists_rows: 0
original_rows: 0
materialized_rows: 1
cut_rows: 2
nonnull_values: 2
min_value: 58
max_value: 58
```
How to repeat:
The following script is self-contained. It creates all base tables, inserts
one row into each, and executes both the original `IN` subquery and the
equivalent temporary-table form.
```sql
DROP DATABASE IF EXISTS mysql_in_union_materialization_repro;
CREATE DATABASE mysql_in_union_materialization_repro;
USE mysql_in_union_materialization_repro;
CREATE TABLE t1 (
c1 INT PRIMARY KEY,
c3 VARCHAR(255),
c4 INT,
c5 DATE NOT NULL,
c6 VARCHAR(10) NOT NULL
);
CREATE TABLE t2 (
c1 INT PRIMARY KEY
);
CREATE TABLE t3 (
c1 INT PRIMARY KEY,
c8 INT,
c12 VARCHAR(50)
);
INSERT INTO t1 VALUES
(58, 'x', 100, '2024-01-01', 's58');
INSERT INTO t2 VALUES
(1);
INSERT INTO t3 VALUES
(1, 58, 'z');
SELECT COUNT(*) AS exists_rows
FROM t2 AS t20
JOIN t1 AS t21
ON t20.c1 <= t21.c4 AND t21.c3 LIKE 'sample_87%'
WHERE t21.c3 NOT LIKE '%sample_14';
-- Original form: the false EXISTS makes t19.c8 NULL.
SELECT COUNT(*) AS original_rows
FROM t1 AS t7
WHERE t7.c6 NOT LIKE '%sample_42%'
AND t7.c1 IN (
SELECT t19.c8
FROM t1 AS t14
RIGHT JOIN (
SELECT t16.c1 AS q7_col_1,
LEFT(t16.c6, t16.c4) AS q7_col_2,
(t16.c4 * t16.c1) AS q7_col_3
FROM t1 AS t16
WHERE t16.c5 = t16.c5
AND t16.c5 NOT BETWEEN '2023-01-01 00:00:00'
AND '2023-12-31 23:59:59'
GROUP BY t16.c1, t16.c6, t16.c4
HAVING STDDEV_SAMP(t16.c1) <= 55 AND COUNT(t16.c5) = 97
UNION ALL
SELECT t17.c1, t17.c12, CAST(31 AS DECIMAL)
FROM t3 AS t17
UNION
SELECT t18.c1, t18.c3, t18.c4
FROM t1 AS t18
) AS sq15
ON t14.c1 <> sq15.q7_col_1
AND t14.c6 = sq15.q7_col_2
LEFT JOIN t3 AS t19
ON EXISTS (
SELECT '2023-01-01'
FROM t2 AS t20
JOIN t1 AS t21
ON t20.c1 <= t21.c4 AND t21.c3 LIKE 'sample_87%'
WHERE t21.c3 NOT LIKE '%sample_14'
)
);
-- Materialized form: CTAS incorrectly stores t19.c8 as 58.
CREATE TEMPORARY TABLE vect_cut_033 AS
SELECT t19.c8 AS t19__c8,
t14.c1 AS t14__c1,
sq15.q7_col_1 AS sq15__q7_col_1,
t14.c6 AS t14__c6,
sq15.q7_col_2 AS sq15__q7_col_2
FROM t1 AS t14
RIGHT JOIN (
SELECT t16.c1 AS q7_col_1,
LEFT(t16.c6, t16.c4) AS q7_col_2,
(t16.c4 * t16.c1) AS q7_col_3
FROM t1 AS t16
WHERE t16.c5 = t16.c5
AND t16.c5 NOT BETWEEN '2023-01-01 00:00:00'
AND '2023-12-31 23:59:59'
GROUP BY t16.c1, t16.c6, t16.c4
HAVING STDDEV_SAMP(t16.c1) <= 55 AND COUNT(t16.c5) = 97
UNION ALL
SELECT t17.c1, t17.c12, CAST(31 AS DECIMAL)
FROM t3 AS t17
UNION
SELECT t18.c1, t18.c3, t18.c4
FROM t1 AS t18
) AS sq15
ON t14.c1 <> sq15.q7_col_1
AND t14.c6 = sq15.q7_col_2
LEFT JOIN t3 AS t19
ON EXISTS (
SELECT '2023-01-01'
FROM t2 AS t20
JOIN t1 AS t21
ON t20.c1 <= t21.c4 AND t21.c3 LIKE 'sample_87%'
WHERE t21.c3 NOT LIKE '%sample_14'
);
SELECT COUNT(*) AS materialized_rows
FROM t1 AS t7
WHERE t7.c6 NOT LIKE '%sample_42%'
AND t7.c1 IN (SELECT input.t19__c8 FROM vect_cut_033 AS input);
SELECT COUNT(*) AS cut_rows,
COUNT(t19__c8) AS nonnull_values,
MIN(t19__c8) AS min_value,
MAX(t19__c8) AS max_value
FROM vect_cut_033;
```
The first count proves that the `EXISTS` condition is false. A correct
`LEFT JOIN` must consequently produce `NULL` for `t19.c8`; MySQL 9.7.1
materializes two rows with value `58` instead.
Description: MySQL returns different results when the relation used by an `IN` predicate is materialized into a temporary table. The original query returns no rows, while the materialized residual returns one row. The relation contains a `UNION` below a `RIGHT JOIN` and a `LEFT JOIN` whose `EXISTS` condition is false. SQL therefore requires the `LEFT JOIN` to NULL-complement the right-side columns. During materialization, MySQL pushes the semijoin predicate into the temporary table and stores a non-NULL value instead. The subsequent `IN` lookup then matches a row that the original join rejects. This is a MySQL optimizer logic bug involving predicate pushdown, semijoin materialization, `UNION`, and outer-join NULL handling. # Expected result The original and materialized statements should return the same result: ```text exists_rows: 0 original_rows: 0 materialized_rows: 0 ``` # Actual result On MySQL 9.7.1, the self-contained reproducer returns: ```text exists_rows: 0 original_rows: 0 materialized_rows: 1 cut_rows: 2 nonnull_values: 2 min_value: 58 max_value: 58 ``` How to repeat: The following script is self-contained. It creates all base tables, inserts one row into each, and executes both the original `IN` subquery and the equivalent temporary-table form. ```sql DROP DATABASE IF EXISTS mysql_in_union_materialization_repro; CREATE DATABASE mysql_in_union_materialization_repro; USE mysql_in_union_materialization_repro; CREATE TABLE t1 ( c1 INT PRIMARY KEY, c3 VARCHAR(255), c4 INT, c5 DATE NOT NULL, c6 VARCHAR(10) NOT NULL ); CREATE TABLE t2 ( c1 INT PRIMARY KEY ); CREATE TABLE t3 ( c1 INT PRIMARY KEY, c8 INT, c12 VARCHAR(50) ); INSERT INTO t1 VALUES (58, 'x', 100, '2024-01-01', 's58'); INSERT INTO t2 VALUES (1); INSERT INTO t3 VALUES (1, 58, 'z'); SELECT COUNT(*) AS exists_rows FROM t2 AS t20 JOIN t1 AS t21 ON t20.c1 <= t21.c4 AND t21.c3 LIKE 'sample_87%' WHERE t21.c3 NOT LIKE '%sample_14'; -- Original form: the false EXISTS makes t19.c8 NULL. SELECT COUNT(*) AS original_rows FROM t1 AS t7 WHERE t7.c6 NOT LIKE '%sample_42%' AND t7.c1 IN ( SELECT t19.c8 FROM t1 AS t14 RIGHT JOIN ( SELECT t16.c1 AS q7_col_1, LEFT(t16.c6, t16.c4) AS q7_col_2, (t16.c4 * t16.c1) AS q7_col_3 FROM t1 AS t16 WHERE t16.c5 = t16.c5 AND t16.c5 NOT BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59' GROUP BY t16.c1, t16.c6, t16.c4 HAVING STDDEV_SAMP(t16.c1) <= 55 AND COUNT(t16.c5) = 97 UNION ALL SELECT t17.c1, t17.c12, CAST(31 AS DECIMAL) FROM t3 AS t17 UNION SELECT t18.c1, t18.c3, t18.c4 FROM t1 AS t18 ) AS sq15 ON t14.c1 <> sq15.q7_col_1 AND t14.c6 = sq15.q7_col_2 LEFT JOIN t3 AS t19 ON EXISTS ( SELECT '2023-01-01' FROM t2 AS t20 JOIN t1 AS t21 ON t20.c1 <= t21.c4 AND t21.c3 LIKE 'sample_87%' WHERE t21.c3 NOT LIKE '%sample_14' ) ); -- Materialized form: CTAS incorrectly stores t19.c8 as 58. CREATE TEMPORARY TABLE vect_cut_033 AS SELECT t19.c8 AS t19__c8, t14.c1 AS t14__c1, sq15.q7_col_1 AS sq15__q7_col_1, t14.c6 AS t14__c6, sq15.q7_col_2 AS sq15__q7_col_2 FROM t1 AS t14 RIGHT JOIN ( SELECT t16.c1 AS q7_col_1, LEFT(t16.c6, t16.c4) AS q7_col_2, (t16.c4 * t16.c1) AS q7_col_3 FROM t1 AS t16 WHERE t16.c5 = t16.c5 AND t16.c5 NOT BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59' GROUP BY t16.c1, t16.c6, t16.c4 HAVING STDDEV_SAMP(t16.c1) <= 55 AND COUNT(t16.c5) = 97 UNION ALL SELECT t17.c1, t17.c12, CAST(31 AS DECIMAL) FROM t3 AS t17 UNION SELECT t18.c1, t18.c3, t18.c4 FROM t1 AS t18 ) AS sq15 ON t14.c1 <> sq15.q7_col_1 AND t14.c6 = sq15.q7_col_2 LEFT JOIN t3 AS t19 ON EXISTS ( SELECT '2023-01-01' FROM t2 AS t20 JOIN t1 AS t21 ON t20.c1 <= t21.c4 AND t21.c3 LIKE 'sample_87%' WHERE t21.c3 NOT LIKE '%sample_14' ); SELECT COUNT(*) AS materialized_rows FROM t1 AS t7 WHERE t7.c6 NOT LIKE '%sample_42%' AND t7.c1 IN (SELECT input.t19__c8 FROM vect_cut_033 AS input); SELECT COUNT(*) AS cut_rows, COUNT(t19__c8) AS nonnull_values, MIN(t19__c8) AS min_value, MAX(t19__c8) AS max_value FROM vect_cut_033; ``` The first count proves that the `EXISTS` condition is false. A correct `LEFT JOIN` must consequently produce `NULL` for `t19.c8`; MySQL 9.7.1 materializes two rows with value `58` instead.