Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4, 11.8, 12.3, 13.1, 11.4.13, 11.8.9, 12.3.3, 13.0.2, 13.1.1
-
Official mariadb Docker images (Ubuntu) and Homebrew 12.3.2 on macOS (arm64); default configuration, InnoDB
-
Unexpected results
Description
A LEFT JOIN with two IN (subquery) conditions loses rows on 11.0 and later. 10.11 is correct. In the wrong plan, the second semi-join table is read through a BNL join buffer inside a DuplicateWeedout range, after the LEFT JOINed table.
Test case
CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB; |
CREATE TABLE t2 (pk INT PRIMARY KEY, a INT NOT NULL, c INT NOT NULL, KEY (a, c)) ENGINE=InnoDB; |
CREATE TABLE t3 (pk INT PRIMARY KEY, d INT NOT NULL, b INT, KEY (b), KEY (d)) ENGINE=InnoDB; |
|
|
INSERT INTO t1 VALUES (1, 1); |
INSERT INTO t2 VALUES (1, 1, 1), (2, 1, 2); |
INSERT INTO t3 VALUES (1, 2, 2), (2, 1, 1); |
|
|
SELECT t1.a, t2.pk |
FROM t1 LEFT JOIN t2 ON t2.a = t1.a |
WHERE t1.a = 1 |
AND t1.b IN (SELECT t3.b FROM t3 WHERE t3.d = 1) |
AND t1.a IN (SELECT x.a FROM t2 x WHERE x.c IN (1, 2, 3, 4)); |
Both IN conditions are true for t1.a = 1, so the expected result is both t2 rows.
10.11.18: correct
+---+------+
|
| a | pk |
|
+---+------+
|
| 1 | 1 |
|
| 1 | 2 |
|
+---+------+
|
|
|
+------+--------------+-------------+------------+---------------+--------------+---------+-------+---------+---------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------+-------------+------------+---------------+--------------+---------+-------+---------+---------------------------------+
|
| 1 | PRIMARY | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | |
|
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | |
|
| 1 | PRIMARY | <subquery3> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | |
|
| 1 | PRIMARY | t2 | ref | a | a | 4 | const | 2 | Using index |
|
| 2 | MATERIALIZED | t3 | ref|filter | b,d | b|d | 5|4 | const | 1 (50%) | Using where; Using rowid filter |
|
| 3 | MATERIALIZED | x | range | a | a | 8 | NULL | 2 | Using where; Using index |
|
+------+--------------+-------------+------------+---------------+--------------+---------+-------+---------+---------------------------------+
|
12.3.2 and 12.3.3: wrong
+---+------+
|
| a | pk |
|
+---+------+
|
| 1 | 1 |
|
+---+------+
|
|
|
+------+-------------+-------+-------------+---------------+---------+---------+-------+------+----------------------------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------------+---------------+---------+---------+-------+------+----------------------------------------------------------------------------------------------+
|
| 1 | PRIMARY | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | |
|
| 1 | PRIMARY | t3 | index_merge | b,d | d,b | 4,5 | NULL | 1 | Using intersect(d,b); Using where; Using index; Start temporary; End temporary |
|
| 1 | PRIMARY | t2 | ref | a | a | 4 | const | 2 | Using index |
|
| 1 | PRIMARY | x | range | a | a | 8 | NULL | 2 | Using where; Using index; Start temporary; End temporary; Using join buffer (flat, BNL join) |
|
+------+-------------+-------+-------------+---------------+---------+---------+-------+------+----------------------------------------------------------------------------------------------+
|
The row with t2.pk = 2 is lost; COUNT(*) also returns 1.
What changes the result (12.3.2)
- Correct, same plan with no join buffer on x: semijoin_with_cache=off, or join_cache_level=0.
- Correct, different plan: semijoin=off, or duplicateweedout=off (both subqueries materialized).
- Still wrong: firstmatch=off, loosescan=off, materialization=off, outer_join_with_cache=off, index_merge_intersection=off, rowid_filter=off; join_cache_level=1; ANALYZE TABLE ... PERSISTENT FOR ALL.
- Correct: INNER JOIN (plan becomes FirstMatch), removing either IN, removing the x.c filter, or KEY (a) instead of KEY (a, c) on t2.
The =off items are optimizer_switch flags.
10.11 with DuplicateWeedout
With optimizer_switch='materialization=off', 10.11.18 also uses DuplicateWeedout with a BNL buffer on x and returns both rows, but puts x before t2:
+------+-------------+-------+------------+---------------+---------+---------+-------+---------+----------------------------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------------+---------------+---------+---------+-------+---------+----------------------------------------------------------------------------------------------+
|
| 1 | PRIMARY | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | |
|
| 1 | PRIMARY | t3 | ref|filter | b,d | b|d | 5|4 | const | 1 (50%) | Using where; Start temporary; End temporary; Using rowid filter |
|
| 1 | PRIMARY | x | range | a | a | 8 | NULL | 2 | Using where; Using index; Start temporary; End temporary; Using join buffer (flat, BNL join) |
|
| 1 | PRIMARY | t2 | ref | a | a | 4 | const | 2 | Using index |
|
+------+-------------+-------+------------+---------------+---------+---------+-------+---------+----------------------------------------------------------------------------------------------+
|
On a larger private query of the same shape, 10.11.18 and 12.3.2 chose the same plan (const tables, an SJ-Materialization eq_ref lookup, the LEFT JOINed tables, then a DuplicateWeedout range with a BNL-joined semi-join table). 10.11.18 returned the right rows; 12.3.2 lost rows.
Versions tested
Official Docker images and quay.io/mariadb-foundation/mariadb-devel nightlies (built 2026-10-24 to 2026-10-28):
- Correct: 10.11.18, 10.11.19, 10.11.20 nightly
- Wrong (join order t1, t3, t2, x in every case): 11.0.6, 11.4.13, 11.8.9, 11.8.10 nightly (includes the
MDEV-41146fix), 12.0.2, 12.1.2, 12.2.2, 12.3.2, 12.3.3, 13.0.2, 13.1.1-rc, 13.2.0 nightly - MySQL 8.0.46, 8.4.13 and 9.7.4 return both rows.
Related issues
- MDEV-29382 (Stalled; fix unmerged on bb-10.9-mdev29382): also DuplicateWeedout + join buffer, but triggered by an SJ-Materialization table in the prefix, and returns extra rows. Its test case passes on 10.11.18 and 12.3.2.
MDEV-41146(fixed in 12.3.4): extra row, needs rand() and non-default cost settings; unrelated cause.MDEV-5161,MDEV-6706(missing rows, semi-join + LEFT JOIN + ORDER BY, fixed in 5.5/10.0): their test cases pass on 12.3.2.
Attachments
Issue Links
- relates to
-
-
- Stalled
-