Description:
When the optimizer chooses a semijoin plan that performs a Nested Loop Join with B-tree index lookup on a DECIMAL column, and the outer table supplies a VARCHAR value that is a non-canonical numeric string (e.g., '1a' which CASTs to DECIMAL 1.00), the index lookup fails to find matching rows that should be returned.
A "non-canonical numeric string" is a string that MySQL can coerce to a number via CAST/implicit conversion, but which contains trailing non-numeric characters (e.g., '1a' → 1.00). Canonical strings like '1' or '0' work correctly for index lookup.
This bug is caused by the B-tree index lookup on the DECIMAL column. When the DECIMAL column has no index, the optimizer chooses Hash semijoin instead, which correctly handles the type coercion. The bug only manifests when a DECIMAL B-tree index exists and the optimizer chooses Nested Loop with index probe.
The root cause appears to be in how MySQL constructs the B-tree index search key during semijoin execution. When converting the VARCHAR value to a DECIMAL search key for the index probe, MySQL uses the raw (non-canonical) string representation instead of the properly coerced DECIMAL value. This causes the B-tree comparison to fail, and the lookup returns 0 rows even though matching rows exist.
Notably, this bug is triggered by completely normal DECIMAL values (e.g., 1.00, 0.00) — not extreme or overflow values. Any non-canonical numeric string (a string with trailing non-numeric characters that MySQL can still coerce to a number) will trigger this bug, meaning everyday VARCHAR-to-DECIMAL comparisons in semijoin queries are affected.
Affected semijoin strategies:
- DuplicateWeedout (uses index lookup — AFFECTED)
- Materialization (uses index lookup — AFFECTED)
Unaffected strategies:
- FirstMatch (no index lookup — NOT affected)
- Hash semijoin (uses hash table, no B-tree lookup — NOT affected)
This is a correctness bug — the optimizer silently drops valid rows under default settings. Since semijoin is enabled by default, any workload with VARCHAR-to-DECIMAL comparisons in EXISTS/IN subqueries that triggers a semijoin index lookup plan may return wrong results.
How to repeat:
DROP DATABASE IF EXISTS test_semijoin_bug;
CREATE DATABASE test_semijoin_bug;
USE test_semijoin_bug;
-- Table with VARCHAR column (no index needed)
CREATE TABLE t0 (
c7 VARCHAR(255) NOT NULL
);
-- Table with DECIMAL column and B-tree index (this index causes the bug)
CREATE TABLE t1 (
c1 DECIMAL(10,2) NOT NULL,
KEY idx_c1 (c1)
);
-- Padding rows to make optimizer prefer Nested Loop index lookup
INSERT INTO t0 (c7) VALUES ('n1'),('n2'),('n3'),('n4'),('n5'),('0'),('1a');
INSERT INTO t1 (c1) VALUES (0.00),(1.00),(1.11),(2.22),(3.33),(4.44),(5.55);
ANALYZE TABLE t0;
ANALYZE TABLE t1;
-- Test 1: Default semijoin (BUGGY — c1=1.00 row missing)
SELECT '--- Test 1: Default semijoin ---' AS info;
EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t0 WHERE t0.c7 = t1.c1);
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t0 WHERE t0.c7 = t1.c1);
-- BUGGY: Returns only c1=0.00 (1 row).
-- The c1=1.00 row is MISSING because '1a' index lookup fails.
-- Test 2: Semijoin OFF (CORRECT reference result)
SELECT '--- Test 2: Semijoin OFF (correct) ---' AS info;
SET SESSION optimizer_switch='semijoin=off';
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t0 WHERE t0.c7 = t1.c1);
-- CORRECT: Returns c1=0.00 AND c1=1.00 (2 rows).
SET SESSION optimizer_switch='default';
-- Test 3: Force DuplicateWeedout (BUGGY)
SELECT '--- Test 3: Force DuplicateWeedout ---' AS info;
SET SESSION optimizer_switch='semijoin=on,firstmatch=off,loosescan=off,materialization=off,duplicateweedout=on';
EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t0 WHERE t0.c7 = t1.c1);
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t0 WHERE t0.c7 = t1.c1);
-- BUGGY: Same wrong result — c1=1.00 row is missing.
SET SESSION optimizer_switch='default';
-- Verification: Removing the index on t1.c1 makes the bug disappear
-- (optimizer switches to Hash semijoin, which handles type coercion correctly)
Expected result:
- Test 1 (default semijoin) and Test 3 (DuplicateWeedout) should return the same rows as Test 2 (semijoin OFF).
- Specifically, the row with c1=1.00 in t1 should be included because '1a' in t0.c7 CASTs to DECIMAL 1.00 and should match.
Actual result:
Test 1: With default optimizer setting (semijoin on)
+------+
| c1 |
+------+
| 0.00 | <- ✗ WRONG! missing 1 row
+------+
1 row in set (0.000 sec)
Test 2: With semijoin OFF
+------+
| c1 |
+------+
| 0.00 |
| 1.00 | <- ✓ CORRECT
+------+
2 rows in set (0.000 sec)
Test 3:Force DuplicateWeedout (semijoin, duplicateweedout on; firstmatch, loosescan, materialization off)
+------+
| c1 |
+------+
| 0.00 | <- ✗ WRONG! missing 1 row
+------+
1 row in set (0.000 sec)
* The EXPLAIN output from script shows: "Covering index lookup on t1 using idx_c1 (c1 = t0.c7)" — the index probe on the DECIMAL B-tree is where the bug occurs.
Suggested fix:
Investigate how the B-tree index search key is constructed during semijoin execution when the join condition involves a VARCHAR-to-DECIMAL type comparison.
Key areas to investigate:
1. In the semijoin Nested Loop execution path, when the outer table provides a VARCHAR value and the inner table has a DECIMAL B-tree index, MySQL must ensure the search key for the index probe uses the properly coerced DECIMAL value (i.e., CAST('1a' AS DECIMAL(10,2)) = 1.00) rather than attempting to use the raw string for B-tree comparison.
2. The bug likely resides in the index lookup key construction code (in ha_innodb.cc or the optimizer's semijoin execution code) where the VARCHAR-to-DECIMAL type conversion for the index search tuple does not follow the same coercion rules as a regular WHERE clause comparison.
3. Verify that the `Item` comparison object used for constructing the index probe key performs proper type conversion for non-canonical numeric strings before the B-tree search is performed.
Description: When the optimizer chooses a semijoin plan that performs a Nested Loop Join with B-tree index lookup on a DECIMAL column, and the outer table supplies a VARCHAR value that is a non-canonical numeric string (e.g., '1a' which CASTs to DECIMAL 1.00), the index lookup fails to find matching rows that should be returned. A "non-canonical numeric string" is a string that MySQL can coerce to a number via CAST/implicit conversion, but which contains trailing non-numeric characters (e.g., '1a' → 1.00). Canonical strings like '1' or '0' work correctly for index lookup. This bug is caused by the B-tree index lookup on the DECIMAL column. When the DECIMAL column has no index, the optimizer chooses Hash semijoin instead, which correctly handles the type coercion. The bug only manifests when a DECIMAL B-tree index exists and the optimizer chooses Nested Loop with index probe. The root cause appears to be in how MySQL constructs the B-tree index search key during semijoin execution. When converting the VARCHAR value to a DECIMAL search key for the index probe, MySQL uses the raw (non-canonical) string representation instead of the properly coerced DECIMAL value. This causes the B-tree comparison to fail, and the lookup returns 0 rows even though matching rows exist. Notably, this bug is triggered by completely normal DECIMAL values (e.g., 1.00, 0.00) — not extreme or overflow values. Any non-canonical numeric string (a string with trailing non-numeric characters that MySQL can still coerce to a number) will trigger this bug, meaning everyday VARCHAR-to-DECIMAL comparisons in semijoin queries are affected. Affected semijoin strategies: - DuplicateWeedout (uses index lookup — AFFECTED) - Materialization (uses index lookup — AFFECTED) Unaffected strategies: - FirstMatch (no index lookup — NOT affected) - Hash semijoin (uses hash table, no B-tree lookup — NOT affected) This is a correctness bug — the optimizer silently drops valid rows under default settings. Since semijoin is enabled by default, any workload with VARCHAR-to-DECIMAL comparisons in EXISTS/IN subqueries that triggers a semijoin index lookup plan may return wrong results. How to repeat: DROP DATABASE IF EXISTS test_semijoin_bug; CREATE DATABASE test_semijoin_bug; USE test_semijoin_bug; -- Table with VARCHAR column (no index needed) CREATE TABLE t0 ( c7 VARCHAR(255) NOT NULL ); -- Table with DECIMAL column and B-tree index (this index causes the bug) CREATE TABLE t1 ( c1 DECIMAL(10,2) NOT NULL, KEY idx_c1 (c1) ); -- Padding rows to make optimizer prefer Nested Loop index lookup INSERT INTO t0 (c7) VALUES ('n1'),('n2'),('n3'),('n4'),('n5'),('0'),('1a'); INSERT INTO t1 (c1) VALUES (0.00),(1.00),(1.11),(2.22),(3.33),(4.44),(5.55); ANALYZE TABLE t0; ANALYZE TABLE t1; -- Test 1: Default semijoin (BUGGY — c1=1.00 row missing) SELECT '--- Test 1: Default semijoin ---' AS info; EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t0 WHERE t0.c7 = t1.c1); SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t0 WHERE t0.c7 = t1.c1); -- BUGGY: Returns only c1=0.00 (1 row). -- The c1=1.00 row is MISSING because '1a' index lookup fails. -- Test 2: Semijoin OFF (CORRECT reference result) SELECT '--- Test 2: Semijoin OFF (correct) ---' AS info; SET SESSION optimizer_switch='semijoin=off'; SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t0 WHERE t0.c7 = t1.c1); -- CORRECT: Returns c1=0.00 AND c1=1.00 (2 rows). SET SESSION optimizer_switch='default'; -- Test 3: Force DuplicateWeedout (BUGGY) SELECT '--- Test 3: Force DuplicateWeedout ---' AS info; SET SESSION optimizer_switch='semijoin=on,firstmatch=off,loosescan=off,materialization=off,duplicateweedout=on'; EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t0 WHERE t0.c7 = t1.c1); SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t0 WHERE t0.c7 = t1.c1); -- BUGGY: Same wrong result — c1=1.00 row is missing. SET SESSION optimizer_switch='default'; -- Verification: Removing the index on t1.c1 makes the bug disappear -- (optimizer switches to Hash semijoin, which handles type coercion correctly) Expected result: - Test 1 (default semijoin) and Test 3 (DuplicateWeedout) should return the same rows as Test 2 (semijoin OFF). - Specifically, the row with c1=1.00 in t1 should be included because '1a' in t0.c7 CASTs to DECIMAL 1.00 and should match. Actual result: Test 1: With default optimizer setting (semijoin on) +------+ | c1 | +------+ | 0.00 | <- ✗ WRONG! missing 1 row +------+ 1 row in set (0.000 sec) Test 2: With semijoin OFF +------+ | c1 | +------+ | 0.00 | | 1.00 | <- ✓ CORRECT +------+ 2 rows in set (0.000 sec) Test 3:Force DuplicateWeedout (semijoin, duplicateweedout on; firstmatch, loosescan, materialization off) +------+ | c1 | +------+ | 0.00 | <- ✗ WRONG! missing 1 row +------+ 1 row in set (0.000 sec) * The EXPLAIN output from script shows: "Covering index lookup on t1 using idx_c1 (c1 = t0.c7)" — the index probe on the DECIMAL B-tree is where the bug occurs. Suggested fix: Investigate how the B-tree index search key is constructed during semijoin execution when the join condition involves a VARCHAR-to-DECIMAL type comparison. Key areas to investigate: 1. In the semijoin Nested Loop execution path, when the outer table provides a VARCHAR value and the inner table has a DECIMAL B-tree index, MySQL must ensure the search key for the index probe uses the properly coerced DECIMAL value (i.e., CAST('1a' AS DECIMAL(10,2)) = 1.00) rather than attempting to use the raw string for B-tree comparison. 2. The bug likely resides in the index lookup key construction code (in ha_innodb.cc or the optimizer's semijoin execution code) where the VARCHAR-to-DECIMAL type conversion for the index search tuple does not follow the same coercion rules as a regular WHERE clause comparison. 3. Verify that the `Item` comparison object used for constructing the index probe key performs proper type conversion for non-canonical numeric strings before the B-tree search is performed.