Bug #121479 ERROR 1815 "Internal error: Key not found" for IN subquery comparing a unique key with (out-of-range constant < column)

• Submitted: 11 Oct 15:43
• Modified: 13 Oct 5:59
• Reporter: Ann Calla
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.46, 8.4.11, 9.7.2, 26.07
• OS: Ubuntu (24.04)
• CPU Architecture: x86 (x86_64)

[11 Oct 15:43] Ann Calla

Description:
A query of the form "c IN (SELECT <constant> < c FROM t)" fails with ERROR 1815 (HY000): Internal error: Key not found when c has a unique index and the constant is outside the range of the column type (999 for TINYINT). With an in-range constant (99) the query returns the correct empty result. EXPLAIN of the failing query raises the same error, so it happens during optimization.

Two rows are enough; no NULL is needed.

Bug #115804 reports the same error message, but its test case needs a JOIN_ORDER hint and a scalar subquery with LIMIT; this one needs neither.

How to repeat:
CREATE TABLE t(c TINYINT UNIQUE);
INSERT INTO t VALUES (1), (2);

SELECT c FROM t WHERE c IN (SELECT 99 < c FROM t);
-- empty (correct)

SELECT c FROM t WHERE c IN (SELECT 999 < c FROM t);
-- expected: empty
-- actual:   ERROR 1815 (HY000): Internal error: Key not found

EXPLAIN SELECT c FROM t WHERE c IN (SELECT 999 < c FROM t);
-- ERROR 1815 (HY000): Internal error: Key not found

[13 Oct 5:59] Chaithra Marsur Gopala Reddy

Hi Ann Calla,

Thank you for the test case. Verified as described.