Description:
A SELECT with no GROUP BY, no DISTINCT and no aggregates returns 0 rows instead of 3 when the execution plan combines:
1. a semijoin executed with the duplicate-weedout strategy, and
2. a filesort producing row IDs ("Sort row IDs" in EXPLAIN FORMAT=TREE) on the first table, feeding the joins below the weedout.
The weedout's temporary table rowid bookkeeping misidentifies every row as a duplicate and discards it. Changing only the ORDER BY (to the primary key) returns all 3 rows. An ORDER BY must never change the number of rows returned, so one of the two results has to be wrong.
The failure mechanism looks identical to the long-fixed Bug #53305: rows arriving at the weedout with stale or unset handler positions.
Verified with the official mysql Docker images: reproduces on 8.0.42, 8.0.44, 8.0.46 and 8.4.10. Not reproducible on 9.3.0 and 9.7.1, which choose the identical execution plan and return the correct rows, so the executor defect appears fixed in the 9.x line but was never backported to 8.0/8.4.
We hit this in production without any hints (Redmine time entries page, where ActiveRecord compiles permission checks into EXISTS subqueries): a list page showed 1 row while its own pagination counter said 4. Any application combining EXISTS/IN subqueries with ORDER BY is exposed, and the wrong results are silent.
While minimizing the test case we found that each of the following is needed to reproduce (removing any of them makes the wrong result disappear on 8.0.44):
- the EXISTS inside the LEFT JOIN ... ON condition (the weedout-executed EXISTS in the WHERE is not enough on its own)
- the composite index t2_id_type (id, type) on the eq_ref'd table t2
- the unused index t1_grp on the filter column (its mere existence changes the outcome)
- reading t1 through an index that does not match the residual filter
How to repeat:
Run on a fresh server (7 rows of data total):
DROP DATABASE IF EXISTS weedout_bug;
CREATE DATABASE weedout_bug;
USE weedout_bug;
CREATE TABLE t1 (
id INT PRIMARY KEY,
grp INT NOT NULL,
item INT,
act INT NOT NULL,
d INT NOT NULL,
KEY t1_grp (grp),
KEY t1_item (item)
);
CREATE TABLE t2 (id INT PRIMARY KEY, type VARCHAR(10), KEY t2_id_type (id, type));
CREATE TABLE t3 (id INT PRIMARY KEY);
CREATE TABLE t4 (grp INT, name VARCHAR(10), KEY t4_grp_name (grp, name));
INSERT INTO t1 VALUES (1,1,2,1,20), (2,1,2,1,10), (3,1,2,1,30); -- all 3 match the WHERE below
INSERT INTO t2 VALUES (1,'A');
INSERT INTO t3 VALUES (2);
INSERT INTO t4 VALUES (1,'x'), (1,'y');
ANALYZE TABLE t1, t2, t3, t4;
Q1, wrong result. Returns 0 rows on 8.0.42 / 8.0.44 / 8.0.46 / 8.4.10, and the correct 3 rows on 9.3.0 / 9.7.1:
SELECT /*+ SEMIJOIN(@sq DUPSWEEDOUT) INDEX(t1 t1_item) */ t1.id
FROM t1
LEFT JOIN t2 ON t2.id = t1.act AND t2.type = 'A'
LEFT JOIN t3 ON t3.id = t1.item
AND EXISTS (SELECT 1 FROM t4 WHERE t4.grp = t1.grp AND t4.name = 'x')
WHERE EXISTS (SELECT /*+ QB_NAME(sq) */ 1 FROM t4 WHERE t4.grp = t1.grp AND t4.name = 'y')
AND t1.item = 2 AND t1.grp = 1
ORDER BY t1.d DESC;
The hints only pin, on this deliberately tiny dataset, the plan the optimizer chooses on its own on our production statistics (where the query is generated hint-free by an ORM): SEMIJOIN(DUPSWEEDOUT) fixes the semijoin strategy, and INDEX(t1 t1_item) makes t1 be read through an index other than the one matching the t1.grp filter, which is what puts the rowid filesort in front of the joins.
Controls, each returns the correct 3 rows:
Q2: Q1 with ORDER BY t1.id instead of ORDER BY t1.d DESC
Q3: Q1 with SEMIJOIN(@sq MATERIALIZATION)
Q4: SET SESSION optimizer_switch='duplicateweedout=off'; then Q1 without the SEMIJOIN hint
EXPLAIN FORMAT=TREE of Q1 is identical on all versions tested, 8.0.42 through 9.7.1; only the 9.x executor returns correct rows from it. Note the weedout fed by "Sort row IDs":
-> Remove duplicate (t1, t2, t3) rows using temporary table (weedout)
-> Nested loop inner join
-> Nested loop left join
-> Nested loop left join
-> Sort row IDs: t1.d DESC
-> Filter: (t1.grp = 1)
-> Index lookup on t1 using t1_item (item=2)
-> Filter: (t2.type = 'A')
-> Single-row index lookup on t2 using PRIMARY (id=t1.act)
-> Nested loop inner join
-> Covering index lookup on t4 using t4_grp_name (grp=1, name='x')
-> Constant row from t3
-> Covering index lookup on t4 using t4_grp_name (grp=1, name='y')
Suggested fix:
Ensure handler positions (rowids) are refilled for rows delivered through a rowid-based filesort before the duplicate-weedout comparison. See Bug #53305 for a historical instance of what looks like the same defect. Alternatively, backport the 9.x fix (present by 9.3.0) to the 8.0 and 8.4 LTS lines.
Workaround: SET GLOBAL optimizer_switch='duplicateweedout=off'; the optimizer falls back to firstmatch/materialization/loosescan and results are correct.
© 2026, Oracle Corporation and/or its affiliates
Description: A SELECT with no GROUP BY, no DISTINCT and no aggregates returns 0 rows instead of 3 when the execution plan combines: 1. a semijoin executed with the duplicate-weedout strategy, and 2. a filesort producing row IDs ("Sort row IDs" in EXPLAIN FORMAT=TREE) on the first table, feeding the joins below the weedout. The weedout's temporary table rowid bookkeeping misidentifies every row as a duplicate and discards it. Changing only the ORDER BY (to the primary key) returns all 3 rows. An ORDER BY must never change the number of rows returned, so one of the two results has to be wrong. The failure mechanism looks identical to the long-fixed Bug #53305: rows arriving at the weedout with stale or unset handler positions. Verified with the official mysql Docker images: reproduces on 8.0.42, 8.0.44, 8.0.46 and 8.4.10. Not reproducible on 9.3.0 and 9.7.1, which choose the identical execution plan and return the correct rows, so the executor defect appears fixed in the 9.x line but was never backported to 8.0/8.4. We hit this in production without any hints (Redmine time entries page, where ActiveRecord compiles permission checks into EXISTS subqueries): a list page showed 1 row while its own pagination counter said 4. Any application combining EXISTS/IN subqueries with ORDER BY is exposed, and the wrong results are silent. While minimizing the test case we found that each of the following is needed to reproduce (removing any of them makes the wrong result disappear on 8.0.44): - the EXISTS inside the LEFT JOIN ... ON condition (the weedout-executed EXISTS in the WHERE is not enough on its own) - the composite index t2_id_type (id, type) on the eq_ref'd table t2 - the unused index t1_grp on the filter column (its mere existence changes the outcome) - reading t1 through an index that does not match the residual filter How to repeat: Run on a fresh server (7 rows of data total): DROP DATABASE IF EXISTS weedout_bug; CREATE DATABASE weedout_bug; USE weedout_bug; CREATE TABLE t1 ( id INT PRIMARY KEY, grp INT NOT NULL, item INT, act INT NOT NULL, d INT NOT NULL, KEY t1_grp (grp), KEY t1_item (item) ); CREATE TABLE t2 (id INT PRIMARY KEY, type VARCHAR(10), KEY t2_id_type (id, type)); CREATE TABLE t3 (id INT PRIMARY KEY); CREATE TABLE t4 (grp INT, name VARCHAR(10), KEY t4_grp_name (grp, name)); INSERT INTO t1 VALUES (1,1,2,1,20), (2,1,2,1,10), (3,1,2,1,30); -- all 3 match the WHERE below INSERT INTO t2 VALUES (1,'A'); INSERT INTO t3 VALUES (2); INSERT INTO t4 VALUES (1,'x'), (1,'y'); ANALYZE TABLE t1, t2, t3, t4; Q1, wrong result. Returns 0 rows on 8.0.42 / 8.0.44 / 8.0.46 / 8.4.10, and the correct 3 rows on 9.3.0 / 9.7.1: SELECT /*+ SEMIJOIN(@sq DUPSWEEDOUT) INDEX(t1 t1_item) */ t1.id FROM t1 LEFT JOIN t2 ON t2.id = t1.act AND t2.type = 'A' LEFT JOIN t3 ON t3.id = t1.item AND EXISTS (SELECT 1 FROM t4 WHERE t4.grp = t1.grp AND t4.name = 'x') WHERE EXISTS (SELECT /*+ QB_NAME(sq) */ 1 FROM t4 WHERE t4.grp = t1.grp AND t4.name = 'y') AND t1.item = 2 AND t1.grp = 1 ORDER BY t1.d DESC; The hints only pin, on this deliberately tiny dataset, the plan the optimizer chooses on its own on our production statistics (where the query is generated hint-free by an ORM): SEMIJOIN(DUPSWEEDOUT) fixes the semijoin strategy, and INDEX(t1 t1_item) makes t1 be read through an index other than the one matching the t1.grp filter, which is what puts the rowid filesort in front of the joins. Controls, each returns the correct 3 rows: Q2: Q1 with ORDER BY t1.id instead of ORDER BY t1.d DESC Q3: Q1 with SEMIJOIN(@sq MATERIALIZATION) Q4: SET SESSION optimizer_switch='duplicateweedout=off'; then Q1 without the SEMIJOIN hint EXPLAIN FORMAT=TREE of Q1 is identical on all versions tested, 8.0.42 through 9.7.1; only the 9.x executor returns correct rows from it. Note the weedout fed by "Sort row IDs": -> Remove duplicate (t1, t2, t3) rows using temporary table (weedout) -> Nested loop inner join -> Nested loop left join -> Nested loop left join -> Sort row IDs: t1.d DESC -> Filter: (t1.grp = 1) -> Index lookup on t1 using t1_item (item=2) -> Filter: (t2.type = 'A') -> Single-row index lookup on t2 using PRIMARY (id=t1.act) -> Nested loop inner join -> Covering index lookup on t4 using t4_grp_name (grp=1, name='x') -> Constant row from t3 -> Covering index lookup on t4 using t4_grp_name (grp=1, name='y') Suggested fix: Ensure handler positions (rowids) are refilled for rows delivered through a rowid-based filesort before the duplicate-weedout comparison. See Bug #53305 for a historical instance of what looks like the same defect. Alternatively, backport the 9.x fix (present by 9.3.0) to the 8.0 and 8.4 LTS lines. Workaround: SET GLOBAL optimizer_switch='duplicateweedout=off'; the optimizer falls back to firstmatch/materialization/loosescan and results are correct.