Description:
We are seeing incorrect results from a specific class of query. The query has two nested derived tables, each defined by a subquery, joined onto the outer tables with LEFT JOIN. The join keys across every table form
a single equality chain (for example, order.customer_id = address.customer_id = customer.id), and none of the columns in the joins are indexed. The
innermost subquery applies a WHERE filter to its own join-key column (a column participating in the equality chain). This filter is
required to reproduce the problem: moving it to any other column in the same subquery, or removing it, produces correct results. However, the filter does not need to be restrictive enough to effect the results (i.e. customer.id > 0 when all ids are positive would still produce incorrect results).
As a concrete example, on MySQL 8.4.11 a statement of this shape that should return 3 rows — one per row of the outermost table — returns 4 instead:
one row appears twice, and a separate row is null-extended even though it has exactly one matching row in the joined data. The data guarantees a single
match per outer row, so the correct result is not ambiguous. The same statement returns the expected 3 rows on 9.5.0 and later.
As it stands, work arounds do exist. 8.4.11 returns the correct result if a unique index is added on any join key, or if derived_merge is disabled.
How to repeat:
DROP DATABASE IF EXISTS mysql84Bug;
CREATE DATABASE mysql84Bug;
USE mysql84Bug;
CREATE TABLE customers (
customer_id bigint DEFAULT NULL
) ENGINE=InnoDB;
CREATE TABLE addresses (
address_id bigint DEFAULT NULL,
customer_id bigint DEFAULT NULL,
rating double DEFAULT NULL
) ENGINE=InnoDB;
CREATE TABLE orders (
order_id bigint DEFAULT NULL,
customer_id bigint DEFAULT NULL
) ENGINE=InnoDB;
INSERT INTO customers (customer_id) VALUES (1),(2);
INSERT INTO addresses (address_id, customer_id, rating) VALUES (1,1,2.0),(2,2,4.0);
-- Customer 1 has two orders, customer 2 has one.
INSERT INTO orders (order_id, customer_id) VALUES (1,1),(2,1),(3,2);
ANALYZE TABLE customers, addresses, orders;
-- The statement. Each order joins to its customer's address, and through a second hop back to
-- the same address table, to project that address's rating.
--
-- The WHERE clause inside the innermost subquery keeps every row of customers, so it cannot
-- account for any row being added or lost. Removing it, or moving it to any other column,
-- makes the server return the correct result.
--
-- Expected: 3 rows, one per order. Actual on 8.4.11: 4 rows.
SELECT COUNT(*) AS rows_returned, 3 AS rows_expected
FROM (
SELECT orders.order_id, d0.rating
FROM orders
LEFT JOIN (SELECT addresses.customer_id, d1.rating AS rating
FROM addresses
LEFT JOIN (SELECT customers.customer_id, a2.rating AS rating
FROM customers
LEFT JOIN addresses AS a2
ON (customers.customer_id = a2.customer_id)
WHERE (customers.customer_id < 1000)) AS d1
ON (addresses.customer_id = d1.customer_id)) AS d0
ON (orders.customer_id = d0.customer_id)
) AS raw_rows;
-- The same statement, returning every row so the two anomalies are visible.
--
-- Expected: 3 rows, one per order, each with the rating of its customer's address:
-- order 1 -> 2.0, order 2 -> 2.0, order 3 -> 4.0
--
-- Actual on 8.4.11: 4 rows.
-- * Order 2 joins to its key (matched_customer_id = 1) but its rating comes back NULL, even
-- though order 1, which has the same customer and the same key, correctly returns 2.0.
-- * Order 3 is returned twice.
-- * Order 1 is correct, so the statement is not wrong for every row.
SELECT orders.order_id,
orders.customer_id AS order_customer_id,
d0.customer_id AS matched_customer_id,
d0.rating AS rating
FROM orders
LEFT JOIN (SELECT addresses.customer_id, d1.rating AS rating
FROM addresses
LEFT JOIN (SELECT customers.customer_id, a2.rating AS rating
FROM customers
LEFT JOIN addresses AS a2
ON (customers.customer_id = a2.customer_id)
WHERE (customers.customer_id < 1000)) AS d1
ON (addresses.customer_id = d1.customer_id)) AS d0
ON (orders.customer_id = d0.customer_id)
ORDER BY orders.order_id;
Suggested fix:
We believe the fault lies in hash join execution rather than in query planning. The query plan on 8.4.11 is identical to the plan produced by 9.5.0, which returns the correct result, so the difference in behavior appears to arise during execution rather than during optimization. `EXPLAIN ANALYZE` shows the divergence at the inner hash join: on 9.4.0 that operator reports 18 rows per loop against a 17-row probe input, while on 9.5.0 the same operator reports 17. Our reading is that one probe row is being corrupted on each re-execution of the inner block, which would account for both observed symptoms — a duplicated output row and a row that loses the value it should have joined to. The plan places one hash join on another hash join's build side, and the two anomalies track the position of the row within the probe table rather than any property of the data, which is consistent with this interpretation.
Our bisection of published release binaries places the change in behavior between 9.4.0, which reproduces the problem, and 9.5.0, which does not. Reviewing the commits in that range, three appear related.
89c6b05f8ee - (Bug#34940000, "Hash join execution may be inefficient if probe input is empty," July 2023)
Introduced the practice of reading the first probe row during `Init()`; this commit is present in every tag from 8.2.0 through 8.4.11.
48d26b1800b - (Bug#37746132, "Result mismatch seen with Old Optimizer and Hypergraph," July 2025)
Describes a mechanism that closely matches what we observe, namely that a hash join higher in the tree can overwrite a prematurely read probe row by way of `BuildHashTable()`, and addresses it by deferring initialization.
801dc8bd747 - (Bug#38418831, September 2025) subsequently reverted that deferral, because it caused a separate wrong-results problem, and instead copies the probe row into a dedicated buffer that is restored on the first `Read()`. The latter is the form that ships in 9.5.0 and later. Neither fix is present in any 8.4 tag, while the 2023 commit that introduced the early probe read is.
Description: We are seeing incorrect results from a specific class of query. The query has two nested derived tables, each defined by a subquery, joined onto the outer tables with LEFT JOIN. The join keys across every table form a single equality chain (for example, order.customer_id = address.customer_id = customer.id), and none of the columns in the joins are indexed. The innermost subquery applies a WHERE filter to its own join-key column (a column participating in the equality chain). This filter is required to reproduce the problem: moving it to any other column in the same subquery, or removing it, produces correct results. However, the filter does not need to be restrictive enough to effect the results (i.e. customer.id > 0 when all ids are positive would still produce incorrect results). As a concrete example, on MySQL 8.4.11 a statement of this shape that should return 3 rows — one per row of the outermost table — returns 4 instead: one row appears twice, and a separate row is null-extended even though it has exactly one matching row in the joined data. The data guarantees a single match per outer row, so the correct result is not ambiguous. The same statement returns the expected 3 rows on 9.5.0 and later. As it stands, work arounds do exist. 8.4.11 returns the correct result if a unique index is added on any join key, or if derived_merge is disabled. How to repeat: DROP DATABASE IF EXISTS mysql84Bug; CREATE DATABASE mysql84Bug; USE mysql84Bug; CREATE TABLE customers ( customer_id bigint DEFAULT NULL ) ENGINE=InnoDB; CREATE TABLE addresses ( address_id bigint DEFAULT NULL, customer_id bigint DEFAULT NULL, rating double DEFAULT NULL ) ENGINE=InnoDB; CREATE TABLE orders ( order_id bigint DEFAULT NULL, customer_id bigint DEFAULT NULL ) ENGINE=InnoDB; INSERT INTO customers (customer_id) VALUES (1),(2); INSERT INTO addresses (address_id, customer_id, rating) VALUES (1,1,2.0),(2,2,4.0); -- Customer 1 has two orders, customer 2 has one. INSERT INTO orders (order_id, customer_id) VALUES (1,1),(2,1),(3,2); ANALYZE TABLE customers, addresses, orders; -- The statement. Each order joins to its customer's address, and through a second hop back to -- the same address table, to project that address's rating. -- -- The WHERE clause inside the innermost subquery keeps every row of customers, so it cannot -- account for any row being added or lost. Removing it, or moving it to any other column, -- makes the server return the correct result. -- -- Expected: 3 rows, one per order. Actual on 8.4.11: 4 rows. SELECT COUNT(*) AS rows_returned, 3 AS rows_expected FROM ( SELECT orders.order_id, d0.rating FROM orders LEFT JOIN (SELECT addresses.customer_id, d1.rating AS rating FROM addresses LEFT JOIN (SELECT customers.customer_id, a2.rating AS rating FROM customers LEFT JOIN addresses AS a2 ON (customers.customer_id = a2.customer_id) WHERE (customers.customer_id < 1000)) AS d1 ON (addresses.customer_id = d1.customer_id)) AS d0 ON (orders.customer_id = d0.customer_id) ) AS raw_rows; -- The same statement, returning every row so the two anomalies are visible. -- -- Expected: 3 rows, one per order, each with the rating of its customer's address: -- order 1 -> 2.0, order 2 -> 2.0, order 3 -> 4.0 -- -- Actual on 8.4.11: 4 rows. -- * Order 2 joins to its key (matched_customer_id = 1) but its rating comes back NULL, even -- though order 1, which has the same customer and the same key, correctly returns 2.0. -- * Order 3 is returned twice. -- * Order 1 is correct, so the statement is not wrong for every row. SELECT orders.order_id, orders.customer_id AS order_customer_id, d0.customer_id AS matched_customer_id, d0.rating AS rating FROM orders LEFT JOIN (SELECT addresses.customer_id, d1.rating AS rating FROM addresses LEFT JOIN (SELECT customers.customer_id, a2.rating AS rating FROM customers LEFT JOIN addresses AS a2 ON (customers.customer_id = a2.customer_id) WHERE (customers.customer_id < 1000)) AS d1 ON (addresses.customer_id = d1.customer_id)) AS d0 ON (orders.customer_id = d0.customer_id) ORDER BY orders.order_id; Suggested fix: We believe the fault lies in hash join execution rather than in query planning. The query plan on 8.4.11 is identical to the plan produced by 9.5.0, which returns the correct result, so the difference in behavior appears to arise during execution rather than during optimization. `EXPLAIN ANALYZE` shows the divergence at the inner hash join: on 9.4.0 that operator reports 18 rows per loop against a 17-row probe input, while on 9.5.0 the same operator reports 17. Our reading is that one probe row is being corrupted on each re-execution of the inner block, which would account for both observed symptoms — a duplicated output row and a row that loses the value it should have joined to. The plan places one hash join on another hash join's build side, and the two anomalies track the position of the row within the probe table rather than any property of the data, which is consistent with this interpretation. Our bisection of published release binaries places the change in behavior between 9.4.0, which reproduces the problem, and 9.5.0, which does not. Reviewing the commits in that range, three appear related. 89c6b05f8ee - (Bug#34940000, "Hash join execution may be inefficient if probe input is empty," July 2023) Introduced the practice of reading the first probe row during `Init()`; this commit is present in every tag from 8.2.0 through 8.4.11. 48d26b1800b - (Bug#37746132, "Result mismatch seen with Old Optimizer and Hypergraph," July 2025) Describes a mechanism that closely matches what we observe, namely that a hash join higher in the tree can overwrite a prematurely read probe row by way of `BuildHashTable()`, and addresses it by deferring initialization. 801dc8bd747 - (Bug#38418831, September 2025) subsequently reverted that deferral, because it caused a separate wrong-results problem, and instead copies the probe row into a dedicated buffer that is restored on the first `Read()`. The latter is the form that ships in 9.5.0 and later. Neither fix is present in any 8.4 tag, while the 2023 commit that introduced the early probe read is.